Selecting a CLOB into .csv
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Selecting a CLOB into .csv

  1. #1
    Join Date
    Feb 2001
    Location
    Location: Boston, MA
    Posts
    20

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458
    Try:

    SET LONG 32000

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2001
    Location
    Location: Boston, MA
    Posts
    20
    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.
    "

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458

    Cool -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

  5. #5
    Join Date
    Jan 2014
    Posts
    3

    multiple lines

    Quote Originally Posted by LKBrwn_DBA View Post
    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!

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    sorry, wrong thread
    Last edited by PAVB; 01-28-2014 at 10: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
  •  



Click Here to Expand Forum to Full Width