Создание внешних таблиц в процедуре Oracle

Question

Я использую Oracle 11g, и у меня возникают проблемы с созданием внешней таблицы в процедуре. Он создается без ошибок, но когда я выполняю процедуру, у меня возникают ошибки.

Первый параметр - это имя файла, а второй - запятая, потому что у меня возникли проблемы с использованием одинарных кавычек для окружения запятой, где я определяю поля, заканчивающиеся разделом. DATA_DIR был объявлен.

Вот что я попробовал.

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;

Вот как я называю процедуру

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

Это ошибка, которую я получаю

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

Любая помощь будет оценена.

Answer 1

Вы можете связывать только переменные, а синтаксис создания внешней таблицы требует текстовых литералов для элементов, которые вы пытаетесь связать.

Вместо этого вам придется использовать конкатенацию:

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.

В этом я избежал одинарных кавычек вокруг объединенных строковых значений. В вопросе, который вы упомянули, вы пропускаете только запятую, потому что у вас "возникли проблемы с использованием одинарных кавычек для окружения запятой"; способ сделать это, удвоив их, является способом сделать это, поэтому, если вы всегда хотите использовать разделитель запятых, вы можете вместо этого сделать:

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');

Однако создание (и, по-видимому, удаление) объектов на лету, как правило, не очень хорошая идея. Было бы лучше создать внешнюю таблицу один раз, что было бы сделано со статическим DDL:

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

а затем просто измените таблицу, чтобы статически иметь новое имя файла:

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

или если вы действительно хотите, чтобы процедура делала это:

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

EXEC LOADTABLE('username.csv');