• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

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
0
GouthamAnand
Asked:
GouthamAnand
  • 4
  • 4
1 Solution
 
Wasim Akram ShaikCommented:
Please post your sample data too..

also what is the error you have encountered, while creating the directory..?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
GouthamAnandAuthor Commented:
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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
GouthamAnandAuthor Commented:
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)?
0
 
Wasim Akram ShaikCommented:
To answer your first query..

Those records are rejected just because you cannot add a space character in date field

  50|ay|0919                   |                    |poogoorddreef 9a     |      |1101 aa amszerdam ZO      |amszerdam       |   |1101 aa    |Nm  |  
  | | |   |        


I had removed the extra spaces in the last column create_date column, now the file is getting uploaded, changing the date type of that column to varchar2  should work.. uploading the same file again this time with modifications for your reference

regarding the access parameters attributes

they are used in case you want the file name to be generated dynamically

%p is replaced by the process ID of the current process. For example, if the process ID of the access driver is 12345, then exttab_%p.log becomes exttab_12345.log.

%a is replaced by the agent number of the current process. The agent number is the unique number assigned to each parallel process accessing the external table.
This number is padded to the left with zeros to fill three characters. For example, if the third parallel agent is creating a file and bad_data_%a.bad was specified as the filename, then the agent would create a file named bad_data_003.bad.
%% is replaced by %. If there is a need to have a percent sign in the filename, then this symbol substitution is used.

refer oracle docs for more info

http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch12.htm

Regarding the error

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

This generally is related to file permissions, check you have permissions for the file on the specified folder and also you have permissions on the directory as well..
ADDRESS-DATA.txt
0
 
GouthamAnandAuthor Commented:
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.
0
 
Wasim Akram ShaikCommented:
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
0
 
Wasim Akram ShaikCommented:
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..
0
 
GouthamAnandAuthor Commented:
Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now