CODEWITHSUNDEEP
oracle-databaseoracle11gsql-loader
arjun gaur
arjun gaur

Reputation: 548

Unable to load data using external table

I'm trying to load data in an external table from a csv file. Following is my fragment:

create table emp_ext
(
eid number,ename char(9)
)
organization external
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test
ACCESS PARAMETERS
       (
       RECORDS DELIMITED BY NEWLINE
       FIELDS TERMINATED BY ','
       (
       eid number,
       ename char(9)
       )

       )
       LOCATION('C:\Users\99002971\Desktop\empf.csv')
)

create directory test as 'C:\Users\99002971\Desktop'
grant read on directory test to matuat35 // granted using another user

When i do select * from emp_ext , i get following errors:

ORA-29913:Error in executing ODCIEXTTABLEOPEN callout
ORA-29400:data cartridge error
KUP-00554:error encountered while parsing access parameters
KUP-01005:syntax error:found ""identifier:expecting one of :"binary_float,binary_double,comma,char,date,double"
KUP-01008:the bad identifier was double
KUP-01007:at line 4 column 12

Please help

Upvotes: 1

Views: 1920

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The datatype_spec section of the external table documenation shows that number isn't recognised by the loader driver. In your code that is being seen as an identifier rather than a data type, hence the error.

You can use oracle_number, or unsigned integer since it's presumably always going to be a positive integer for an employee ID; or leave it untyped and allow implicit conversion to the table column type.

Your location should also only specify the file name within the directory, not the full path:

create table emp_ext
(
eid number,ename varchar2(15)
)
organization external
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test
ACCESS PARAMETERS
       (
       RECORDS DELIMITED BY NEWLINE
       FIELDS TERMINATED BY ','
       (
       eid unsigned integer external(9),
       ename char(15)
       )

       )
       LOCATION('empf.csv')
)

Or more simply, but relying on implicit conversion:

...
ACCESS PARAMETERS
       (
       RECORDS DELIMITED BY NEWLINE
       FIELDS TERMINATED BY ','
       (
       eid,
       ename char(15) rteim
       )

       )
       LOCATION('empf.csv')
)

Upvotes: 2

Related Questions