Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00905, ORA-06512 during Create External Table in PL/SQL (Oracle Express 10g, 10.2.0, Windows XP Professional SP2)
icon2.gif  ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310444] Tue, 01 April 2008 05:19 Go to next message
Hann
Messages: 5
Registered: April 2008
Location: Germany
Junior Member
Hi there,
I am new here an hope to get some useful answers to my questions.

I want to import diffferent ".csv"-files with a timestamp in the name. An example for the filename would be "Datalogger_2008_03_27.csv".
To do the process automaically I decided to use windows scheduler to load a batch-file daily which will execute sqlplus. With sqlplus I want to execute a PL/SQL procedure which loads the data from the file into an external table.
(With PL/SQL I want to use a loop to load different files into the external table and then insert the data into real tables.)

First Question: Is this the right way to solve my issue?

Secondly I hope someone could help me with my problem with the external tables. I tested the SQL-Statement to Create the external table and it works fine.
However, when trying to execute it through PL/SQL it returns the following:

Prozedur wurde erstellt.

BEGIN exttabletest; END;

*
FEHLER in Zeile 1:
ORA-00905: Schlüsselwort fehlt
ORA-06512: in "SYSTEM.EXTTABLETEST", Zeile 40
ORA-06512: in Zeile 1



So well.., heres my code:

create or replace
PROCEDURE exttabletest 
IS

BEGIN

DECLARE

 --First I obtain information from a table which tells me about the files I want to import

 datumheute   VARCHAR2(10):= sysdate;  
 tag          VARCHAR2(2):= substr(sysdate, 1, 2);
 monat        VARCHAR2(2):=substr(datumheute, 4, 2);
 jahr         VARCHAR2(2):=substr(datumheute, 7, 2);
 dateischwanz VARCHAR2(50):=
rpad(rpad(rpad(rpad(rpad(rpad('_20', 5, jahr), 6, '_'), 8, monat), 9, '_'), 11, tag), 15, '.csv');
 dateiname    VARCHAR2(50);
 dir          CSV_DATEIEN.CSV_DIR%TYPE; 
 prefix       CSV_DATEIEN.CSV_PREFIX%TYPE; 
 delimiter    CSV_DATEIEN.CSV_DELIMITER%TYPE; 
 opt          CSV_DATEIEN.CSV_OPT%TYPE;  
 zeile1name   CSV_DATEIEN.CSV_ZEILE1NAME%TYPE; 
 testfile     VARCHAR2(30) := 'Datenlogger_2008_03_27.csv'; -- for testing purposes
 
 CURSOR TABLE_CURSOR IS 
 SELECT CSV_DIR, CSV_PREFIX, CSV_DELIMITER, CSV_OPT, CSV_ZEILE1NAME
 FROM CSV_DATEIEN WHERE CSV_AKTIV='1'; 

BEGIN
 
 --1: The Loop starts
 
  open TABLE_CURSOR;
  loop
 
    fetch TABLE_CURSOR into 
    dir, prefix, delimiter, opt, zeile1name; 
    dateiname:=rpad(prefix, 26, dateischwanz);
  
--2: import to external table (This is the problem part!!)

    EXECUTE IMMEDIATE 'CREATE TABLE test1_ext ( 
                       datum varchar2(10),zeit varchar2(8),energie varchar2(16),
id varchar2(3),power varchar2(10),
rlt varchar2(10),vlt varchar2(10),volume varchar2(16),
flow varchar2(12))
                       ORGANIZATION EXTERNAL ( 
                       TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus 
                       ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE 
                       FIELDS TERMINATED BY '','' 
                       MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS) 
                       LOCATION (testfile)
                       ) REJECT LIMIT UNLIMITED PARALLEL';

--3: Later I want to insert the data into a real table.....

  exit when TABLE_CURSOR%NOTFOUND; 
  end loop; 
 close TABLE_CURSOR;
END;

END exttabletest;
/
EXEC exttabletest


Finally I want to add that I tested around for days, searched forums and google a lot but I couldnt solve the problem myself.
Maybe I am missing something? Maybe I didnt see the obvious answer?

For executing the code I used SQL*Plus Worksheet from Oracle Enterprise Manager. I executed the Code as user SYSTEM. To be sure I got all the rights I logged in as sysdba and GRANTED CREATE ANY TABLE and CREATE ANY DIRECTORY to SYSTEM.

