Creating external tables in Oracle procedure

Question

I'm using Oracle 11g and I'm having in issue with creating an external table in a procedure. It gets created with no errors but when I execute the procedure I'm having errors.

The first parameter is the name of the file and the second is a comma because I was having issues with using single quotations to surround the comma where I specify the fields terminated by section. DATA_DIR was declared.

Here's what I tried.

CREATE OR REPLACE PROCEDURE LOADTABLE
(
    FILENAME VARCHAR2,
    COMMA VARCHAR
)
AS
BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE LOAD
    (
       USERNAME VARCHAR2(30)
    )
    ORGANIZATION EXTERNAL
    (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY DATA_DIR
        ACCESS PARAMETERS
        ( FIELDS TERMINATED BY :COMMA)
        LOCATION (:FILENAME)
    )' USING IN COMMA, FILENAME;
END;

This is how I call the procedure

EXEC LOADTABLE('username.csv',',');

This is the error I get

ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "DATA_ADMIN.LOADTABLE", line 9
ORA-06512: at line 1

Any help will be appreciated.

Answer 1

You can only bind variables, and the external table creation syntax requires text literals for the elements you're trying to bind.

You'll have to use concatenation instead:

CREATE OR REPLACE PROCEDURE LOADTABLE
(
  FILENAME VARCHAR2,
  COMMA VARCHAR
)
AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE LOAD
(
  USERNAME VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_DIR
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY ''' || COMMA || ''')
  LOCATION (''' || FILENAME || ''')
)';
END;
/

Procedure LOADTABLE compiled

EXEC LOADTABLE('username.csv',',');

PL/SQL procedure successfully completed.

In that I've escaped the single quotes around the concatenated string values. In the question you mentioned that you're only passing the comma because you were "having issues with using single quotations to surround the comma"; escaping them by doubling them up is the way to do that, so if you do always want a comma separator you can instead do:

CREATE OR REPLACE PROCEDURE LOADTABLE
(
  FILENAME VARCHAR2
)
AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE LOAD
(
  USERNAME VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_DIR
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY '','')
  LOCATION (''' || FILENAME || ''')
)';
END;
/

EXEC LOADTABLE('username.csv');

However, creating (and presumably dropping) objects on the fly isn't generally a good idea. It would be better to create the external table once, which would be done with static DDL:

CREATE TABLE LOAD
(
  USERNAME VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_DIR
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY ',')
  LOCATION ('dummy')
);

and then just alter the table to have a new file name statically too:

alter table load location ('username.csv');

or if you really want a procedure to do it:

CREATE OR REPLACE PROCEDURE LOADTABLE
(
  FILENAME VARCHAR2
)
AS
BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE LOAD LOCATION (''' || FILENAME || ''')';
END;
/

EXEC LOADTABLE('username.csv');