Home » SQL & PL/SQL » SQL & PL/SQL » Help
Help [message #243368] Thu, 07 June 2007 02:15 Go to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi All,

I have data in a flat file.
is there any option to open this file in oracle without loading into a table or i have to load the data into a table.

rgds
Hari Krishna

Re: Help [message #243373 is a reply to message #243368] Thu, 07 June 2007 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"external table"

Regards
Michel
Re: Help [message #243390 is a reply to message #243368] Thu, 07 June 2007 03:25 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Michel

When i googled for "External Table" i got the link

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

i created text file and when executing following command iam getting error


sql> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\temp\';


and the error is

ORA-01031: insufficient privileges

which privilage i have to give

thanks & rgds
Hari Krishna

[Updated on: Thu, 07 June 2007 03:26]

Report message to a moderator

Re: Help [message #243393 is a reply to message #243390] Thu, 07 June 2007 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create any directory

But DBA can create the directory and gives you the rights to read and write in it.

Regards
Michel
Re: Help [message #243394 is a reply to message #243368] Thu, 07 June 2007 03:45 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member



Thanks Michel

rgds
Hari Krishna
Re: Help [message #243400 is a reply to message #243368] Thu, 07 June 2007 03:59 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Michel,

Still iam facing some problems.

first i explain what i did

1. First i created Directory hari in 'C' Drive.

2. Created two text files hk.txt and hk1.txt.

3. executed this command

SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\hari\'

4.

SQL> CREATE TABLE countries_ext (
country_code VARCHAR2(5),
country_name VARCHAR2(50),
country_language VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
country_code CHAR(5),
country_name CHAR(50),
country_language CHAR(50)
)
)
LOCATION ('hk.txt','hk1.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;



Up to here iam ok but when i am selecting records iam facing problems.

Error message is

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file COUNTRIES_EXT_540_2012.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


In that link i mentioned above , he has given that If the load files have not been saved in the appropriate directory then this error will come.

but i dont know where iam dng mistake.

iam connecting oracle thru "System" schema.


rgds
Hari Krishna

[Updated on: Thu, 07 June 2007 04:00]

Report message to a moderator

Re: Help [message #243407 is a reply to message #243400] Thu, 07 June 2007 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The file must be on the server not on the client.
Is this the case?

The directory and files must be readable by the owner of the instance (the user that starts it).
Is this the case?

(or maybe is this the owner of the listener)

Regards
Michel

[Updated on: Thu, 07 June 2007 04:11]

Report message to a moderator

Re: Help [message #243413 is a reply to message #243368] Thu, 07 June 2007 04:16 Go to previous message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

hi Michel

i created directory in client machine not in server

ok right now idnt have access to server

i will try it tomarrow

thanks for your suggestions

rgds
Hari Krishna

Previous Topic: exclude a group of records
Next Topic: How to release locks on a table
Goto Forum:
  


Current Time: Sat Dec 10 07:20:57 CST 2016

Total time taken to generate the page: 0.10315 seconds