Thanks for your help!
Hann

[Updated on: Tue, 01 April 2008 06:47] by Moderator

Report message to a moderator

Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310469 is a reply to message #310444] Tue, 01 April 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Keep your code line in 80 characters
2/
ORA-00905: missing keyword

I don't know where is like 40 but check your code.
3/ Why do you create the table on the fly? Why don't you previously create it?

Regards
Michel
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310474 is a reply to message #310444] Tue, 01 April 2008 07:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi,

1. Your procedure performs a loop (cursor), but you are trying to create a sinlge external table named test1_ext.
The second iteration will fail with "ALREADY EXISTS' error message.
2. You are generating DDL (CREATE command) so no variables are allowed.
-- What is " sqlplus " ? in
"TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus"
-- Did you create such directory?
3. Concatenate testfile name instead of using variable:
LOCATION ( ''' || testfile || ''' )


HTH.
Michael
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310480 is a reply to message #310444] Tue, 01 April 2008 07:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

ORA-00905: Schlüsselwort fehlt


You are getting above error because of the location keyword.
  1  begin
  2  execute immediate 'CREATE TABLE test1_ext (
  3                         datum varchar2(10))
  4                         ORGANIZATION EXTERNAL (
  5                         TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus
  6                         ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
  7                         FIELDS TERMINATED BY '','')
  8                         LOCATION (testfile)
  9                         ) REJECT LIMIT UNLIMITED PARALLEL';
 10* end;
SQL> /
begin
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 2

Modified version is
  1  declare
  2    testfile varchar2(30);
  3    begin
  4    testfile := 'Helloworld.txt';
  5    execute immediate 'CREATE TABLE test1_ext (
  6                           datum varchar2(10))
  7                           ORGANIZATION EXTERNAL (
  8                           TYPE ORACLE_LOADER DEFAULT DIRECTORY crm_data
  9                           ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
 10                           FIELDS TERMINATED BY '','')
 11                           LOCATION ('''||testfile||''')
 12                           ) REJECT LIMIT UNLIMITED PARALLEL';
 13*  end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select table_name, location from user_external_locations
  2  where table_name = 'TEST1_EXT';

TABLE_NAME                     LOCATION
------------------------------ ------------------------------
TEST1_EXT                      Helloworld.txt

SQL> alter table test1_ext location('myfile.txt');

Table altered.

SQL> select table_name, location from user_external_locations
  2  where table_name = 'TEST1_EXT';

TABLE_NAME                     LOCATION
------------------------------ ------------------------------
TEST1_EXT                      myfile.txt

But as @Michael why are you creating the objects on the fly. If the table structure is going to be same create the table statically and you can modify the name of the file at runtime. For more information check the following link.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2104274

Hope that helps.

Regards

Raj
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310503 is a reply to message #310444] Tue, 01 April 2008 07:54 Go to previous messageGo to next message
Hann
Messages: 5
Registered: April 2008
Location: Germany
Junior Member
Hi all,

thanks for your nice and helpful replies!
(Sorry for the long code, and yes I created a directory named "sqlplus").

I just did like you said and changed variable to ''' || variable || ''' and now it works (not in loop)! Thank you!

I will further change my procedure and do it like this:

1: Read different csv-filenames and the names of the productive tables from a table
2: Creating an external Table with fixed structure by loading an example-file
3: Writing data from (1) in a cursor -> LOOP
3.1: update the data in the external table by using "alter table..."
3.2: insert the data from the external table into a productive table
4. delete the external table

Could you please correct me if my idea is wrong? (especially 3.1: could i use "alter table")???

And could you please tell me which statement I need to use to insert the data into a producitve table and which I need to delete an external table.

Thanks for all the answers!
Hann

[Updated on: Tue, 01 April 2008 07:55]

Report message to a moderator

Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310507 is a reply to message #310503] Tue, 01 April 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to do steps 2 and 4?
Create the external table once and keep it forever.

Regards
Michel
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310509 is a reply to message #310503] Tue, 01 April 2008 08:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Say for example you are having
a) File120080101.txt
b) File220080102.txt
c) File320080103.txt
Do you want to insert data from all these files into the same table or you want to insert data from these files to different tables ? If it is going to be the same table, instead of altering the location everytime you can merge these files into a single file and load it one go.
Quote:
4. delete the external table

