Error In Executing Odciexttableopen Callout Ora-29400 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 ora-29913 error in executing odciexttableopen callout ora-29400 data cartridge error policies of this site About Us Learn more about Stack Overflow the ora-29913 error in executing odciexttableopen callout ora-29400 data cartridge error kup-04040 company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags ora 20011 approximate ndv failed ora 29913 error in executing odciexttableopen callout Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only ora-29400 data cartridge error kup-04040 takes a minute: Sign up ORA-29913: error in executing ODCIEXTTABLEOPEN callout when inserting csv into oracle up vote 1 down vote favorite I'm trying to execute this code in PL/SQL: create or replace directory ext_tab_dir as 'C:/mydir'; GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO PUBLIC; DROP TABLE emp_load; CREATE TABLE emp_load (v1 VARCHAR2(4000), v2 VARCHAR2(4000) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT
Kup-00554: Error Encountered While Parsing Access Parameters
DIRECTORY ext_tab_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE ext_tab_dir:'bad.bad' LOGFILE ext_tab_dir:'log.log' FIELDS TERMINATED BY ',' ) LOCATION ('testfile.csv') ); -- INSERT INTO tablename(v1,v2) SELECT * From emp_load and then getting next errors: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file C:/mydir/log.log I do get that it has to do something with permissions, but I'm the one who created that directory, so how do I grant priveleges to myself if it is set like this by default? Is there any way to perform that sort of operation from PL/SQL? oracle csv plsql share|improve this question edited Dec 1 '14 at 14:47 slavoo 2,76192029 asked Dec 1 '14 at 14:45 Nikita Buriak 25118 1 What's the content of the LOGFILE? –Marco Baldelli Dec 1 '14 at 15:38 1 It's not in there as if it doesn't create itself, and If I do that manually it is empty. –Nikita Buriak Dec 2 '14 at 9:47 1 Try writing the path with backslahes: C:\mydir\log.log –Marco Baldelli Dec 2 '14 at 13:51 add a
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the
Ora-29913 Error In Executing Odciexttableopen Callout Impdp
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions 29913. 00000 - "error in executing %s callout" Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million ora-30653: reject limit reached programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle 11g External Table error up vote 0 down vote favorite Im trying to run a simple external table program http://stackoverflow.com/questions/27230879/ora-29913-error-in-executing-odciexttableopen-callout-when-inserting-csv-into-o using oracle 11g on Linux VM. The problem is that i cant query any data from .txt files. Here's my code: CONN / as sysdba; CREATE OR REPLACE DIRECTORY DIR1 AS 'home/oracle/TEMP/X/'; GRANT READ, WRITE ON DIRECTORY DIR1 TO user; CONN user/password; CREATE TABLE gerada ( field1 INT, field2 Varchar2(20) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY DIR1 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ';' MISSING FIELD VALUES http://stackoverflow.com/questions/17744445/oracle-11g-external-table-error ARE NULL ) LOCATION ('registros.txt') ) REJECT LIMIT UNLIMITED; --Error starts here. SELECT * FROM gerada; DROP TABLE gerada; DROP DIRECTORY DIR1; Here's the error message: ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file home/oracle/TEMP/X/GERADA_3375.log And thats how registros.txt looks like: 1234;hello world; I've checked my permissions on DIR1 and I do have read/write permissions. Any ideas? database oracle table external share|improve this question asked Jul 19 '13 at 10:57 Diego 1014 do the owner of oracle-process has read/write permissions on DIR1 ? –schurik Jul 19 '13 at 11:02 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted ORA-29913 and ORA-29400 mean that you're unable to access to directory and/or file. Looking carefully at the CREATE DIRECTORY command it looks like the path you're using may be mis-formatted. Try putting a forward slash at the start of the path and removing the one at the end of the path when creating the directory - e.g. CREATE OR REPLACE DIRECTORY DIR1 AS '/home/oracle/TEMP/X';. Share and enjoy. share|improve this answer answered Jul 19 '13 at 11:03 Bob Jarvis 24.5k43766 Bob Jarvis, the code worked by using your suggestion AND removing the extension ".txt" from LOCATION. I just dont understand why!
December 2009 06:12 ramya29p Messages: 100Registered: November 2007 Location: Chennai Senior Member Hi, I tried to upload the http://www.orafaq.com/forum/t/153122/ Data through External Table. SQL> create table ext_emp(ename varchar2(20),age number) 2 organization external( 3 type oracle_loader 4 default directory dir1 5 access parameters 6 (fields terminated by http://blog.mclaughlinsoftware.com/2009/07/11/external-table-query-fix/ ',' 7 ) 8 location('emp.txt') 9 ) 10 / Table created. SQL> desc ext_emp; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ENAME VARCHAR2(20) AGE NUMBER table got error in created but while selecting from the table i am getting the following error. can anyone please help me. SQL> select * from ext_emp; select * from ext_emp * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file EXT_EMP_2604_4584.log Report message to a moderator error in executing Re: Oracle External Table [message #435110 is a reply to message #435109] Mon, 14 December 2009 06:24 ramoradba Messages: 2454Registered: January 2009 Location: AndhraPradesh,Hyderabad,I... Senior Member http://www.adp-gmbh.ch/ora/err/29913.html http://www.dba-oracle.com/t_ora_29913_external_table_error.htm http://www.oracle-base.com/articles/9i/ExternalTables9i.php especially on this http://www.orafaq.com/node/848 The ORA-29913 error can also happen in external tables when you don't grant read and write permissions to the directory. And always post your Oracle version with 4 digits. sriram [Updated on: Mon, 14 December 2009 06:33]Report message to a moderator Re: Oracle External Table [message #435112 is a reply to message #435110] Mon, 14 December 2009 06:35 ramya29p Messages: 100Registered: November 2007 Location: Chennai Senior Member Hi , I created the Directory as CREATE OR REPLACE DIRECTORY dir1 as 'D:\BPEL' then why i need 2 give grant permission? Report message to a moderator Re: Oracle External Table [message #435113 is a reply to message #435112] Mon, 14 December 2009 06:42 Michel Cadot Messages: 63861Registered: March 2007 Locati
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 whe