Home » SQL & PL/SQL » SQL & PL/SQL » Reg KUP-04063 error while using External tables
Reg KUP-04063 error while using External tables [message #16500] Wed, 01 December 2004 01:25 Go to next message
Gurusubramanyam
Messages: 79
Registered: July 2001
Member
Hi,

I am getting the error KUP-04063 when I am using the 9i External Tables.Let me explain the steps exactly what I did.

01) I had created the directory c:study in OS and kept the emp.dat file which holds the data in csv format.The example date is given below. 

7369,SMITH,CLERK,7902,17-DEC-80,800,,20

7499,ALLEN,SALESMAN,7698,<st1:date Month="2" Day="20" Year="1981">20-FEB-81</st1:date>,1600,300,30

7521,WARD,SALESMAN,7698,<st1:date Month="2" Day="22" Year="1981">22-FEB-81</st1:date>,1250,500,30

02) The directory is created in the database by using the following syntax

 create or replace directory data_dir as 'c:study';

03) The External table is created as follows

ACCTMED> ed
Wrote file afiedt.buf


  1  create table external_table
  2   (EMPNO NUMBER(4) ,
  3    ENAME VARCHAR2(10),
  4    JOB VARCHAR2(9),
  5    MGR NUMBER(4),
  6    HIREDATE DATE,
  7    SAL NUMBER(7, 2),
  8    COMM NUMBER(7, 2),
  9    DEPTNO NUMBER(2)
 10   )
 11   organization external
 12   (type oracle_loader
 13   default directory data_dir
 14   access parameters
 15   (fields terminated by ',')
 16   location ('emp.dat')
 17*  )
 18  /


Table created.

04) The next step if i issue the followin command I am receiving the error.

ACCTMED> select * from external_table
  2  /
select * from external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXTERNAL_TABLE_10172.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


What is the reason for this? Advanced thanks for your timely solutions.

Thanks & Regards,

