Ora-29913 Error In Executing Odciexttableopen Callout Kup-04040
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog ora-30653: reject limit reached
Oracle External kup-01005 Table error and ORA-29913 Oracle Database Tips by Burleson Consulting Question: I'm getting the ORA-29913 error with the KUP-00552 ora-30657: operation not supported on external organized table error when attempting to query from an external table: ERROR en l?ea 1: ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEOPEN ORA-29400: error de cartucho de datos KUP-00552: internal XAD package failed oracle external table example to load ORA-06512: en "SYS.ORACLE_LOADER", l?ea 19 Answer: The root cause for the error relates to the message: "KUP-00552: internal XAD package failed to load". There are several causes for the ORA-29913 error, including bug 5172459 (MOSC Note:373168.1), problems with file permissions on the external directory: ORA-29913: error in executing string callout Cause: The execution of the specified callout caused an error. Action: ExamineKup-00554: Error Encountered While Parsing Access Parameters
the error messages take appropriate action. KUP-00552 internal XAD package failed to load Cause: An error was encountered while attempting to initialize the XAD package. Action: This message should be accompanied by other error messages indicating the cause of the error. The most common causes of the ORA-29913 error are that Oracle fails to open OS file that is required. These files might contain output, input, log, discard or bad files. The main reason behind the ORA-29913 error is that an Oracle executable has denied the permission or the file granted the permission or directory itself does not exist for accessing the directory properly. You can also finds in the ORA-29913 error when Oracle failed to load the table data or write the log files, and the ORA-29913 may indicate problems with table directory or the .log files in the directory. Make sure whether the directories have granted the required rights or permission for Oracle to carry out read and write to the directories. Execute the special check on ownership and permissions for network directories. See BUG 5172459 (MOSC Note:373168.1) The problem is that the message file for external tables, not the English version
here for a quick overview of the site Help Center Detailed answers to any questions you might have
Ora-29400 Data Cartridge Error External Table
Meta Discuss the workings and policies of this site About Us error in executing %s callout Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with kup-04001: error opening file us Stack Overflow 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, http://www.dba-oracle.com/t_ora_29913_external_table_error.htm just like you, helping each other. Join them; it only takes a minute: Sign up error in executing ODCIEXTTABLEOPEN callout : File not found up vote 0 down vote favorite I am getting a file not found error though I have placed the file in the correct location. I created a directory CREATE or replace DIRECTORY http://stackoverflow.com/questions/24569243/error-in-executing-odciexttableopen-callout-file-not-found ext_directory AS '/usr/apps/datafiles'; I have granted read write permission to the ext_directory. GRANT READ WRITE ON DIRECTORY ext_directory TO MY_DEV; Then I created an external table: CREATE TABLE CUSTOMER_DTL_EXT(Customer_Number CHAR(10), Forename VARCHAR2(40), Surname VARCHAR2(40), Date_Birth VARCHAR2(8) organization external ( type oracle_loader default directory ext_directory access parameters ( records delimited by newline NOBADFILE NODISCARDFILE NOLOGFILE FIELDS TERMINATED BY ‘|’ ) location (‘SAMPLE.TXT’) ); I have also used chmod 777 SAMPLE.TXT But, when I run the query: Select * from CUSTOMER_DTL_EXT; I get the following error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file SAMPLE.TXT in ext_directory not found 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. Can anyone please tell me where should the file SAMPLE.TXT be placed. I have placed it under '/usr/apps/datafiles' itself. I have checked this link but this did not solve my problem: sqlplus error on select from external table: ORA-
Error KUP-04040 while using External Tables [message #129334] Sun, 24 July 2005 04:52 lawrence_saldanha Messages: http://www.orafaq.com/forum/t/49294/ 1Registered: July 2005 Location: Dubai Junior Member Hi All, I http://blog.mclaughlinsoftware.com/plsql-programming/how-to-guarantee-an-external-file-before-querying/ am using External Tables i have written a PL/SQL to Create External Tables . Some time if the file is not available on the Specified Directory.system will throw the following Error: ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout error in ORA-29400: data cartridge error KUP-04040: file kkk.DAT in FERSHO_EXPORT_GL not found ORA-06512: at "SYS.ORACLE_LOADER", line 19 ORA-06512: at line 1 ORA-06512: at "FERSHO.CREATE_TEMP_TABLE", line 115 ORA-06512: at line 1 I Want to know how to trap this error so that we can show proper message . Thanks & Regards, Lawrence Report message error in executing to a moderator Re: How to trap Error KUP-04040 while using External Tables [message #129368 is a reply to message #129334] Sun, 24 July 2005 20:37 Barbara Boehmer Messages: 8609Registered: November 2002 Location: California, USA Senior Member -- successful execution: scott@ORA92> CREATE OR REPLACE DIRECTORY fresho_export_gl AS 'c:\oracle' 2 / Directory created. scott@ORA92> CREATE TABLE temp_table (col1 NUMBER) 2 / Table created. scott@ORA92> CREATE OR REPLACE PROCEDURE create_temp_table 2 AS 3 v_ref SYS_REFCURSOR; 4 v_val NUMBER; 5 BEGIN 6 EXECUTE IMMEDIATE 'DROP TABLE temp_table'; 7 EXECUTE IMMEDIATE 8 'CREATE TABLE temp_table 9 (COL1 NUMBER) 10 ORGANIZATION external 11 (TYPE oracle_loader 12 DEFAULT DIRECTORY fresho_export_gl 13 ACCESS PARAMETERS 14 (FIELDS TERMINATED BY "," 15 (COL1)) 16 location (''kkk.dat''))'; 17 OPEN v_ref FOR SELECT * FROM temp_table; 18 LOOP 19 FETCH v_ref INTO v_val; 20 EXIT WHEN v_ref%NOTFOUND; 21 DBMS_OUTPUT.PUT_LINE ('value: ' || v_val); 22 END LOOP; 23 END create_temp_table; 24 /
for that. Since the lack of a file raises an error stack, I concluded that they want to verify whether the file exists before querying it. That way, they avoid the annoying ORA-29913, ORA-29400, and KUP-04040 error stack, as shown: SQL> SELECT * FROM CHARACTER; SELECT * FROM CHARACTER * ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04040: file CHARACTER.csv IN DOWNLOAD NOT found There are a number of ways to solve this type of problem but they all employ Java to read the external file system. Rather than write a custom Java module to look for a specific file, I’ll reuse the ListVirtualDirectory library from the Reading an external directory from SQL or PL/SQL blog page. I’m also using the GET_DIRECTORY_PATH function from this blog page. These are the steps to perform this small case. You can find broader information on the related blog pages for some elements of these steps. 1. The first thing to do is create a physical directory on the server, than a virtual directory in the database that points to the physical directory. This command creates a virtual DOWNLOAD directory for uploads when run as the SYSTEM user: SQL> CREATE DIRECTORY download AS ‘C:\DATA\Files\Upload’; Using DOWNLOAD for the virtual directory and Upload for the physical directory should help avoid confusion between virtual and physical directories. After you create a directory, you need to grant privileges to the schema that will read or write to the virtual directory (Oracle also needs file system level privileges to the directory). You grant read privileges by: SQL> GRANT READ ON DIRECTORY download TO plsql; 2. After creating the directory and granting permissions, you create an external table by using the following type of construction statement: 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; The metadata or catalog information is stored in two views, the USER_EXTERNAL_TABLES and USER_EXTERNAL_LOCATIONS (they naturally have ALL_ or DBA_ views too). The virtual directory is stored in the USER_EXTERNAL_TABLES view, while the physical file name is stored in the USER_EXTERNAL_