Multiple insert SQL statement problem 3

[RustyAfro]

I'm trying to learn how to build a multi insert statement into oracle. The below inserts work one at a time, but when I put them together and try to execute, I receive an error (in PL/SQL).

One error is below, which seems to reference the ending ";".
ORA-00911: Invalid Character

However, if I remove the ";" on each insert and just put "Commit;" at the end, I get an error stating SQL not ended
ORA-00933 SQL command not properly ended. If I put "Commit;" at the end while keeping the ";" after each insert, I still get the ORA-00911 error.

I tried wrapping them in a Begin End statement, which worked, but when I put a few hundred in, another error occured about the program being to long.

If I could get any pointers would really appreciate it. Also, I'm trying to understand the fastest way of writing the INSERT statements against the server. The reason I am using Insert statements is that this is for a small project to move access data. I know there are other tools, but I wanted to try to see what I could first using Insert statements.

INSERT /*+ append */ INTO UWTBL_COUNTY_FACTORS VALUES('AR','OUC','OUACHITA',99,0.89,'Y','N','         ','01-JAN-1900','31-DEC-9999');
INSERT /*+ append */ INTO UWTBL_COUNTY_FACTORS VALUES('AR','OUC','OUACHITA',100,1.00,'Y','N','         ','01-JAN-1900','31-DEC-9999');
INSERT /*+ append */ INTO UWTBL_COUNTY_FACTORS VALUES('AR','OUC','OUACHITA',251,0.89,'Y','N','         ','01-JAN-1900','31-DEC-9999');
INSERT /*+ append */ INTO UWTBL_COUNTY_FACTORS VALUES('AR','OUC','OUACHITA',252,1.00,'Y','N','         ','01-JAN-1900','31-DEC-9999');

Thanks,

Joel Seguin
Sr Systems Analyst

 

[carp]

INSERT only does one row. There are exceptions to this, such as, if you have another table with the data you want to insert, you can insert the results of a query and get multiple rows loaded. But if you are supplying explicit values (as you show), you can only insert one row at a time.

 

[SantaMufasa]

Joel, Sorry to be the bearer of bad news, but Oracle's "generic" INSERT statement does not allow for multiple rows of explicit information to INSERT from one invocation of the INSERT verb.

You can, however, achieve multiple rows of INSERTs from a single SQL statement via either the:

CREATE TABLE <table_name> AS SELECT <select statement>;

-OR-

INSERT INTO <table_name> SELECT <select statement>;

If the data does not already reside in a table, then a clever way to achieve the above is to place all of the data into a flat file, then use Oracle's "EXTERNAL TABLE" access method, which we can discuss further (or direct you to links of other threads on that topic) if you desire.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Darn, I wasn't aware of this.

Mufasa, sure I'd love to hear more info about the External Table method. I will have an external flat file available so if there is a way in SQL to import the data (from a local machine?) I'll take it. Can you give me an example?

Thanks

Joel

 

[SantaMufasa]

I'll be happy to do so. To make the example most meaningful, how about if you post a sample row or two of the flat file. The data can be either delimited or fixed-length fields (preferrably delimited). Also, please post a "describe <table_name>" of the target table and the name of the flat file, qualified fully by the path to it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Alright thank you

Path: C:\Temp\tblCountyFactors.txt

Here is some comma delimited data:

'AK','dm1','Dummy1',267,0.84,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm1','Dummy1',269,0.84,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm2','Dummy2',99,1.22,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm2','Dummy2',100,1.00,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm2','Dummy2',251,1.22,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm2','Dummy2',252,1.00,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm2','Dummy2',267,0.83,'Y','N','         ','01-JAN-1900','31-DEC-9999'
'AK','dm2','Dummy2',269,0.83,'Y','N','         ','01-JAN-1900','31-DEC-9999'

Here is the create table SQL (I only understand a bit of this)

-- Create table
create table
(
  STATE              VARCHAR2(2),
  COUNTY             VARCHAR2(3),
  COUNTY_NAME        VARCHAR2(22),
  PRODUCT_NUM        NUMBER,
  COUNTY_FACTOR      NUMBER,
  NETWRK_INPLACE_IND VARCHAR2(1),
  MULTI_ZIPCODE_IND  VARCHAR2(1),
  ZIPCODE            VARCHAR2(9),
  VALID_FROM         DATE,
  VALID_TO           DATE
)
tablespace BKB_USERS_TABSP
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 160K
    next 384K
    minextents 1
    maxextents unlimited
    pctincrease 1
  );
-- Add comments to the table 
comment on table 
  is 'Type a Description Here';
-- Create/Recreate indexes 
create index UWTBL_COUNTY_FACTORS_IX on  (STATE, COUNTY, COUNTY_NAME, PRODUCT_NUM, ZIPCODE)
  tablespace BKB_USERS_TABSP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 160K
    next 384K
    minextents 1
    maxextents unlimited
    pctincrease 1
  );

 

[SantaMufasa]

First, Rusty, there are a couple of issues with your code and your original "CREATE TABLE..." code and your data, above:

1) "CREATE TABLE..." must have a valid table name.
2) We consider the ' ' entry on each record, above, to be poor form/extraneous in Oracle. We don't store any trailing blank spaces, and for a completely blank field, we, instead, store '' (two successive single quotes), which represent NULL as the content of the expression.

