CODEWITHSUNDEEP
oracle-databaseexternal-tables
Aldrin D'souza
Aldrin D'souza

Reputation: 3

error while trying to select from external table

I am getting the below error while trying to select * from ext_poc

ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned" KUP-01008: the bad identifier was: varchar2 KUP-01007: at line 4 column 10 29913. 00000 - "error in executing %s callout" *Cause: The execution of the specified callout caused an error. *Action: Examine the error messages take appropriate action.

Below is the ddl for the table:

CREATE TABLE "JDASTG"."EXT_POC" 
(   "ID" varchar2(100), 
"NAME"   varchar2(100), 
"DOB"    varchar2(100)
) 

ORGANIZATION EXTERNAL 
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY "SCPO_EXT_DATA"
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE    
  FIELDS TERMINATED BY ','    
  MISSING FIELD VALUES ARE NULL               
  (id      varchar2(100),    
  name     varchar2(100),     
  dob      varchar2(100)                
  )        
)
  LOCATION
   ( 'xyz_aldrin.csv'
   )
);

PS: This error however doesn't occur if the varchar2(100) is changed to char(100):

MISSING FIELD VALUES ARE NULL               
(id      char(100),    
name    char(100),     
dob     char(100)                
)

Upvotes: 0

Views: 2668

Answers (1)

Srini V
Srini V

Reputation: 11375

It is important to distinguish between oracle internal data types specified on the table, and the external data types specified in the access parameters for the external table (control file for sqlldr). Read the manual again with that in mind. It should become clear. CHAR is allowed in SQL loader whereas VARCHAR2 is not

Upvotes: 4

Related Questions