Hi,
I am trying to use oracle external table feature to load flat file into database but encounter a error while execute SELECT statement on external table which i have created.
Below are the exact steps that i am following --
/user2/cbusdev01/smp43/domains/columbus/tools>mkdir data_1
/user2/cbusdev01/smp43/domains/columbus/tools>chmod 777 data_1
create a file inside the data_1 folder add_len.csv
Barbados_1000230,2461000230,2461000230,SSLN 00 0 00 138
Barbados_1000229,2461000229,2461000229,SSLN 00 0 00 139
Barbados_1000228,2461000228,2461000228,SSLN 00 0 03 14
login to sysdba --
grant create any directory to cbusdev01cm;
grant read, write on DIRECTORY data_1 to cbusdev01cm;
Now create a external table --
CREATE TABLE add_len_ext (
country_code VARCHAR2(20),
sip NUMBER,
tn NUMBER,
len VARCHAR2(30)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE data_1:'bad.bad'
LOGFILE data_1:'log.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
country_code CHAR(50),
sip CHAR(50),
tn CHAR(50),
len CHAR(50)
)
)
LOCATION (data_1:'add_len.csv')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;
Table ADD_LEN_EXT created.
Then i fire the SELECT statement
select * from ADD_LEN_EXT;
And it gives me --
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /user2/cbusdev01/smp43/domains/columbus/tools/log.log
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
I am not getting this error !!
I have granted all the permission to concerns but still the error is coming.
I am using --
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production