Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle data export

raj111
Occasional Visitor

oracle data export

hi

i want to export data from oracle database to "|" separated csv files, and file size should not be exceeded then 1GB. I have a lot of data to export.
please suggest me how to do that...

4 REPLIES
Alexander M. Ermes
Honored Contributor

Re: oracle data export

Hi there.
I think, you will have to do it by sqlplus.
select field1 || ';' || field2||';'||

or by a script with dbms_output to limit your filesize.

make your lines large enough ( set lines 5000 ) and your file without braks ( set pages 0).
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Steven E. Protter
Exalted Contributor

Re: oracle data export

Shalom,

The toad oracle toolset also provides the ability to write sql statements and export them to a file with any delimiter you choose.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com

Re: oracle data export

Hi,

Yes, you can use SQL*Plus, here is what you need to do:
http://oracle.ittoolbox.com/groups/technical-functional/oracle-sql-l/how-to-export-the-content-of-a-table-into-csv-and-then-xls-1663934

Donâ t forget to enable SERVEROUTPUT parameter in SQL*Plus and set it to 1000000 (max value in bytes for Oracle 10gR1 and before)

Bear in mind that PAGESIZE parameter has maximum value 50000.

Regards
Vadim
Yogeeraj_1
Honored Contributor

Re: oracle data export

hi,

Write the script that would run using sqlplus as follows:

e.g.
spool file1.csv
set escape '\'
select field1||'\|'||field2||'\|'||field3
from tab1
where

should be such that the size of file that would be generated does not exceed the 1GB specified. NB. You would have to do some tests first.

hope this helps!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)