Home » SQL & PL/SQL » SQL & PL/SQL » KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log
KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185730] Thu, 03 August 2006 05:58 Go to next message
kunalrai
Messages: 4
Registered: August 2006
Location: noida
Junior 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:\oracle\kunalexternal\mySID\data in OS and kept the CAS_INT_AUTO.txt file which holds the data in csv format.The example date is given below.

H20060708
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
T00001H20060726
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
T00002H20060728
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
T00002HCAS_INT_AUTO 20062629 000595
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
T00002HCAS_INT_AUTO 20063430 000596
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
10000000000000000000000000000000000000000286800000000000000000000000000000000000000003232L0000000000000000000101000000000000000000323 2LLL00000000000000000000020000000000000000000062L00000000000000000000000000000000000000000000PL 0000000000000000004389
T00002

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

create or replace directory external_data_dir as 'C:\oracle\kunalexternal\mySID\data';

03) The External table is created as follows

ACCTMED> ed
Wrote file afiedt.buf

CREATE TABLE EXTERNAL_AUTO1_TABLE
(
CIA_REC_LENGTH NUMBER(5),
CIA_APP_ID_C NUMBER(14),
CIA_BRANCH_NO NUMBER(5),
CIA_FROM_ACCOUNT NUMBER(11),
CIA_FROM_APPLICATION_TYPE VARCHAR2(1) ,
CIA_TO_BRANCH NUMBER(5),
CIA_TO_ACCOUNT NUMBER(11),
CIA_TO_APPLICATION_TYPE VARCHAR2(1),
CIA_TRANSFER_AMMOUNT_CODE VARCHAR2(1),
CIA_SPECIAL_FREQUENCY VARCHAR2(1) ,
CIA_TRANSFER_DATE NUMBER(Cool,
CIA_END_DATE NUMBER(Cool,
CIA_END_TRANSFER_CODE VARCHAR2(1),
CIA_STATEMENT_NOTICE_DESC VARCHAR2(16),
CIA_PROVE_CODE NUMBER(10),
CIA_TO_TRANCODE NUMBER(5),
CIA_FROM_TRANCODE NUMBER(5),
CIA_MAPPEDVALUES VARCHAR2(20),
CIA_SECONDSUM VARCHAR2(20),
CIA_FIRSTSUM VARCHAR2(20)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY external_data_dir
ACCESS parameters (
records delimited BY newline
fields (
CIA_REC_LENGTH position(1:5) CHAR(5) ,
CIA_APP_ID_C position(6:14) CHAR(14) ,
CIA_BRANCH_NO position(22:5) CHAR(5),
CIA_FROM_ACCOUNT position(28:11) CHAR(11),
CIA_FROM_APPLICATION_TYPE position(40:1) CHAR(1) ,
CIA_TO_BRANCH position(42:5) CHAR(5),
CIA_TO_ACCOUNT position(48:11) CHAR(11),
CIA_TO_APPLICATION_TYPE position(60:1) CHAR(1),
CIA_TRANSFER_AMMOUNT_CODE position(62:1) CHAR(1) ,
CIA_SPECIAL_FREQUENCY position(64:1) CHAR(1),
CIA_TRANSFER_DATE position(66:8) CHAR(Cool,
CIA_END_DATE position(75:8) CHAR(Cool,
CIA_END_TRANSFER_CODE position(84:1) CHAR(1),
CIA_STATEMENT_NOTICE_DESC position(86:16) CHAR(16),
CIA_PROVE_CODE position(103:10) CHAR(10),
CIA_TO_TRANCODE position(114:5)CHAR(5) ,
CIA_FROM_TRANCODE position(120:5) CHAR(5),
CIA_MAPPEDVALUES position(126:20) CHAR(20),
CIA_SECONDSUM position(147:20) CHAR(20),
CIA_FIRSTSUM position(168:20) CHAR(20)
)
)
location (external_data_dir:'CAS_INT_AUTO.txt')
)
reject LIMIT UNLIMITED;

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_AUTO1_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,

KUNAL RAI
Re: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185734 is a reply to message #185730] Thu, 03 August 2006 06:18 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Did you create the directory on the database server itself? If so, have you verified the permissions on the directory.
Re: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185736 is a reply to message #185734] Thu, 03 August 2006 06:23 Go to previous messageGo to next message
kunalrai
Messages: 4
Registered: August 2006
Location: noida
Junior Member
yes I have created the directory and also given permissions by wrting :

CREATE OR REPLACE DIRECTORY external_data_dir AS 'C:\oracle\kunalexternal\mySID\data';

GRANT READ,WRITE ON DIRECTORY external_data_dir TO lippo_test
Re: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185738 is a reply to message #185736] Thu, 03 August 2006 06:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I mean did you actually create the directory on the server's file system? Can you go to the server and see the directory thru Windows Explorer on the file system with the appropriate permissions?

Just creating the directory in Oracle doesn't create the directory on the file system.
Re: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185739 is a reply to message #185736] Thu, 03 August 2006 06:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you actually have OS permission to write to that directory.

The error looks like it's erroring trying to create the log file.
Re: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185874 is a reply to message #185739] Thu, 03 August 2006 23:55 Go to previous messageGo to next message
kunalrai
Messages: 4
Registered: August 2006
Location: noida
Junior 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:\oracle\kunalexternal\mySID\data in OS and kept the CAS_INT_AUTO.txt file which holds the data in csv format.The example date is given below.

12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
02) The directory is created in the database by using the following syntax

create or replace directory external_data_dir as 'C:\oracle\kunalexternal\mySID\data';

03) The External table is created as follows

ACCTMED> ed
Wrote file afiedt.buf

CREATE TABLE EXTERNAL_AUTO_TABLE
(
CIA_REC_LENGTH NUMBER(5),
CIA_APP_ID_C NUMBER(14),
CIA_BRANCH_NO NUMBER(5),
CIA_FROM_ACCOUNT NUMBER(11),
CIA_FROM_APPLICATION_TYPE VARCHAR2(1) ,
CIA_TO_BRANCH NUMBER(5),
CIA_TO_ACCOUNT NUMBER(11),
CIA_TO_APPLICATION_TYPE VARCHAR2(1),
CIA_TRANSFER_AMMOUNT_CODE VARCHAR2(1),
CIA_SPECIAL_FREQUENCY VARCHAR2(1) ,
CIA_TRANSFER_DATE NUMBER(Cool,
CIA_END_DATE NUMBER(Cool,
CIA_END_TRANSFER_CODE VARCHAR2(1),
CIA_STATEMENT_NOTICE_DESC VARCHAR2(16),
CIA_PROVE_CODE NUMBER(10),
CIA_TO_TRANCODE NUMBER(5),
CIA_FROM_TRANCODE NUMBER(5),
CIA_MAPPEDVALUES VARCHAR2(20),
CIA_SECONDSUM VARCHAR2(20),
CIA_FIRSTSUM VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY external_data_dir
ACCESS parameters
(
records delimited BY newline
fields terminated BY ','
)
location(EXTERNAL_DATA_DIR:'CAS_INT_AUTO.txt')
);

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_AUTO_TABLE
*

Though no error is comming but only first record is getting selected and displayed in the external table,other lines are not getting populated.


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

Thanks & Regards,

KUNAL RAI
Re: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log [message #185876 is a reply to message #185874] Fri, 04 August 2006 00:18 Go to previous message
kunalrai
Messages: 4
Registered: August 2006
Location: noida
Junior 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:\oracle\kunalexternal\mySID\data in OS and kept the CAS_INT_AUTO.txt file which holds the data in csv format.The example date is given below.

12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
12345,1234567890123,12345,12345678901,a,12345,12345678901,b,c,d,12345678,12345678,e,ciastatementnot,1234567890,12345,12345,mapped_val ues,second_sum,first_sum
02) The directory is created in the database by using the following syntax

create or replace directory external_data_dir as 'C:\oracle\kunalexternal\mySID\data';

03) The External table is created as follows

ACCTMED> ed
Wrote file afiedt.buf

CREATE TABLE EXTERNAL_AUTO_TABLE
(
CIA_REC_LENGTH NUMBER(5),
CIA_APP_ID_C NUMBER(14),
CIA_BRANCH_NO NUMBER(5),
CIA_FROM_ACCOUNT NUMBER(11),
CIA_FROM_APPLICATION_TYPE VARCHAR2(1) ,
CIA_TO_BRANCH NUMBER(5),
CIA_TO_ACCOUNT NUMBER(11),
CIA_TO_APPLICATION_TYPE VARCHAR2(1),
CIA_TRANSFER_AMMOUNT_CODE VARCHAR2(1),
CIA_SPECIAL_FREQUENCY VARCHAR2(1) ,
CIA_TRANSFER_DATE NUMBER(,
CIA_END_DATE NUMBER(,
CIA_END_TRANSFER_CODE VARCHAR2(1),
CIA_STATEMENT_NOTICE_DESC VARCHAR2(16),
CIA_PROVE_CODE NUMBER(10),
CIA_TO_TRANCODE NUMBER(5),
CIA_FROM_TRANCODE NUMBER(5),
CIA_MAPPEDVALUES VARCHAR2(20),
CIA_SECONDSUM VARCHAR2(20),
CIA_FIRSTSUM VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY external_data_dir
ACCESS parameters
(
records delimited BY newline
fields terminated BY ','
)
location(EXTERNAL_DATA_DIR:'CAS_INT_AUTO.txt')
);

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_AUTO_TABLE
*

Though no error is comming but only first record is getting selected along with the record in the second line for last field i.e for first_sum coloumn ,value first_sum12345 is comming.because between them there is no comma .I want to ask why all other records in different lines are not comming?


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

Thanks & Regards,

KUNAL RAI
Previous Topic: Specifically inserting a Row into a Result (+_+)
Next Topic: Problem in number selection
Goto Forum:
  


Current Time: Tue Dec 06 10:18:20 CST 2016

Total time taken to generate the page: 0.05800 seconds