OraFAQ Forum: SQL & PL/SQL » ORA-29913 ORA-29400 KUP-00554

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29913 ORA-29400 KUP-00554 (Oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-29913 ORA-29400 KUP-00554 [message #677229]

Tue, 03 September 2019 00:20

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

Michel Cadot
Messages: 68417
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

Report message to a moderator

Re: ORA-29913 ORA-29400 KUP-00554 [message #677233 is a reply to message #677229]

Tue, 03 September 2019 08:03

EdStevens
Messages: 1375
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.

Report message to a moderator

Re: ORA-29913 ORA-29400 KUP-00554 [message #677237 is a reply to message #677229]

Wed, 04 September 2019 04:47

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

Report message to a moderator

Re: ORA-29913 ORA-29400 KUP-00554 [message #677238 is a reply to message #677237]

Wed, 04 September 2019 05:25

Michel Cadot
Messages: 68417
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

EdStevens
Messages: 1375
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?

Report message to a moderator

Re: ORA-29913 ORA-29400 KUP-00554 [message #677241 is a reply to message #677238]

Wed, 04 September 2019 06:40

EdStevens
Messages: 1375
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.

Report message to a moderator

Previous Topic:	How to remove ordinal suffixes after the numbers
Next Topic:	IIF and Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 22 07:47:03 CDT 2023