I presume you are asking how to drop the table. External table is like any other oracle table but you cannot delete any rows from external table because DML operations are not allowed in external table as they sit outside oracle. For more information about external tables check the oracle reference manual.

Regards

Raj

[Updated on: Tue, 01 April 2008 08:08]

Report message to a moderator

Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310518 is a reply to message #310444] Tue, 01 April 2008 08:22 Go to previous messageGo to next message
Hann
Messages: 5
Registered: April 2008
Location: Germany
Junior Member
Okay,
I do not really know if it would be better to
- once create the external table and reuse it every time
or to
- create every time the procedure starts a new external table and then drop it at the end of the procedure.

What I know is that I have different .csv-files which all have the same structure but could have variable length (lines).
I want to write them into different productive tables...

Right now I prefer to create and drop a new table every time because I do not know how to load data from a file into an existing external table without creating the table first.

Maybe you could help me with the "update"/alter of the external table.
I want to load the data from the next csv-file into the external table, using "Alter table...".
After I altered the table the table still has the same number of lines even if my new csv-file has a lot of more lines.
The value of all the lines is NULL.

Thank you
Hann

create or replace
PROCEDURE csv_import
IS
BEGIN
DECLARE
 datumheute   VARCHAR2(10):= sysdate;  
 tag          VARCHAR2(2):= substr(sysdate, 1, 2);
 monat        VARCHAR2(2):=substr(datumheute, 4, 2);
 jahr         VARCHAR2(2):=substr(datumheute, 7, 2);
 dateischwanz VARCHAR2(50):=rpad(rpad(rpad(rpad(rpad(rpad('_20', 5, jahr), 6, '_'), 8, monat), 9, '_'), 11, tag), 15, '.csv');
 dateiname    VARCHAR2(50);
 dir          CSV_DATEIEN.CSV_DIR%TYPE; 
 prefix       CSV_DATEIEN.CSV_PREFIX%TYPE; 
 delimiter    CSV_DATEIEN.CSV_DELIMITER%TYPE; 
 opt          CSV_DATEIEN.CSV_OPT%TYPE;  
 zeile1name   CSV_DATEIEN.CSV_ZEILE1NAME%TYPE; 
 dummy        VARCHAR2(30) := 'dummy.csv';
 CURSOR TABLE_CURSOR IS 
 SELECT CSV_DIR, CSV_PREFIX, CSV_DELIMITER, CSV_OPT, CSV_ZEILE1NAME
 FROM CSV_DATEIEN WHERE CSV_AKTIV='1'; 
BEGIN
--1: create extern table
EXECUTE IMMEDIATE 'CREATE TABLE test4_ext ( 
datum varchar2(10),
zeit varchar2(8),
energie varchar2(16),
id varchar2(3),
power varchar2(10),
rlt varchar2(10),
vlt varchar2(10),
volume varchar2(16),
flow varchar2(12))
ORGANIZATION EXTERNAL ( 
TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus 
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE 
FIELDS TERMINATED BY ''' || delimiter || ''' 
MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS) 
LOCATION (''' || dummy || ''')
) 
REJECT LIMIT UNLIMITED PARALLEL';
--2: The Loop starts
  open TABLE_CURSOR;
  loop
    fetch TABLE_CURSOR into 
    dir, prefix, delimiter, opt, zeile1name; 
    dateiname:=rpad(prefix, 26, dateischwanz);
    dateiname:='Datenlogger_2008_03_27.csv';
