Re: Missing identifier when Using Parameter in Execute Immediate

Resant wrote:
> Please help me, what's wrong with this script.
> When i remove the parameter ps_filename, it's executed successfully,
> but after I add the parameter, show error :
>
> ORA-00931: missing identifier
> ORA-06512: at line 4
>
> DECLARE
> ps_filename VARCHAR2(50) := 'hms_tpo_eblek_rev2.csv';
> begin
> execute immediate 'create table hms_tpo_eblek_rev_ext4
> ( EBLEK_ID VARCHAR2(50 BYTE),
> EBLEK_KLP_ID NUMBER,
> EBLEK_BRAND VARCHAR2(7 BYTE),
> EBLEK_TANGGAL VARCHAR2(10 BYTE),
> EBLEK_GL_CLASS VARCHAR2(10 BYTE),
> EBLEK_MANDOR_ID VARCHAR2(30 BYTE),
> EBLEK_TERDAFTAR NUMBER,
> EBLEK_ABSEN NUMBER,
> EBLEK_PROD NUMBER,
> EBLEK_PROC_ID NUMBER,
> CREATED_BY VARCHAR2(20 BYTE),
> CREATED_DATE date,--VARCHAR2(20 BYTE),
> LAST_UPDATED_BY VARCHAR2(20 BYTE),
> LAST_UPDATED_DATE VARCHAR2(20 BYTE),
> EBLEK_PROD_PLAN NUMBER,
> EBLEK_KONFIRM VARCHAR2(1 BYTE)
> )
> ORGANIZATION EXTERNAL
> ( type oracle_loader
> default directory EXT_TABLES
> access parameters
> (
> records delimited by newline
> NOBADFILE NODISCARDFILE NOLOGFILE
> fields terminated by '',''
> missing field values are null
> ( EBLEK_ID,
> EBLEK_KLP_ID,
> EBLEK_BRAND,
> EBLEK_TANGGAL,
> EBLEK_GL_CLASS,
> EBLEK_MANDOR_ID,
> EBLEK_TERDAFTAR,
> EBLEK_ABSEN,
> EBLEK_PROD,
> EBLEK_PROC_ID,
> CREATED_BY,
> CREATED_DATE date ''dd/mm/yyyy hh24:mi:ss'',
> LAST_UPDATED_BY,
> LAST_UPDATED_DATE,
> EBLEK_PROD_PLAN,
> EBLEK_KONFIRM
> )
> )
> location (:1)
> )
> reject limit unlimited'
> USING ps_filename;
> end;
>
> Thanks a lot

Why are you doing this with NDS? It is a inappropriate use of the capability. If you want to build tables go to $ORACLE_HOME/rdbms/admin and look at sql.bsq.

There is a reason Oracle does it this way.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org