[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2604
  • Last Modified:

Obtain csv file from Oracle table with LOB field

On sqlplus I do:
sql>SPOOL /var/www/myfile.csv
sql>select id, to_char(DBMS_LOB.SUBSTR(F1,4000, 1)) FROM myTab;
sql>spool off;

The CLOB fields have multiple lines in the output .csv file. I cannot parse the file (as a comma-separated one).
I parse the .csv file assuming one line represents one record of the source table.

Is there any way to use the CLOB field as well in the .csv file..?thanks.
0
toooki
Asked:
toooki
3 Solutions
 
toookiAuthor Commented:
Thank you. Looking into it.
0
 
Plk_In_EECommented:
Hi,
If you are using PL SQL developer its very east to export the table data into CSV format as per below steps, there should be provision will be there for other tools as well,
1. Right click on the table data which you want to export
2. there will be 3 tabs . oracle export and SQL inserts , PL SQL developer
3. choose oracle  export. give the if there is any condition in where clause
4. give some name and location for the extract.
5. click on export and you are done..

thanks
 
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
toookiAuthor Commented:
Thank you.
No actually I need to automate the entire prcoess -- dump from the source Oracle db table (with CLOB field). Then sql loader to populate to Oracel table in another database.
The above works fine with non-CLOB fields. But stuck with CLOB one.

So Pl/SQL (which is a one-time matter) is not an option..
Looking at the links but those are written at a high level .. tying to understand..
Thanks.
0
 
JacobfwCommented:
Try this:

SET HEAD OFF
SET PAGES 0
SET LINES 5000
SET TRIMSPOOL ON
SET TAB OFF
SET FEEDBACK OFF
SET LONG 4000
COL c FORM A5000
SPOOL /var/www/myfile.csv
select id ||','|| to_char(DBMS_LOB.SUBSTR(F1,4000, 1)) c FROM myTab
/

0
 
toookiAuthor Commented:
Thank you. Actually I also used some similar query and could spool to the CSV file the CLOB file.
And it worked.
Thanks.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now