Selecting a CLOB into .csv

Hi All,
I am currently trying to select all of the date in a table and spool it to a csv file. The issue is that one of the fields is a clob. When I run it I only get the first 80 characters of the record. When I take out the CLOB I get all the other fields (with the clob it cuts off after about 6 fields.) I know CLOBs can be tricky to work with, but I hope the solution is simple. Here's my SQL:

set pagesize 0
set termout off
set trimspool on
set linesize 32767
set feedback off
set trimout on
set underline off
set time off
set timing off
SET NEWPAGE 0
SET SPACE 0
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET tab off

/* Data for Extract */

spool foo.csv
select
',"'||TRIM(E_PARTNER_KEY)||'",'||
'"'||TRIM(E_NAME)||'",'||
'"'||TRIM(E_WEBSITE)||'",'||
'"'||TRIM(E_INDUSTRY)||'",'||
'"'||TRIM(C_PARTNER_KEY)||'",'||
'"'||TRIM(C_USERNAME)||'",'||
'"'||TRIM(C_PASSWORD)||'",'||
'"'||TRIM(C_CONTACTINFOACCESS)||'",'||
'"'||TRIM(C_FIRSTNAME)||'",'||
'"'||TRIM(C_LASTNAME)||'",'||
'"'||TRIM(C_EMAIL)||'",'||
'"'||TRIM(J_PARTNER_KEY)||'",'||
'"'||TRIM(J_TITLE)||'",'||
'"'||TRIM(J_OPPORTUNITY_TYPE)||'",'||
J_IS_FULLTIME||','||
'"'||TRIM(J_TRACK_DECISIONS)||'",'||
to_char(J_APPLY_STARTDATE, 'MM/DD/YY' )||','||
to_char(J_APPLY_ENDDATE, 'MM/DD/YY' )||','||
'"'||TRIM(J_COMPENSATION_DETAILS)||'",'||
'"'||TRIM(J_SPECIAL_INSTRUCTIONS_TYPE)||'",'||
'"'||TRIM(J_SPECIAL_INSTRUCTIONS)||'",'||
'"'||TRIM(J_ADDITIONAL_INSTRUCTIONS)||'",'||
J_TRACK_APPLICATIONS||','||
to_char(J_SEARCHABLE_BEGINDT, 'MM/DD/YY' )||','||
to_char(J_INACTIVATION_DT, 'MM/DD/YY' )||','||
J_IS_DEPLOYED_TO_ALL||','||
'"'||TRIM(J_CITY)||'",'||
'"'||TRIM(J_STATE)||'",'||
'"'||TRIM(J_COUNTRY)||'",'||'"',
/* Formatting of this column is off due to SQL-PLUS limitations, there is a large amount of whitespace between the previous column and this one. This field also can't be concatenated with the previous since it is a CLOB column. So for now, we have to live with a large amount of spaces embedded between fields. Also, the trailing quote at the end of the line is not showing up, there appears to be a problem with Concatenating text to the tail end of a CLOB. So, for the short term we are appending a quote to the end of the line in post-processing
*/
',"'||substr(J_DESCRIPTION,1,DBMS_LOB.GETLENGTH(J_DESCRIPTION))||'"'
from FOO_OUT
/

spool off

Try:

SET LONG 32000

:D

That helped. Thanks so much. The only other problem I have now is that when I try to append the final double quote at the end, a hard return is inserted right before that. I need the return after the quote.

For example, each record comes out looking like:

,"cxcore","CoreTechs, Inc","","Engineering - Other","dpk_cxcore.090104","du_cxcore.090104","","Hide Everything","Not","Specified","","cxcore.090104","HVAC Plumbing/Piping Sr. Engineer or Project Manag",Yes,"Job","No",08/05/04,09/04/04,"50-70 or 70-95k.","URL","","",No,08/05/04,09/04/04,No,"Seattle","WA","United States of America","Mechanical Construction/Engineering Company needs Senior Engineers and/or Project Managers with a proven track record in innovative design and management on large commercial projects.
"

Did you try this:

substr(J_DESCRIPTION,1,DBMS_LOB.GETLENGTH(J_DESCRIPTION)-1)||???

:confused:

Quote:

---

Originally Posted by LKBrwn_DBA

Did you try this:

substr(J_DESCRIPTION,1,DBMS_LOB.GETLENGTH(J_DESCRIPTION)-1)||???

:confused:

---

