Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29913 ORA-29400 KUP-00554 (Oracle 12c)
ORA-29913 ORA-29400 KUP-00554 [message #677229] Tue, 03 September 2019 00:20 Go to next message
kavitha.raj23
Messages: 2
Registered: June 2007
Location: Chennai
Junior Member
Please help below.. Table is created, but when we use select * from table, comes with below error.

CREATE TABLE Ops_users_ext (
  line  number,
  Name1 VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ACV_DATA_PATH
  ACCESS PARAMETERS
  (
    FIELDS TERMINATED BY ','
    RECORDS DELIMITED BY NEWLINE
    reject limit UNLIMITED
    MISSING FIELD VALUES ARE NULL
    discardfile ACV_LOG_PATH:'read_trace_dis.txt'
    badfile     ACV_LOG_PATH:'read_trace_bad.txt'
    logfile     ACV_LOG_PATH:'read_trace_log.txt'
    (
    line  ,
    Name1 
    )
    )
  LOCATION ('Bdat.csv')
)
select * from Ops_users_ext

Error

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 "records": expecting one of: "all, column, date_format, enclosed, (, ltrim, lrtrim, ldrtrim, missing, notrim, nullif, optionally, rtrim, reject"
KUP-01007: at line 2 column 5
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.

With Regards
Kavitha M



[Edit MC: add code tags]

[Updated on: Tue, 03 September 2019 01:01] by Moderator

Report message to a moderator

Re: ORA-29913 ORA-29400 KUP-00554 [message #677230 is a reply to message #677229] Tue, 03 September 2019 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Some lines of your code are in the wrong place:
SQL> CREATE TABLE Ops_users_ext (
  2    line  number,
  3    Name1 VARCHAR2(10)
  4  )
  5  ORGANIZATION EXTERNAL
  6  (
  7    TYPE ORACLE_LOADER
  8    DEFAULT DIRECTORY MY_DIR
  9    ACCESS PARAMETERS
 10    (
 11      RECORDS DELIMITED BY NEWLINE
 12      discardfile MYDIR:'read_trace_dis.txt'
 13      badfile     MYDIR:'read_trace_bad.txt'
 14      logfile     MYDIR:'read_trace_log.txt'
 15      FIELDS TERMINATED BY ','
 16      MISSING FIELD VALUES ARE NULL
 17      (
 18      line  ,
 19      Name1
 20      )
 21      )
 22    LOCATION ('Bdat.csv')
 23  )
 24  reject limit UNLIMITED
 25  /

Table created.

SQL> select * from Ops_users_ext;
select * from Ops_users_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file Bdat.csv in MY_DIR not found
Re: ORA-29913 ORA-29400 KUP-00554 [message #677233 is a reply to message #677229] Tue, 03 September 2019 08:03 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
kavitha.raj23 wrote on Tue, 03 September 2019 00:20
Please help below.. Table is created, but when we use select * from table, comes with below error.

CREATE TABLE Ops_users_ext (
  line  number,
  Name1 VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ACV_DATA_PATH
  ACCESS PARAMETERS
  (
    FIELDS TERMINATED BY ','
    RECORDS DELIMITED BY NEWLINE
    reject limit UNLIMITED
    MISSING FIELD VALUES ARE NULL
    discardfile ACV_LOG_PATH:'read_trace_dis.txt'
    badfile     ACV_LOG_PATH:'read_trace_bad.txt'
    logfile     ACV_LOG_PATH:'read_trace_log.txt'
    (
    line  ,
    Name1 
    )
    )
  LOCATION ('Bdat.csv')
)
select * from Ops_users_ext

Error

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 "records": expecting one of: "all, column, date_format, enclosed, (, ltrim, lrtrim, ldrtrim, missing, notrim, nullif, optionally, rtrim, reject"
KUP-01007: at line 2 column 5
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.

With Regards
Kavitha M



[Edit MC: add code tags]
The last line of output said "*Action: Examine the error messages take appropriate action."
So, when we follow the clear action plan and examine the error messages, some may seem cryptic, but this one is pretty clear:

KUP-01005: syntax error: found "records": expecting one of:

So, if the syntax parser found the key word "records" when it was expecting something else, then obviously some clause prior to the occurrence of the word "records" was not complete. Review your syntax of everything preceding the only occurrence of the word "records". Be sure you do this review with the reference manual in front of you. Also be sure to check for mis-matched things that come in pairs, like quote signs or parentheses.
Re: ORA-29913 ORA-29400 KUP-00554 [message #677237 is a reply to message #677229] Wed, 04 September 2019 04:47 Go to previous messageGo to next message
oracledev
Messages: 2
Registered: August 2019
Junior Member
Ensure those privileges provided or not READ,WRITE,EXECUTE(777) privileges to directory to user.

If your session is idle at that time this error is occur so start new session then try.


Thanks

Re: ORA-29913 ORA-29400 KUP-00554 [message #677238 is a reply to message #677237] Wed, 04 September 2019 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You missed to say that he has to be sure that the power is on.

[Updated on: Wed, 04 September 2019 05:25]

Report message to a moderator

Re: ORA-29913 ORA-29400 KUP-00554 [message #677240 is a reply to message #677237] Wed, 04 September 2019 06:39 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
oracledev wrote on Wed, 04 September 2019 04:47
Ensure those privileges provided or not READ,WRITE,EXECUTE(777) privileges to directory to user.

If your session is idle at that time this error is occur so start new session then try.


Thanks

How does any of that have any bearing on the problem when the error very clearly says there is a syntax error in the CREATE statement?
icon14.gif  Re: ORA-29913 ORA-29400 KUP-00554 [message #677241 is a reply to message #677238] Wed, 04 September 2019 06:40 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Wed, 04 September 2019 05:25

You missed to say that he has to be sure that the power is on.

+1
Previous Topic: How to remove ordinal suffixes after the numbers
Next Topic: IIF and Oracle
Goto Forum:
  


Current Time: Thu Mar 28 17:17:10 CDT 2024