Search code examples
sqlcsvplsqloracle10gexternal

Is it possible to Dynamically Create External tables in PLSQL/Oracle

Am currently having a bit of trouble and am trying to dynamically pull values out of CSV files and dynamically create the external tables as each CSV file is going to have a different number of headers each time.

Is there any way to dynamically to do this, the research I have done is telling me you cant, but if you could it would be pretty difficult via oracle threads and I also have not had any luck achieving this functionality.

Has anyone tried this or had experience in it who could offer a bit of help or advice?

My current script:

DROP TABLE TEST_CSV CASCADE CONSTRAINTS;

CREATE TABLE TEST_CSV
(
  VAL1  VARCHAR2(255 BYTE),
  VAL2  VARCHAR2(255 BYTE),
  VAL3  VARCHAR2(255 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DATALOAD
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE
       FIELDS TERMINATED BY ","
       )
     LOCATION (DATALOAD:'test1.csv')
  )
REJECT LIMIT UNLIMITED;
Solution

  • You should use execute immediate statement for this:

    execute immediate 'DROP TABLE TEST_CSV CASCADE CONSTRAINTS';

    And etc... It hasn't any differences with usual command using. But you can have some troubles with permissions. Oracle dynamic sql usually has some bugs and his behavior can be different with normal DDL command