Ora-29400 Data Cartridge Error Kup-00554
Contents |
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank kup-01005 syntax error found identifier message. Please type your message and try again. More discussions in General Database kup-01008: the bad identifier was: varchar2 Discussions All PlacesDatabaseGeneral Database Discussions This discussion is archived 1 2 Previous Next 16 Replies Latest reply on Mar 14, 2013 ora-29913 error in executing odciexttableopen callout ora-29400 data cartridge error kup-04040 10:16 AM by Nicolas.Gasparotto ERROR WITH EXTERNAL TABLE 458726 Jan 20, 2006 7:45 PM Hi, I am trying to query an external table that points it file on my WindowsXP system. The Oracle server
Kup-01005 Syntax Error Found Single-quoted-string
is running on a same machine. ----------------------------------------- create table external_arq( text1 varchar2(1), text2 varchar2(20) ) organization external (type oracle_loader default directory external_directory access parameters ( records delimited by newline fields missing field value are null (text1 position(1,1), text2 position(2,20) ) ) location ('c:\temp\TEST.TXT') ) ------------------------------------------------------------ When I query the external table it returns the following error. SQL> select * from EXT_TABLE; select * from EXT_TABLE * ERROR at line kup-01008: the bad identifier was: number 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountred while parsing access parameters KUP-01005: sintaxe error: found "identifier": expecting one of :"values" KUP-01008: the bad identifier was: value KUP-01007: at line 3 column 15 ORA-06512: at "SYS.ORACLE_LOADER", line 14 ORA-06512: at line 1 Vendor code 29913 Anybody have any idea ? Anderson 42573Views Tags: none (add) 11gContent tagged with 11g, externalContent tagged with external, inContent tagged with in, preprocessorContent tagged with preprocessor, r2Content tagged with r2, tableContent tagged with table This content has been marked as final. Show 16 replies 1. Re: ERROR WITH EXTERNAL TABLE 484240 Jan 20, 2006 8:30 PM (in response to 458726) When using positional notation for the fields, I always have used : to separate the start:stop position where you are using a ,. Regards, Daniel Fink Like Show 0 Likes(0) Actions 2. Re: ERROR WITH EXTERNAL TABLE 230263 Jan 20, 2006 9:41 PM (in response to 458726) Try changing the line missing field value are null to missing field values are null Like Show 0 Likes(0) Actions 3. Re: ERROR WITH EXTERNAL TABLE 458726 Jan 23, 2006 11:21 AM (in response to 230263) Using ':' to separete the fields and not nulls i
error [message #484805] Thu, 02 December 2010 20:02 timvand Messages: 7Registered: December 2005 Location: NorCal Junior Member I kup-01005 external tables receive the following error message ERROR at line 1: ORA-29913:
Kup-01005: Syntax Error: Found "missing": Expecting One Of: "column, ("
error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access
Kup 01005 Syntax Error Found Minussign Expecting One Of
parameters KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, https://community.oracle.com/thread/358054 ), unsigned, varrawc, varchar, varraw, varcharc, zoned" KUP-01008: the bad identifier was: VARCHAR2 KUP-01007: at line 6 column 21 ORA-06512: at "SYS.ORACLE_LOADER", line 19 when I select count(*) on the external table created below. SQL> CREATE TABLE cac_500_load 2 ( 3 EMAILADDRESS VARCHAR2(80), 4 FIRSTNAME VARCHAR2(60), 5 LASTNAME VARCHAR2(60), 6 STREETADDRESS http://www.orafaq.com/forum/t/163832/ VARCHAR2(100), 7 CITY VARCHAR2(50), 8 STATE VARCHAR2(30), 9 ZIP NUMBER, 10 PHONE NUMBER, 11 UPDATEDON DATE, 12 IPADDRESS VARCHAR2(40), 13 SITE VARCHAR2(100), 14 GENDER VARCHAR2(30), 15 DOB VARCHAR2(40) 16 ) 17 ORGANIZATION EXTERNAL 18 ( 19 TYPE ORACLE_LOADER 20 DEFAULT DIRECTORY xtrnl_tbls_dir 21 ACCESS PARAMETERS 22 ( 23 RECORDS DELIMITED BY NEWLINE 24 FIELDS TERMINATED BY ',' 25 OPTIONALLY ENCLOSED BY '"' 26 MISSING FIELD VALUES ARE NULL 27 ( 28 EMAILADDRESS VARCHAR2(80), 29 FIRSTNAME VARCHAR2(60), 30 LASTNAME VARCHAR2(60), 31 STREETADDRESS VARCHAR2(100), 32 CITY VARCHAR2(50), 33 STATE VARCHAR2(30), 34 ZIP NUMBER, 35 PHONE NUMBER, 36 UPDATEDON DATE MASK "MM/DD/YYYY", 37 IPADDRESS VARCHAR2(40), 38 SITE VARCHAR2(100), 39 GENDER VARCHAR2(30), 40 DOB VARCHAR2(40), 41 ) 42 ) 43 LOCATION ('cac-500.csv') 44 ) 45 ; Table created. SQL> Here is the db version info: SQL> select * from v$instance; INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------- HOST_NAME ---------------------------------------------------------------- VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT ----------------- ---------
cartridge error Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.The pair ORA-29913/ORA-29400 is a sort of catch-all exception embedding http://oraclequirks.blogspot.com/2008/07/ora-29400-data-cartridge-error.html KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.The type of errors encountered spans from syntax errors to missing files or privileges.For instance, yesterday i got this one when i forgot to specify a keyword in the external table definition.CREATE TABLE "IMP_BAD_BOXES"( "TOTE_ID" NUMBER(8,0),"DEPT" VARCHAR2(2 BYTE),"CREATED" DATE)ORGANIZATION EXTERNAL( TYPE syntax error ORACLE_LOADERDEFAULT DIRECTORY "IMPORT_DIR"ACCESS PARAMETERS(LOGFILE 'BOXES.log'FIELDSMISSING FIELD VALUES ARE NULLREJECT ROWS WITH ALL NULL FIELDS (CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",TOTE_ID POSITION(15:22) CHAR,DEPT POSITION(23:24) CHAR))LOCATION( 'BOXES.dat'));select * from IMP_BAD_BOXES;ORA-29913: error in executing ODCIEXTTABLEOPEN calloutORA-29400: data cartridge errorKUP-00554: error encountered while parsing access parametersKUP-01005: syntax error: found "logfile": expecting one of: "column, ..."KUP-01007: at line 1 column 1I marked in syntax error found red color the position in the statement that caused the run-time error.Note indeed that when you create the table, no issues are reported, you won't know if it works until you go live.So, in the end, this verbose error message was to report that i forgot to specify the keyword RECORDS before LOGFILE.If you look at the syntax diagram of the ACCESS PARAMETERS clause (ver. 10R1), you'll notice that there are four distinct sub-clauses. LOGFILE belongs to the record format sub-clause. This means that you cannot specify any keyword in this sub-clause if you haven't specified the RECORDS keyword first.Note also that from a syntax standpoint it is perfectly legitimate to write the RECORDS keyword alone, but if you do not add DELIMITED BY NEWLINE ( or some other specification) then the record terminator will remain undefined, resulting in the following run-time error:CREATE TABLE "IMP_BAD_BOXES"( "TOTE_ID" NUMBER(8,0),"DEPT" VARCHAR2(2 BYTE),"CREATED" DATE)ORGANIZATION EXTERNAL( TYPE ORACLE_LOADERDEFAULT DIRECTORY "IMPORT_DIR"ACCESS PARAMETERS(RECORDSLOGFILE 'BOXES.log'FIELDSMISSING FIELD VALUES ARE NULLREJECT ROWS WITH ALL NULL FIELDS (CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",