Clever Geek Handbook

Is it possible to dynamically create external tables in PLSQL / Oracle

I am currently having a bit of a problem and am trying to dynamically pull values ​​from CSV files and create external tables dynamically as each CSV file will have a different number of headers each time.

Is there a way to dynamically do this, the research I have done tells me that you cannot, but if you could, it would be quite difficult through oracle threads, and I also failed to achieve this functionality.

Has anyone tried this or had experience with this who could offer some 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;
+3


source to share


2 answers


To do this, use the immediate operation:

execute immediate 'DROP TABLE TEST_CSV CASCADE CONSTRAINTS';


Etc. It has no differences from a regular team. But you may have permission issues. Oracle dynamic sql usually has some bugs and its behavior may differ from normal DDL command

0


source


I recommend that you create a dynamic procedure that creates an external table so you can control the voice by parameter as well as the directory.

WATCH OUT, you should think about all the cases that can happen in this dynamic procedure.

It works, I've done it before.



Any question just let me know.

Thank.

-1


source






More articles:


All Articles