Hi, I know this is a very very old thread but I can't find any solution in the web. This thread is the closest thing I found to answer my problem.

So here it goes. I want to extract data and save it into a CSV file. One column from the table is CLOB. I followed the solution in the previous post and was able to extract the CLOB column but I encountered a different problem. Instead of giving me a single line for every row of data, the CSV file contains multiple lines per row of data. How can I solve this problem? Below is the sample of my SQL code:

SET LINESIZE 32000;

SET NEWPAGE 0;
SET SPACE 0;
SET PAGESIZE 0;
SET LONG 32000;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET TERMOUT OFF;
SET VERIFY OFF;
SET TRIMSPOOL ON;

SELECT NVL(ID, ' ') || '|' ||
NVL(AGTFLAG, 0) || '|' ||
NVL(ATTEMPTS, 0) || '|' ||
NVL(DTVALID, SYSDATE) || '|' ||
NVL(FNAME, ' ') || '|' ||
NVL(CODE, ' ') || '|' ||
NVL(LSTLOGIN, SYSDATE) || '|' ||
NVL(LNAME, ' ') || '|' ||
NVL(LSTPWRDUPDT, SYSDATE) || '|' ||
NVL(LSTPDTE, SYSDATE) || '|' ||
NVL(LGNFLAG, 0) || '|' ||
NVL(MNTNDBY, ' ') || '|' ||
NVL(MNAME, ' ') || '|' ||
NVL(PWRD, ' ') || '|' ||
NVL(ID, ' ') || '|' ||
NVL(REMARKS, ' ') || '|' ||
NVL(SESSION, ' ') || '|' ||
NVL(STAT, ' ') || '|' ||
NVL(TTLATTEMPTS, 0) || '|' ||
NVL(CREATIONDTE, SYSDATE) || '|' ||
NVL(BACKUPID, 0) || '|' ||
NVL(AUTHORIZATIONLMT, 0) || '|' ||
NVL(PWSOURCE, ' ') || '|' ||
NVL(SUBSTR(PWCERTIFICATE, 1, DBMS_LOB.GETLENGTH(PWCERTIFICATE) - 1), ' ') || '|' ||
NVL(EMAILADD, ' ') || '|'
FROM USER
WHERE CODE = '&1';

This is the sample result that I'm getting

120005|0|10|10-OCT-13|Kat|100001|12-DEC-13|Doedo|26-NOV-13|2
6-NOV-13|0| |D|password123|JASON_GALLARD
O_01| | |US04|10|10-OCT-13|0|0|MINIKEY|M
IICLjCCAZegAwIBAgIEN18U6zANBgkqhkiG9w0BA
QQFADBSMScwJQYDVQQEEx5CYW5rX29mX3RoZV9Qa
GlsaXBwaW5lX0lzbGFuZHMxJzAlBgNVBAMTHkJhb
mtfb2ZfdGhlX1BoaWxpcHBpbmVfSXNsYW5kczAeF
w0wNzA3MjMwNTIzMTRaFw0xNzA3MjQwNTIzMTRaM
GUxCzAJBgNVBAYTAlBIMQwwCgYDVQQKEwNCUEkxD
jAMBgNVBAsTBUZJUlMzMRwwGgYDVQQEExNKQVNPT
l9HQUxMQVJET19URVNUMRowGAYDVQQDExFKQVNPT
l9HQUxMQVJET18wMTCBnzANBgkqhkiG9w0BAQEFA
AOBjQAwgYkCgYEAp2ODdoLfZkEqZ7G02kGB4E38s
fls3PRGduBrwLjFHx/FnLNRajqXoljM3IPTZ/7ig
JABLH9XagZ+6pAwNr/ngebpmt5oz3lxwT4XZqCw6
yng04bYqXIvIbQJy9PtqDb/Ie9YDyO90J0xusNxt
QqxxD3ANjphwUQpzZbJmL2uZykCAwEAATANBgkqh
kiG9w0BAQQFAAOBgQAMxeMDzM7i0IihWjvE5xai/
rV5nrpQ6j3qjesG3bIPsuxctq0L2OEWyy2wg5lXX
fxcxvPxArhky5B4XPLor7suxNm+0hMYFh1tHkgvy
RHyVlHnY+d5ukzUUgxOubAv9V8ns0SaDwMDwHhXj
cQdW1jiGF4u9hdnFWwcERlvHcufpQ==
| |

Thanks in advance!

sorry, wrong thread