--3: import to external table
      EXECUTE IMMEDIATE 'alter table test4_ext location(''' || dateiname || ''')';
--4: Later I want to insert the data into a real table.....
  exit when TABLE_CURSOR%NOTFOUND; 
  end loop; 
 close TABLE_CURSOR;
--5: Later I want to drop the external table here...
END;
END csv_import;
/
EXEC csv_import
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310613 is a reply to message #310518] Tue, 01 April 2008 15:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Right now I prefer to create and drop a new table every time because I do not know how to load data from a file into an existing external table without creating the table first.


I think you need to read the oracle manual about external tables. You cannot do any DML operations on an external table.
Quote:
dateiname:='Datenlogger_2008_03_27.csv';

Quote:
After I altered the table the table still has the same number of lines even if my new csv-file has a lot of more lines.

I hope it answers your question.

Regards

Raj
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310704 is a reply to message #310444] Wed, 02 April 2008 02:29 Go to previous messageGo to next message
Hann
Messages: 5
Registered: April 2008
Location: Germany
Junior Member
Hi all!

Just wanted to tell you that my script now works fine.

I will go into oracle manual a bit further but at least i know that its working. Thanks for your help.

If anyone is interested in the solution, heres my final script, which is working 100%:

create or replace PROCEDURE csv_import
IS
BEGIN
DECLARE
 datumheute   VARCHAR2(10):= sysdate;  
 tag          VARCHAR2(2):= substr(sysdate, 1, 2);
 monat        VARCHAR2(2):=substr(datumheute, 4, 2);
 jahr         VARCHAR2(2):=substr(datumheute, 7, 2);
 dateischwanz VARCHAR2(50):=rpad(rpad(rpad(rpad(rpad(
 rpad('_20', 5, jahr), 6, '_'), 8, monat), 9, '_'), 11, tag),
 15, '.csv');
 dateiname    VARCHAR2(50);
 dir          CSV_DATEIEN.CSV_DIR%TYPE; 
 prefix       CSV_DATEIEN.CSV_PREFIX%TYPE; 
 delimiter    CSV_DATEIEN.CSV_DELIMITER%TYPE; 
 opt          CSV_DATEIEN.CSV_OPT%TYPE;  
 zeile1name   CSV_DATEIEN.CSV_ZEILE1NAME%TYPE; 
 dummy        VARCHAR2(30) := 'dummy.csv';
 CURSOR TABLE_CURSOR IS 
 SELECT CSV_DIR, CSV_PREFIX, CSV_DELIMITER, CSV_OPT, CSV_ZEILE1NAME
 FROM CSV_DATEIEN WHERE CSV_AKTIV='1'; 
BEGIN
--1: create extern table
EXECUTE IMMEDIATE 'CREATE TABLE test1_ext ( 
datum varchar2(10),
zeit varchar2(8),
energie varchar2(16),
id varchar2(3),
power varchar2(10),
rlt varchar2(10),
vlt varchar2(10),
volume varchar2(16),
flow varchar2(12))
ORGANIZATION EXTERNAL ( 
TYPE ORACLE_LOADER DEFAULT DIRECTORY wmzdaten 
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE 
FIELDS TERMINATED BY '';'' 
MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS) 
LOCATION (''' || dummy || ''')
) 
REJECT LIMIT UNLIMITED PARALLEL';
--2: The Loop starts
  open TABLE_CURSOR;
  loop
    fetch TABLE_CURSOR into 
    dir, prefix, delimiter, opt, zeile1name; 
    dateiname:=rpad(prefix, 26, dateischwanz);
    dateiname:='Datenlogger_2008_03_27.csv';
--3: import to external table
      EXECUTE IMMEDIATE 'alter table test1_ext location(''' || dateiname || ''')';
--4: insert the data into the productive table
      EXECUTE IMMEDIATE 'INSERT INTO test1 SELECT * FROM test1_ext';
  exit when TABLE_CURSOR%NOTFOUND; 
  end loop; 
 close TABLE_CURSOR;
--5: drop the external table
EXECUTE IMMEDIATE 'DROP TABLE test1_ext';
END;
END csv_import;
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310780 is a reply to message #310704] Wed, 02 April 2008 07:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

dateiname:=rpad(prefix, 26, dateischwanz);
dateiname:='Datenlogger_2008_03_27.csv';


What is the use of first statement ? That value is getting overwritten by a constant every time.

Regards

Raj
Re: ORA-00905, ORA-06512 during Create External Table in PL/SQL [message #310822 is a reply to message #310444] Wed, 02 April 2008 09:03 Go to previous message
Hann
Messages: 5
Registered: April 2008
Location: Germany
Junior Member
Hi!
The overwrite of the variable was just for testing purposes.
I made my tests with that file.
Regards
Hann
Previous Topic: outer join
Next Topic: use of logminer in a procedure
Goto Forum:
  


Current Time: Thu Dec 08 00:26:59 CST 2016

Total time taken to generate the page: 0.09830 seconds