Error while accessing External table.
539790Oct 16 2006 — edited Oct 16 2006Hi All,
While accessing oracle external table. I created the table with the following query.
CREATE OR REPLACE DIRECTORY load_dir AS '\\oraaps\Exceldata\'
;
CREATE TABLE my_sheet
(
DEPTNO NUMBER,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
badfile load_dir:'my_sheet.bad'
logfile load_dir:'my_sheet.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
DEPTNO,
DNAME,
LOC
))
LOCATION ('my_sheet.csv')
)REJECT LIMIT UNLIMITED;
I am sure that the table and the directory got created because i can see the table in the SQL developer. But whenever i say select * from my_sheet i'm getting the following error in the log file.
LOG file opened at 10/16/06 14:48:21
Field Definitions for table mysheet
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
DEPTNO NUMBER Terminated by ","
Trim whitespace same as SQL Loader
DNAME VARCHAR2(14),
Terminated by ","
Trim whitespace same as SQL Loader
LOC VARCHAR2(13)
Terminated by ","
Trim whitespace same as SQL Loader
KUP-04001: error opening file \\oraaps\Exceldata\mysheet.csv
KUP-04017: OS message: The data is invalid.
Please do reply..Its urgent from my project deliverable point of view.
Any help appreciated.
Thanks and Regards.
V.Venkateswara Rao