Solved

Oracle External tables

Posted on 2014-04-15
9
822 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 142

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now