Home » SQL & PL/SQL » SQL & PL/SQL » Hi I am fresh to this ..plz help me
Hi I am fresh to this ..plz help me [message #199937] Fri, 27 October 2006 01:04 Go to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Hi I am Sundar

Welcome to all frds

I have one Q?
Plz help me on this

In creating external table ,i created directory EXT and table ad EXT_TAB
they are done succesfully

but while i am trying "Select * from ext_tab",I am getting the following error.

Error on line 0
select * from ext_tab

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file demo1.txt in EXT not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

but i have file demo1.txt in c:\external location,
plz help me ASAP

Urs New Frd
Sundar
Re: Hi I am fresh to this ..plz help me [message #199940 is a reply to message #199937] Fri, 27 October 2006 01:23 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here is an example; see if you followed all steps, try it and say if it still doesn't work (did you, perhaps, forgot to GRANT privileges on a directory to the user?).
C:\TEMP>sqlplus system/pwd

create or replace directory ext_dir as 'c:\temp';

grant read, write on directory ext_dir to scott;

connect scott/tiger@ora10g

CREATE TABLE ext_table_csv 
(  sifra        VARCHAR2(10),
   tvbr         VARCHAR2(20),
   iznos        NUMBER
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS 
  ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ';'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('test.txt')
)
REJECT LIMIT UNLIMITED
/

SELECT * FROM ext_table_csv;
Re: Hi I am fresh to this ..plz help me [message #199943 is a reply to message #199937] Fri, 27 October 2006 01:34 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Nice to see reply

steps i followed

connected as sys
----------------
CREATE OR REPLACE DIRECTORY ext AS 'c:\external\';

GRANT READ ON DIRECTORY ext TO scott;

GRANT WRITE ON DIRECTORY ext TO scott;

Connected to scott
---------------------
CREATE TABLE ext_tab (
empno CHAR(4),
ename CHAR(20),
job CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(empno, ename, job, deptno))
LOCATION ('demo1.txt'))
PARALLEL
REJECT LIMIT 0;

select * from ext_tab;

But I am Using Oracle9i with TOAD s/w
U said @10g
I am getting problem again...

From Sundar
Re: Hi I am fresh to this ..plz help me [message #199948 is a reply to message #199943] Fri, 27 October 2006 01:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you have a file c:\external\demo1.txt ON YOUR SERVER?
Re: Hi I am fresh to this ..plz help me [message #199950 is a reply to message #199948] Fri, 27 October 2006 01:58 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Yes

I have demo1.txt on my machine at c:\extenal folder

Why this error is generated.its too cruical on my path
Re: Hi I am fresh to this ..plz help me [message #199951 is a reply to message #199950] Fri, 27 October 2006 02:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Frank asked whether this file was ON YOUR SERVER, not on your PC (unless there's Oracle server on your PC as well).
Re: Hi I am fresh to this ..plz help me [message #199953 is a reply to message #199937] Fri, 27 October 2006 02:07 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
No sir
I did not have it on my server

Plz tell me how to place it...

Re: Hi I am fresh to this ..plz help me [message #199954 is a reply to message #199953] Fri, 27 October 2006 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to ask your System Administrator to grant you access to a folder on Oracle Server. Then, you'll be able to map this folder to act as if it was a local disk and that'll do it. However, if he/she can not grant you access, you'll have to ask him/her to put the file into one for you (and tell you its name so that you could properly create an external table).
icon7.gif  Re: Hi I am fresh to this ..plz help me [message #199955 is a reply to message #199937] Fri, 27 October 2006 02:16 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Thank u sir
I will ask her and my result will be let u know

Thanks very much
icon7.gif  Re: Hi I am fresh to this ..plz help me [message #199971 is a reply to message #199954] Fri, 27 October 2006 04:11 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Hi Sir,

I got it thank u very much
Re: Hi I am fresh to this ..plz help me [message #199978 is a reply to message #199971] Fri, 27 October 2006 04:50 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Great!
Previous Topic: How to use denserank function
Next Topic: About ORA-01410: invalid ROWID error
Goto Forum:
  


Current Time: Sun Dec 04 19:09:37 CST 2016

Total time taken to generate the page: 0.07282 seconds