Home » SQL & PL/SQL » SQL & PL/SQL » External Table
External Table [message #323666] Thu, 29 May 2008 03:16 Go to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
Hi

when i execute the following code,following error will come..
ERROR at line 7:
ORA-22929: invalid or missing directory name
Can anyone clarify this..

CREATE TABLE SAMPLE2_EXT (
field1 varchar2(4000)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY /usr2/ecs/work/naga
ACCESS PARAMETERS
(
records delimited by newline
badfile DAT_DIR:'SAMPLE2_EXT%a_%p.bad'
logfile DAT_DIR:'SAMPLE2_EXT%a_%p.log'
fields LRTRIM REJECT ROWS WITH ALL NULL FIELDS (field1 char(4000))
)
LOCATION ('sample2.dat')
)
REJECT LIMIT 10;
Re: External Table [message #323669 is a reply to message #323666] Thu, 29 May 2008 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Directory is an Oracle direcory object not an OS one.

Regards
Michel
Re: External Table [message #323670 is a reply to message #323666] Thu, 29 May 2008 03:35 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong, this is wrong:
DEFAULT DIRECTORY /usr2/ecs/work/naga
"Directory", in this context, isn't a "real" directory which exists on your disk, but Oracle object which is to be created (as a privileged user) using the CREATE DIRECTORY statement.

This is Windows example, but you'll get the point:
C:\TEMP>sqlplus system/pwd

create or replace directory ext_dir as 'c:\temp';   --> EXT_DIR is a directory name

grant read, write on directory ext_dir to kriz;

connect kriz/kriz@ora10g

CREATE TABLE ext_table_csv 
(  sifra_kpc    VARCHAR2(10),
   brojilo      VARCHAR2(20)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ext_dir                        --> EXT_DIR is then used in CREATE TABLE statement
...
Re: External Table [message #323674 is a reply to message #323670] Thu, 29 May 2008 03:44 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
when i execut that command...following error will come..

SQL> Create directory DAT_DIR as '/usr2/ecs/work/naga';
Create directory DAT_DIR as '/usr2/ecs/work/naga'
*
ERROR at line 1:
ORA-01031: insufficient privileges

i think i dnt have an privileges to execute this..
Re: External Table [message #323676 is a reply to message #323674] Thu, 29 May 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right.
Ask your DBA to create the directory for you and give you access to it.

Regards
Michel
Re: External Table [message #323681 is a reply to message #323674] Thu, 29 May 2008 03:56 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nagaa wrote on Thu, 29 May 2008 10:44
ORA-01031: insufficient privileges

i think i dnt have an privileges to execute this..

What do you think, why did I say
Quote:
... which is to be created (as a privileged user)
Re: External Table [message #323688 is a reply to message #323676] Thu, 29 May 2008 04:23 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
I'm new to this concept..
Pls correct me if anything wrong with me..

Why not i use my directory as a default directory?
Re: External Table [message #323689 is a reply to message #323688] Thu, 29 May 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read CREATE DIRECTORY

Regards
Michel

[Updated on: Thu, 29 May 2008 05:01]

Report message to a moderator

Re: External Table [message #323692 is a reply to message #323689] Thu, 29 May 2008 04:40 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
Hi,

Can u pls tell me the query for how to view the created dir?
Becaus i can use that directory for my use...
Re: External Table [message #323694 is a reply to message #323689] Thu, 29 May 2008 04:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

The link you have posted is pointing to your local machine.

Nagaa,
Check these links.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5007.htm#i2061958


http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1050.htm#i1576965

Regards

Raj
Re: External Table [message #323699 is a reply to message #323694] Thu, 29 May 2008 05:02 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Raj,
Thanks for pointing me to this error, I fix it.

Regards
Michel
Previous Topic: Reg: Materialized view logs
Next Topic: Oracle 9i database LDAP and Microsoft Active Directory
Goto Forum:
  


Current Time: Tue Dec 06 13:53:46 CST 2016

Total time taken to generate the page: 0.09702 seconds