Odciexttableopen Data Cartridge Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies ora-29400: data cartridge error of this site About Us Learn more about Stack Overflow the company
Ora-29913 Error In Executing Odciexttableopen Callout Ora-29400 Data Cartridge Error Kup-04040
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users ora-29913 error in executing odciexttableopen callout impdp Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a
Ora-29913 Error In Executing Odciexttablefetch Callout Ora-30653 Reject Limit Reached
minute: Sign up sqlplus error on select from external table: ORA-29913: error in executing ODCIEXTTABLEOPEN callout up vote 4 down vote favorite 1 I have setup a simple Oracle external table test that I (alongside a DBA and Unix admin) can't get to work. The following is based on Oracle's External Tables Concepts. The database we're using is 11g. This is the kup-00554: error encountered while parsing access parameters external table definition: drop table emp_load; CREATE TABLE emp_load (employee_number CHAR(5), employee_dob DATE, employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY defaultdir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy" ) ) LOCATION ('external_table_test.dat') ); This is the contents of "external_table_test.dat": 56november, 15, 1980 baker mary alice 09/01/2004 87december, 20, 1970 roper lisa marie 01/01/1999 I am able to run the script that creates "emp_load" with no issues. I can also describe the table fine. When I attempt "select * from emp_load", I get the following errors: SQL> select * from emp_load; select * from emp_load * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file /defaultdir/EMP_LOAD_29305.log EDIT 1 oracle has read/write permissions on the directory. EDIT 2 I was able to get passed this error by using the following external table definition: CREATE TABLE emp_load (employee_number CHAR(3), employee_last_name CHAR(20), employee_middle_name CHAR(15), employee_first_name CHAR(15) ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY defaultdir ACCESS PARAMETERS
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss
Ora-29913 Error In Executing Odciexttablefetch Callout During Import
the workings and policies of this site About Us Learn more about
29913. 00000 - "error In Executing %s Callout"
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow kup-01005 Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each http://stackoverflow.com/questions/9066191/sqlplus-error-on-select-from-external-table-ora-29913-error-in-executing-odcie other. Join them; it only takes a minute: Sign up Error in executing ODCIEXTTABLEOPEN callout up vote -1 down vote favorite i am getting the below error message while using the functionality of external table in oracle. 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 http://stackoverflow.com/questions/23488204/error-in-executing-odciexttableopen-callout "field": expecting one of: "badfile, byteordermark, characterset, column, data, delimited, discardfile, disable_directory_link_check, fields, fixed, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, preprocessor, readsize, string, skip, territory, variable" Actually, I have created a table by using below command CREATE TABLE SUMIT ( NAME VARCHAR2(20), AGE INTEGER) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY TEST_FILES ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELD TERMINATED BY ',') LOCATION ('feed.txt')); and table was created successfully. when i am trying to view the contents of the table then i am getting the error. Directory object TEST_FILES is also correct. I have checked path as well as file name feed.txt. Below is the structure of feed.txt sumit,123 I am using Linux environment. Please help me to resolve this issue. oracle share|improve this question edited May 6 '14 at 7:32 asked May 6 '14 at 7:14 sumit vedi 1242512 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote As shown in all the examples, and in the SQL*Loader documentation, and in the error message you quo
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 http://oraclequirks.blogspot.com/2008/07/ora-29400-data-cartridge-error.html a sort of catch-all exception embedding KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.The type of http://blog.mclaughlinsoftware.com/2009/07/11/external-table-query-fix/ 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 error in the external table definition.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(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 error in executing while parsing access parametersKUP-01005: syntax error: found "logfile": expecting one of: "column, ..."KUP-01007: at line 1 column 1I marked in 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
Java stored procedures as the best solution initially. That was overkill. This afternoon, while writing about them for the new PL/SQL Workboook, it became clear. The fix is really easy. If you know little to nothing about external tables, you can go read this earlier post. Likewise, if you don't know about objects and object collection, you can refer to this post. Having provided you with the context, here's an example that eliminates errors when querying an external table without an external file. Create an external file, like this character table. CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'character.bad' DISCARDFILE 'DOWNLOAD':'character.dis' LOGFILE 'DOWNLOAD':'character.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; Create a user-defined object type that mirrors your external table defintion, like this: CREATE OR REPLACE TYPE character_obj IS OBJECT ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)); / Create a user-defined collection of your object type, like CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj; / Create a function that returns the user-defined collection of your object type, like CREATE OR REPLACE FUNCTION character_source RETURN character_obj_table IS c NUMBER; collection CHARACTER_OBJ_TABLE := character_obj_table(); BEGIN FOR i IN (SELECT * FROM CHARACTER) LOOP collection.EXTEND; collection(c) := character_obj( i.character_id , i.first_name , i.last_name); c := c + 1; END LOOP; RETURN collection; EXCEPTION WHEN OTHERS THEN RETURN collection; END; / Query the function not the table, which returns no rows found when the file doesn't physically exist, or the file contains no data. Lastly, the function returns the data when it is there. SELECT * FROM TABLE(character_source); Hope this helps those using external tables to avoid the typical error stack: SELECT * FROM CHARACTER * ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: