Creating external table and loading this data into another table inside procedure

Question

I'm trying to create a procedure, which allows to load data from file into external table. Next it should create: sequence and trigger, which allow to generate automatically the ID during the inserting data from external table into other table. I wrote the whole procedure, but there is a lot of problems, so I started from the beginning - just to create external table from file.

CREATE OR REPLACE PROCEDURE LOAD_TO_EXTERNAL AS
  PATH_FILE VARCHAR2(100) := 'TEST_FILE.TXT';
  DELIMETER VARCHAR2(100) := '\n';
  DATE_FORMATTING VARCHAR2(100) := 'YYYY-MM-DD';
  STMT VARCHAR2(5000);
BEGIN
  STMT := 'CREATE TABLE "TEST_EXT"
    (
      DATA DATE,
      NAME VARCHAR2(5),
      CODE VARCHAR2(5)
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER DEFAULT DIRECTORY MY_DIR ACCESS PARAMETERS
      (RECORDS DELIMITED BY' || DELIMETER || '
      FIELDS LRTRIM (
          DATA (1:10) CHAR(10) DATE_FORMAT DATE MASK ' || DATE_FORMATTING || ',
          NAME (39:78) CHAR(40),
          CODE (79:80) CHAR(2)
         )
      ) LOCATION (' || PATH_FILE || ')
    )
    REJECT LIMIT UNLIMITED';

    EXECUTE IMMEDIATE STMT;

END LOAD_TO_EXTERNAL;

Unfortunately, I'm getting this error, which points to EXECUTE IMMEDIATE:

ORA-00905: missing keyword
ORA-06512: at "SYSTEM.LOAD_TO_EXTERNAL_SEQ", line 34
ORA-06512: at line 2

I also so suggestion to put the STMT between: ('BEGIN ' || STMT || 'END;'), but then I'm getting this error:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
   json_exists json_value json_query json_object json_array

I have no idea what is wrong with this...

I tried without creating the STMT variable, by putting the statement directly in EXECUTE IMMEDIATE, but it's the same...

What is more, I know that there will be next problem to load data from external table into other table. I was getting message that external table does not exist, which is true, but it will be created before calling the code, which will loads data from external to normal table.

I would appreciate your help!

**EDIT: **

The @Guenther answer is correct - missing extra qoutes. I discovered this today morning. But right now I have a different problem... Load FLOAT data into the external table... Data like that: +00000700000,00. Any ideas how to do this? FLOAT EXTERNAL or FLOAT(15) does not work... Of course in CREATE statement I have FLOAT column.

Guenther · Accepted Answer · 2017-09-28T14:15:40.317

The following works fine for me. I just added an extra space after delimited by and extra quotes where needed.

create or replace PROCEDURE LOAD_TO_EXTERNAL AS
  PATH_FILE VARCHAR2(100) := 'TEST_FILE.TXT';
  DELIMETER VARCHAR2(100) := '-';
  DATE_FORMATTING VARCHAR2(100) := 'YYYY-MM-DD';
  STMT VARCHAR2(5000);
BEGIN
  STMT := 'CREATE TABLE "TEST_EXT"
    (
      DATA DATE,
      NAME VARCHAR2(5),
      CODE VARCHAR2(5)
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER DEFAULT DIRECTORY MY_DIR ACCESS PARAMETERS
      (RECORDS DELIMITED BY ''' || DELIMETER || '''
      FIELDS LRTRIM (
          DATA (1:10) CHAR(10) DATE_FORMAT DATE MASK ''' || DATE_FORMATTING || ''',
          NAME (39:78) CHAR(40),
          CODE (79:80) CHAR(2)         )
      ) LOCATION (''' || PATH_FILE || ''')
    )
    REJECT LIMIT UNLIMITED';

    EXECUTE IMMEDIATE STMT;
END LOAD_TO_EXTERNAL;

After that

begin
 LOAD_TO_EXTERNAL();
end;

creates the table. Make sure the schema owner has read rights to the directory.

@lui I edited my answer. add missing space and extra quotes. Let me know if this works for you as well. — Guenther, Sep 28 '17 at 14:16
Yes, I discovered the missing quotes today morning, but I haven't time to give the correct version ;) Do you know maybe how to load the `FLOAT` data? Look at my updated question. — Lui, Sep 28 '17 at 15:15

Creating external table and loading this data into another table inside procedure

1 Answers1