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  |
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 #327371 is a reply to message #16602] |
Mon, 16 June 2008 04:32 |
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.
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 06:26:51 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
