-
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
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
"
-
-1?
Did you try this:
substr(J_DESCRIPTION,1,DBMS_LOB.GETLENGTH(J_DESCRIPTION)-1)||???
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
multiple lines
Originally Posted by LKBrwn_DBA
Did you try this:
substr(J_DESCRIPTION,1,DBMS_LOB.GETLENGTH(J_DESCRIPTION)-1)||???
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!
-
Last edited by PAVB; 01-28-2014 at 11:14 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|