Home » SQL & PL/SQL » SQL & PL/SQL » Error after running control file in toad (Oracle 10 G, Toad)
Error after running control file in toad [message #428064] Tue, 27 October 2009 01:16 Go to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Hi experts:

I ran the following code in toad.
create table ext_table_csv (
  i   Number,
  n   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    BADFILE 'D:\wence\External_tables\test.bad'
    LOGFILE 'D:\wence\External_tables\test.log'
    fields  terminated by ','
    missing field values are null
  )
  location ('D:\wence\External_tables\test.csv')
)
reject limit unlimited;


After the execution, I issue the following statement.
select *
from ext_table_csv


Then i get this error.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01006: error signalled during parse of access parameters
KUP-00562: unknown escape sequence
ORA-06512: at "SYS.ORACLE_LOADER", line 19


I can't figure out the problem. Kindly guide me.
Re: Error after running control file in toad [message #428068 is a reply to message #428064] Tue, 27 October 2009 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Location and other "file" parameter are file name without path. Path is given by "directory" parameter or fields.

Regards
Michel
Re: Error after running control file in toad [message #428077 is a reply to message #428068] Tue, 27 October 2009 02:58 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Thanks for your reply.

What's wrong with this code?
CREATE OR REPLACE DIRECTORY ext_dir AS 'D:\wence\External_tables';

GRANT READ ON DIRECTORY ext_dir TO public;

GRANT WRITE ON DIRECTORY ext_dir TO public;


create table ext_table_csv (
  i   Number,
  n   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    BADFILE 'test.bad'
    LOGFILE 'test.log'
    fields  terminated by ','
    missing field values are null
  )
  location ('test.csv')
)
reject limit unlimited;


I'm still getting the same error.
Re: Error after running control file in toad [message #428080 is a reply to message #428077] Tue, 27 October 2009 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You do know that directory must be located on a server, not your local PC, don't you?
Re: Error after running control file in toad [message #428081 is a reply to message #428077] Tue, 27 October 2009 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Error after running control file in toad [message #428227 is a reply to message #428064] Tue, 27 October 2009 09:16 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Thanks for your replies.

I don't know littlefoot, this is my first time to try.

What do you mean Michel?
Re: Error after running control file in toad [message #428247 is a reply to message #428227] Tue, 27 October 2009 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What part of my sentence don't you understand?
Anyway, first be sure the directory is on the server.

Regards
Michel

[Updated on: Tue, 27 October 2009 09:48]

Report message to a moderator

Re: Error after running control file in toad [message #428321 is a reply to message #428247] Tue, 27 October 2009 19:28 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

What do you mean by copy your session.

It is to connect via SQL *Plus and run my code in it?

Okay, I'll try to change my directory first.

I ran it and it gives me the same error.
All code successfully executed but the select statement.

[Updated on: Tue, 27 October 2009 19:45]

Report message to a moderator

Re: Error after running control file in toad [message #428324 is a reply to message #428321] Tue, 27 October 2009 19:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com

do keyword search on "external table"


post results from following SQL

SELECT TABLE_NAME, DEFAULT_DIRECTORY_OWNER, DEFAULT_DIRECTORY_NAME
FROM USER_EXTERNAL_TABLES
/


[Updated on: Tue, 27 October 2009 20:42]

Report message to a moderator

Re: Error after running control file in toad [message #428341 is a reply to message #428321] Wed, 28 October 2009 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What do you mean by copy your session.

It is to connect via SQL *Plus and run my code in it?

Yes use SQL*Plus to execute your code, then use copy feature to copye the session in clipboard, then use paste to paste it in your post adding code tags.

Regards
Michel
Re: Error after running control file in toad [message #428378 is a reply to message #428324] Wed, 28 October 2009 02:55 Go to previous message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

BlackSwan wrote on Wed, 28 October 2009 00:52
http://asktom.oracle.com

do keyword search on "external table"


post results from following SQL

SELECT TABLE_NAME, DEFAULT_DIRECTORY_OWNER, DEFAULT_DIRECTORY_NAME
FROM USER_EXTERNAL_TABLES
/


SQL> edit
Wrote file afiedt.buf

  1  SELECT TABLE_NAME, DEFAULT_DIRECTORY_OWNER, DEFAULT_DIRECTORY_NAME
  2* FROM USER_EXTERNAL_TABLES
SQL> /

TABLE_NAME                     DEF DEFAULT_DIRECTORY_NAME
------------------------------ --- ------------------------------
EXT_TABLE_CSV                  SYS EXT_DIR
Previous Topic: Comparing Overlapping Dates
Next Topic: finding sum from inner query
Goto Forum:
  


Current Time: Sun Dec 04 12:38:46 CST 2016

Total time taken to generate the page: 0.19447 seconds