Ora-29913: Error In Executing Odciexttableopen Callout

I am creating external table using hr schema but i get errors 'ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encounter

Solution 1:

You have several issues here. The immediate one causing your problem is that you have the clauses in the wrong order, but you also have MISSING FIELDS instead of MISSING FIELD:

...
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY',' 
  MISSING FIELD VALUESARENULL
  REJECT ROWSWITHALLNULL FIELDS
  (
...

Then your field list contents have invalid data types for that part of the statement; you can just omit that entirely in this case as those match the table column definition.

If no field list is specified, then the fields in the data file are assumed to be in the same order as the fields in the external table.

So you can simplify it to:

createtable strecords (
st_id number(4),
st_name varchar(10),
schl_name varchar(5),
st_city varchar(15),
st_year number(4)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY ex_tab
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY',' 
  MISSING FIELD VALUESARENULL
  REJECT ROWSWITHALLNULL FIELDS
 )
 LOCATION ('strecords.txt')
);

Solution 2:

Some operations needed against an Oracle Bug called

Select From External Table Returns Errors ORA-29913 ORA-29400 KUP-554 KUP-1005 (Doc ID 302672.1)

When creating an external table, the access parameters should be specified in the following order:

• Comments
• Record Format Info
• Field definitions

Specify Comments, Record Format Info and Field definitions in the correct order. Even inside the Record format Info, 'Records delimited by ...' clause should come before any other clause.

For more information, refer to the access_parameters clause.