Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle External tables

Posted on 2014-04-15
9
Medium Priority
?
843 Views
Last Modified: 2014-04-15
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
Comment
Question by:GouthamAnand
  • 4
  • 4
9 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40001142
Please post your sample data too..

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40001172
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
 

Author Comment

by:GouthamAnand
ID: 40001194
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GouthamAnand
ID: 40001262
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
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 2000 total points
ID: 40001378
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
 

Author Comment

by:GouthamAnand
ID: 40001508
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40001569
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40001615
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
 

Author Closing Comment

by:GouthamAnand
ID: 40001951
Thank you very much for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question