CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
steve
steve

Reputation: 362

dynamic directory for external table in procedure (oracle)

I am using the following Procedure in pl sql

       CREATE OR REPLACE PROCEDURE LOAD ( file_path VARCHAR2)
    AS
    BEGIN
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_tab_data AS '''||file_path||''' ';
    EXECUTE IMMEDIATE '
    CREATE TABLE DUMMY
     (
         col1      VARCHAR2(2000),
         col2     VARCHAR2(2000)
    ) 
    ORGANIZATION EXTERNAL
       (
             TYPE oracle_loader
             DEFAULT DIRECTORY ext_tab_data
             ACCESS PARAMETERS 
             (
                   RECORDS DELIMITED BY NEWLINE
                   badfile ext_tab_data:''bad_file.bad''
                   logfile ext_tab_data:''logs.log''
                    RECORDS DELIMITED BY NEWLINE
                   (FIELDS TERMINATED BY '','')
                   MISSING FIELD VALUES ARE NULL
                   ( 
                             col1  CHAR ,
                             col2  CHAR  

             ))
                 LOCATION (''F1.txt'')
    )REJECT LIMIT UNLIMITED;';
    END;

/
EXEC LOAD('C:\Users\external_table_new');

But this doesn't work, i'm getting this error

exec load
Error report -
ORA-00922: missing or invalid option
ORA-06512: at "SYSTEM.LOAD", line 7
ORA-06512: at line 1
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

oracle execute immediate.Can anyone help please? i want directory name to be added dynamically when the procedure is run by passing a parameter.

Upvotes: 0

Views: 824

Answers (1)

Jon Heller
Jon Heller

Reputation: 36832

Dynamic SQL statements should not be terminated by a semicolon. Remove the last ; from the create table statement in the execute immediate.

You might also want to look into using the alternative quoting syntax. This will allow you to include the dynamic SQL without as much escaping.

For example:

execute immediate q'[
   create table dummy ...
   ...
                   badfile ext_tab_data:'bad_file.bad'
   ...
    )REJECT LIMIT UNLIMITED
]';

Upvotes: 1

Related Questions