Home » SQL & PL/SQL » SQL & PL/SQL » How to load oracle table data into a file (Oracle10g)
How to load oracle table data into a file [message #400191] Mon, 27 April 2009 01:49 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,
How to load oracle table data into file.
Re: How to load oracle table data into a file [message #400195 is a reply to message #400191] Mon, 27 April 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spool
utl_file
fetch/write
xml
dbms_lob

Regards
Michel
Re: How to load oracle table data into a file [message #400241 is a reply to message #400191] Mon, 27 April 2009 05:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Did you Google?
Did you search this forum before starting new thread?

Following example may help you.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:95212348059

regards,
Delna
Re: How to load oracle table data into a file [message #400352 is a reply to message #400241] Mon, 27 April 2009 14:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte has one page that has links to a variety of different methods:

http://asktom.oracle.com/tkyte/flat/index.html
Re: How to load oracle table data into a file [message #400408 is a reply to message #400191] Tue, 28 April 2009 01:40 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:95212348059

i ran the code given on this link.

and i executed that code using this parameter

select dump_csv('select * from lot_loan_t',',','orcl','test')from dual;

but i m getting the error message?
Re: How to load oracle table data into a file [message #400411 is a reply to message #400191] Tue, 28 April 2009 01:44 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>but i m getting the error message?
Would you like some cheese with your whine?


Error? what error?
Re: How to load oracle table data into a file [message #400426 is a reply to message #400191] Tue, 28 April 2009 02:58 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

This error message is getting when i try to execute it,
SQL> select dump_csv('select * from lot_loan_t',',','D:\orcl\','test.txt')from dual ;

select dump_csv('select * from lot_loan_t',',','D:\orcl\','test.txt')from dual

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "ULS5302DEV.DUMP_CSV", line 16
ORA-06512: at line 1

SQl>
Re: How to load oracle table data into a file [message #400428 is a reply to message #400426] Tue, 28 April 2009 03:08 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
ORA-29280: invalid directory path 
Cause: A corresponding directory object does not exist.
 
Action: Correct the directory object parameter, 
or create a corresponding directory object 
with the CREATE DIRECTORY command.
 



[Updated on: Tue, 28 April 2009 03:09]

Report message to a moderator

Re: How to load oracle table data into a file [message #400510 is a reply to message #400426] Tue, 28 April 2009 09:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte originally wrote the dump_csv function for an older version of Oracle, before Oracle directory objects existed. In newer versions, you need to create a directory object and use that instead of the directory path, like so:

CREATE OR REPLACE DIRECTORY your_dir AS 'D:\orcl';
 
select dump_csv
        ('select * from lot_loan_t',
         ',',
         'YOUR_DIR', -- must be in upper case
         'test.txt')
from dual;


[Updated on: Tue, 28 April 2009 09:21]

Report message to a moderator

Re: How to load oracle table data into a file [message #400547 is a reply to message #400510] Tue, 28 April 2009 12:44 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
And don't forget
grant read,write on directory your_dir to public;
Previous Topic: SQL : Update table based on the number of child attached
Next Topic: compare similar values
Goto Forum:
  


Current Time: Tue Dec 06 16:24:49 CST 2016

Total time taken to generate the page: 0.16259 seconds