Following is code that:

1) Creates an EXTERNAL TABLE from your flat file,
2) Displays your data via a SELECT against the external table,
3) Creates another, standard table from your external table,
4) Displays your data in the standard table.

set feedback on
create directory RustyDir as 'c:\temp'
/

Directory created.

create table RustyTab1
(
  STATE              VARCHAR2(2),
  COUNTY             VARCHAR2(3),
  COUNTY_NAME        VARCHAR2(22),
  PRODUCT_NUM        NUMBER,
  COUNTY_FACTOR      NUMBER,
  NETWRK_INPLACE_IND VARCHAR2(1),
  MULTI_ZIPCODE_IND  VARCHAR2(1),
  ZIPCODE            VARCHAR2(9),
  VALID_FROM         DATE,
  VALID_TO           DATE
)
organization external
(  type oracle_loader
   default directory RustyDir
   access parameters
   (records delimited by newline
    fields terminated by ',' optionally enclosed by "'"
   )
location ('tblCountyFactors.txt')
)
reject limit unlimited;

Table created.

select * from RustyTab1;

       COUNTY
ST COU NAME   PRODUCT_NUM COUNTY_FACTOR N M ZIPCODE   VALID_FRO VALID_TO
-- --- ------ ----------- ------------- - - --------- --------- ---------
AK dm1 Dummy1         267           .84 Y N           01-JAN-00 31-DEC-99
AK dm1 Dummy1         269           .84 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2          99          1.22 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         100             1 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         251          1.22 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         252             1 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         267           .83 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         269           .83 Y N           01-JAN-00 31-DEC-99

8 rows selected.

SQL> create table RustyTab2 as select * from RustyTab1;

Table created.

SQL> rem Following UPDATE removes extraneous blanks from ZIPCODE
SQL> update RustyTab2 set ZIPCODE = trim(ZIPCODE);

8 rows updated.

SQL> select * from RustyTab2;

       COUNTY
ST COU NAME   PRODUCT_NUM COUNTY_FACTOR N M ZIPCODE   VALID_FRO VALID_TO
-- --- ------ ----------- ------------- - - --------- --------- ---------
AK dm1 Dummy1         267           .84 Y N           01-JAN-00 31-DEC-99
AK dm1 Dummy1         269           .84 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2          99          1.22 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         100             1 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         251          1.22 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         252             1 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         267           .83 Y N           01-JAN-00 31-DEC-99
AK dm2 Dummy2         269           .83 Y N           01-JAN-00 31-DEC-99

8 rows selected.

Let us know if you have questions regarding the above code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

This looks really good, I'll give it a try and report back any problems. Thanks Mufasa, this is a big help!

 

[Dagon]

Sorry to be the bearer of bad news, but Oracle's "generic" INSERT statement does not allow for multiple rows of explicit information to INSERT from one invocation of the INSERT verb.

Actually, it does.

create table x (x number);

insert all
   into x (x) values (1)
   into x (x) values (2)
   into x (x) values (3)
   select * from dual;

 

[SantaMufasa]

Cool, Dagon ! You provided my LSNED concept. (...Learn Something New Every Day). Hava

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Is the "select * from dual;" statement required in an Insert ALL statement?

 

[Dagon]

Yes, in the sense that it doesn't work without it.

 

[RustyAfro]

Alright, very helpful, thanks again!! Star from me too

 

[jimirvine]

2) We consider the ' ' entry on each record, above, to be poor form/extraneous in Oracle. We don't store any trailing blank spaces, and for a completely blank field, we, instead, store '' (two successive single quotes), which represent NULL as the content of the expression.

Hmmm..:

SQL> create table tab1 (a varchar2(30));

Table created.

SQL> insert into tab1 values ('aaaa');

1 row created.

SQL> insert into tab1 values ('    ');

1 row created.

SQL> select length(a) from tab1;

 LENGTH(A)
----------
         4
         4

I also wouldn't say that '' represents Null. Null represents Null. '' is CONVERTED to Null.
(But that is purely nit picking )

 

[SantaMufasa]

I also wouldn't say that '' represents Null. Null represents Null. '' is CONVERTED to Null.

Yes, Jim, I would agree that Oracle converts '' to NULL when storing information. I always prefer using NULL for all references to the absence of value. I used the two single quotes ('') to create an unmistakable visual distinction from Joel's original (' ').


So that there is no confusion as to the contents (or lack thereof) in database-values storage, I'll post this code (as an extension of yours, Jim):

SQL> create table tab1 (a varchar2(30));

Table created.

SQL> insert into tab1 values ('aaaa');

1 row created.

SQL> insert into tab1 values ('    ');

1 row created.

SQL> insert into tab1 values ('');

1 row created.

SQL> insert into tab1 values (null);

1 row created.

SQL> select nvl(length(a),0) from tab1;

NVL(LENGTH(A),0)
----------------
               4
               4
               0
               0

SQL> select count(*) from tab1 where a is null;

  COUNT(*)
----------
         2

We can see clearly that:

a) ' ' and '' represent very different situations, and
b) Oracle stores '' as NULL.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]