asked on

Oracle External tables

Hi
1) I created external table and accessing a file. But I am getting the below error in log file and 3 records got rejected. This is becuse CREATE_DATE_TIME null in the flat file.

error processing column CREATE_DATE_TIME in row 103 for datafile C:\test\ADDRESS_DATA.txt

But when I load through sql loader these three records also getting loaded.

So can you please let me know how can I access there 3 records also even though the create_date_time value is not present in the flat file?
Please find the attached external table creation script file.

2) Also can you please let me know if I can create the Oracle directory referring the network path and access the flat file for the oracle external table?
I tried to give the network path but not able to create oracle directory.
Just wanted to know path problem which I am giving or I cannot create oracle directory for a network path itself.

Can you please suggest for both the questions?

Thanks.
ee-address.txt

Wasim Akram Shaik

Please post your sample data too..

also what is the error you have encountered, while creating the directory..?

Guy Hengel [angelIII / a3]

I think you need to add a NULLIF specs to your external table ...
http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch12.htm

GouthamAnand

ASKER

Please find the attched data file. In the file for the last column(date field), 5th row and 7th row do not have the data for date field.

Also I could able to create the directory.

But while accessing through the external table that oracle directory,
am getting the error

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file \\punhtcngs09\ABCD\Monthly\empxt000_1620_5548.log
ADDRESS-DATA.txt

GouthamAnand

ASKER

Also one more question(3rd) is I used empxt%a_%p in external table creation and log file created is empxt000_1620_5248.
can you please let me know what this %a_%p mean(I just used based on an example script found)?

ASKER CERTIFIED SOLUTION

Wasim Akram Shaik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GouthamAnand

ASKER

ok thank you. By changing the date field to varchar2 I could load the data.
I did not get how to use NULLIF. I think we do not have an option to trim the data before loading like we do in sql loader.(CREATE_DATE_TIME "trim(:CREATE_DATE_TIME )" ) so that I can keep the date data type.

Can you also please suggest if we can create the oracle directory for a file in LAN path
(for windows os) and refer in oracle external tables?

Thanks.

Wasim Akram Shaik

You can use the same syntax as

CREATE DIRECTORY (network directory path)

this should be be accessible from the Oracle server and should have relevant permissions for the oracle user

Wasim Akram Shaik

I think that you may need to map the network drive for this to work..

try it out.. else open up a new question related to this.. so that experts can help out in this regard..

GouthamAnand

ASKER

Thank you very much for your help.