Error Converting an External Table Creation Instruction to a PLSQL Procedure

Question

I'm having some problem converting a single External table instruction into a PLSQL Procedure. In particular, this is the External Table create instruction that works perfectly.

create table TMP_TBL (
  id VARCHAR2(10)
)
organization external (
  TYPE ORACLE_LOADER
  default directory DATA_DIR
  access parameters (
    RECORDS DELIMITED BY NEWLINE
    fields terminated by '|'
    missing field values are null
  )
  location ('test.txt')
)
reject limit unlimited;

I'm trying to make out of this a PLSQL procedure that creates the table given the external data file. This is what i've done since now but i'm still having some problem when i'm calling it.

PROCEDURE CREATE_TMP_TBL(FILENAME VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'create table TMP_TBL (
      id VARCHAR2(10)
    )
    organization external (
      type oracle_loader
      default directory DATA_DIR
      access parameters (
        records delimited by newline
        fields terminated by ''|''
        missing field values are null
      )
      location ('''||FILENAME||''')
    )
    reject limit unlimited;';
  END CREATE_TMP_TBL;

Executing the procedure with the following command:

exec pkg_load.CREATE_TMP_TBL('test.txt');

It gives me this error:

Error report:
ORA-00911: invalid character
ORA-06512: at "PKG_LOAD", line 43
ORA-06512: at line 1
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:

line 43 is the "BEGIN" number line of the procedure CREATE_TMP_TBL.

Info:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE   11.2.0.2.0  Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Any suggestion to fix this problems are welcome. Thanks in Advice,

Carlo

If all you want to potentially do is to change the file that is referenced, You can do that with the ALTER TABLE statement - see here for all changes you can make to an external table (LOCATION attribute). docs.oracle.com/cd/B28359_01/server.111/b28310/… — OldProgrammer

DazzaL DazzaL · Accepted Answer · 2013-02-17T16:36:40

remove the semicolon from the end of the statement here:

reject limit unlimited;';

ie change to

reject limit unlimited';

Error Converting an External Table Creation Instruction to a PLSQL Procedure

1 Answers