B.Prasad  

 
Re: Reg KUP-04063 error while using External tables [message #16519 is a reply to message #16500] Wed, 01 December 2004 09:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
External tables have a lot of pecuiliarities. Whether you specify one or not, it will automatically try to create a log file and sometimes it will try to create that log file in a directory other than what you would expect. It appears that it is attempting to automatically generate a log file named EXTERNAL_TABLE_10172.log in a directory that you do not have write access to. It may be that, although you have read access to the DATA_DIR directory, that you lack the necessary write access. Or, it may be that it is trying to write to another directory. I have found that this can happen even when the default directory is specified. A solution is to specify the directory for the log file in your table creation, making sure that it is a directory that you have write acces to and making sure that you enter it in upper case. You may also find that you have to do this with the bad file as well. Another pecuiliarity is that, if you enter these two lines, you will also need to enter the line "RECORDS DELIMITED BY NEWLINE" above. Please see the suggested code in the example below.

scott@ORA92> CREATE OR REPLACE DIRECTORY data_dir AS 'c:study'
  2  /

Directory created.

scott@ORA92> CREATE TABLE external_table
  2    (empno	 NUMBER(4),
  3  	ename	 VARCHAR2(10),
  4  	job	 VARCHAR2(9),
  5  	mgr	 NUMBER(4),
  6  	hiredate DATE,
  7  	sal	 NUMBER(7, 2),
  8  	comm	 NUMBER(7, 2),
  9  	deptno	 NUMBER(2))
 10  ORGANIZATION EXTERNAL
 11    (TYPE ORACLE_LOADER
 12  	DEFAULT DIRECTORY data_dir
 13  	ACCESS PARAMETERS
 14  	  (RECORDS DELIMITED BY NEWLINE
 15  	   BADFILE 'DATA_DIR':'emp.bad'
 16  	   LOGFILE 'DATA_DIR':'emp.log'
 17  	   FIELDS TERMINATED BY ',')
 18  	LOCATION ('emp.dat'))
 19  /

Table created.

scott@ORA92> SELECT * FROM external_table
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

scott@ORA92> 
Re: Reg KUP-04063 error while using External tables [message #16526 is a reply to message #16519] Wed, 01 December 2004 11:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
You can also do a test run with NOBADFILE and NOLOGFILE, to determine if that is the problem, as demonstrated below.

scott@ORA92> CREATE OR REPLACE DIRECTORY data_dir AS 'c:study'
  2  /

Directory created.

scott@ORA92> CREATE TABLE external_table
  2    (empno	 NUMBER(4),
  3  	ename	 VARCHAR2(10),
  4  	job	 VARCHAR2(9),
  5  	mgr	 NUMBER(4),
  6  	hiredate DATE,
  7  	sal	 NUMBER(7, 2),
  8  	comm	 NUMBER(7, 2),
  9  	deptno	 NUMBER(2))
 10  ORGANIZATION EXTERNAL
 11    (TYPE ORACLE_LOADER
 12  	DEFAULT DIRECTORY data_dir
 13  	ACCESS PARAMETERS
 14  	  (RECORDS DELIMITED BY NEWLINE
 15  	   NOBADFILE
 16  	   NOLOGFILE
 17  	   FIELDS TERMINATED BY ',')
 18  	LOCATION ('emp.dat'))
 19  /

Table created.

scott@ORA92> SELECT * FROM external_table
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

scott@ORA92>
Re: Reg KUP-04063 error while using External tables [message #16575 is a reply to message #16519] Fri, 03 December 2004 02:41 Go to previous messageGo to next message
Gurusubramanyam
Messages: 79
Registered: July 2001
Member
Hi,

Thanks for the guidelines. I had used all the parametrs as you have specified but still I am receiving the following error.

ACCTMED> SELECT * FROM external_table;
SELECT * FROM external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file emp.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I am not doing this activity in the server. I am doing this external table creation from one of the client pc thru sql*plus. Is it referencing the server directory?

Please clarify.

Thanks & Regards,
B.Prasad
Re: Reg KUP-04063 error while using External tables [message #16584 is a reply to message #16575] Fri, 03 December 2004 09:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Yes, the directory and file must be on the server.
Re: Reg KUP-04063 error while using External tables [message #16591 is a reply to message #16584] Fri, 03 December 2004 19:31 Go to previous messageGo to next message
Gurusubramanyam
Messages: 79
Registered: July 2001
Member
Hi,

I don't have the access to work on server. In this scenario , how can I create and use the external table?
Is there any alternative? Even I am not able to use the SQL*Loader because of the same constraint.

Please helpme out.

Thanks & Regards,
B.Prasad
Re: Reg KUP-04063 error while using External tables [message #16602 is a reply to message #16591] Sat, 04 December 2004 10:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
I don't know of any alternative. You will have to get somebody who has sufficient privileges to copy the file onto the server for you and grant you access to the file. Then you can create your external table from your client terminal. You don't have to be working on the server terminal. You just need privileges to access it from your client machine. For example, in a Windows environment, you can copy files from your client terminal to a mapped network drive that is accessible to the server.
Re: Reg KUP-04063 error while using External tables [message #327371 is a reply to message #16602] Mon, 16 June 2008 04:32 Go to previous message
DanDan
Messages: 1
Registered: June 2008
Location: Australia
Junior Member
Dear all,

I have been experiencing the same problem as you have been discussing in the posts above.

My scenario is as follows:

There is a unix box that has the Oracle database. On that box there is a folder /stagingServer/data/staging.

I create this folder to be the folder that stores the data to be imported.

CREATE OR REPLACE DIRECTORY ext AS '/stagingServer/data/staging';

GRANT READ ON DIRECTORY ext TO rss;
GRANT WRITE ON DIRECTORY ext TO rss;


Then i create the external table:

CREATE TABLE QSCAT_PARAMETERS_LOAD_EXTERNAL
(
ID VARCHAR2(50),
ORBIT VARCHAR2(50),
TIME_MIN NUMBER(18,0),
WINDS_MS NUMBER(18,2),
WIND_DIR_D NUMBER(18,2),
RAIN NUMBER(18,2),
RAD_RAIN NUMBER(18,2)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS
(
records delimited by newline
fields terminated by ','
reject rows with all null fields
(
ID,
ORBIT,
TIME_MIN,
WINDS_MS,
WIND_DIR_D,
RAIN,
RAD_RAIN
)
)
LOCATION ('qscat.csv')
)
PARALLEL
REJECT LIMIT 0;

then when i try to do select * from table i get:

select * from QSCAT_PARAMETERS_LOAD_EXTERNAL
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file QSCAT_PARAMETERS_LOAD_EXTERNAL_28974.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

Any help would be much much apreciated. No idea as to why this might be happening.

Thank you for your responses up front.
Previous Topic: update column and text search (merged)
Next Topic: Reg delete statements
Goto Forum:
  


Current Time: Mon Dec 05 20:50:44 CST 2016

Total time taken to generate the page: 0.06655 seconds