Home » SQL & PL/SQL » SQL & PL/SQL » Oracle External Table
Oracle External Table [message #435109] Mon, 14 December 2009 06:12 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,
I tried to upload the Data through External Table.
SQL> create table ext_emp(ename varchar2(20),age number)
  2  organization external(
  3  type oracle_loader
  4  default directory dir1
  5  access parameters
  6  (fields terminated by ','
  7  )
  8  location('emp.txt')
  9  )
 10  /

Table created.

SQL> desc ext_emp;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ENAME                                                          VARCHAR2(20)
 AGE                                                            NUMBER

table got created but while selecting from the table i am getting the following error. can anyone please help me.
SQL> select * from ext_emp;
select * from ext_emp
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file EXT_EMP_2604_4584.log
Re: Oracle External Table [message #435110 is a reply to message #435109] Mon, 14 December 2009 06:24 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.adp-gmbh.ch/ora/err/29913.html
http://www.dba-oracle.com/t_ora_29913_external_table_error.htm

http://www.oracle-base.com/articles/9i/ExternalTables9i.php


especially on this

http://www.orafaq.com/node/848

The ORA-29913 error can also happen in external tables when you don't grant read and write permissions to the directory.
And always post your Oracle version with 4 digits.

sriram Smile

[Updated on: Mon, 14 December 2009 06:33]

Report message to a moderator

Re: Oracle External Table [message #435112 is a reply to message #435110] Mon, 14 December 2009 06:35 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
I created the Directory as
CREATE OR REPLACE DIRECTORY dir1 as 'D:\BPEL'
then why i need 2 give grant permission?
Re: Oracle External Table [message #435113 is a reply to message #435112] Mon, 14 December 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Yes.
2/ And you need to be sure that the instance owner has access to the directory and file.

Regards
Michel
Re: Oracle External Table [message #435118 is a reply to message #435112] Mon, 14 December 2009 07:00 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ramya29p wrote on Mon, 14 December 2009 13:35

CREATE OR REPLACE DIRECTORY dir1 as 'D:\BPEL'

"D:\BPEL" should be a directory on a database server, not your local PC. Is that the case?
Re: Oracle External Table [message #435119 is a reply to message #435113] Mon, 14 December 2009 07:09 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,I have given the read permission as
GRANT READ ON DIRECTORY dir1 TO PUBLIC;
But still i am getting the same problem.

Re: Oracle External Table [message #435120 is a reply to message #435118] Mon, 14 December 2009 07:09 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Good point Wink
/forum/fa/1581/0/

sriram Smile
Re: Oracle External Table [message #435121 is a reply to message #435119] Mon, 14 December 2009 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But still i am getting the same problem.

Answer below:

Quote:
2/ And you need to be sure that the instance owner has access to the directory and file.

Quote:
"D:\BPEL" should be a directory on a database server, not your local PC. Is that the case?


Regards
Michel

[Updated on: Mon, 14 December 2009 07:13]

Report message to a moderator

Re: Oracle External Table [message #435122 is a reply to message #435109] Mon, 14 December 2009 07:31 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
sorry i created the path with my local pc directory.
After creating directory on a database server. i am getting the below error
select * from abc1

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

Re: Oracle External Table [message #435123 is a reply to message #435122] Mon, 14 December 2009 07:38 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
ORA-30653: reject limit reached
Cause: the reject limit has been reached.

Action: Either cleanse the data, or increase the reject limit.



And always search before posting.
Always add/provide your Oracle version with 4 digits along with your post.

Sriram Smile

[Updated on: Mon, 14 December 2009 07:49]

Report message to a moderator

Re: Oracle External Table [message #435130 is a reply to message #435122] Mon, 14 December 2009 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check your data, they do not fit your table description.
Check you transfered your file in ASCII format (and not BINARY one).

Regards
Michel

[Updated on: Mon, 14 December 2009 08:17]

Report message to a moderator

Re: Oracle External Table [message #435373 is a reply to message #435130] Tue, 15 December 2009 21:08 Go to previous message
eoracleapps
Messages: 17
Registered: November 2009
Location: California
Junior Member

Check your data .Try to populate some simple data without any spaces in start and end and see everything fine.

I got this error some time back , but not able to recall the resoloution.


eoracleapps
Previous Topic: Needed PL/SQL Procedure for this 2nd Table.(and others merged 5)
Next Topic: Extracting data from BLOB data type (merged)
Goto Forum:
  


Current Time: Tue Apr 23 12:05:41 CDT 2024