extract a CLOB column to CSV
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: extract a CLOB column to CSV

  1. #1
    Join Date
    Jan 2014
    Posts
    3

    extract a CLOB column to CSV

    Hi. I'm trying to exctract a table containing a CLOB column and save it into a CSV file. I was able to extract the CLOB column but I encountered a problem. Instead of giving me a single line for every row of data, the CSV file contains multiple lines per row of data. If I remove the CLOB column, the result shows a single line for every 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!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    There are two approaches to this kind of problem. You can use formatted sql, in which case you need to name the column something like text.

    Code:
    SET LINESIZE 2048;
    SET NEWPAGE 0;
    SET SPACE 0;
    SET PAGESIZE 0;
    SET LONG 10000000;
    SET ECHO OFF;
    SET FEEDBACK OFF;
    SET HEADING OFF;
    SET TERMOUT OFF;
    SET VERIFY OFF;
    SET TRIMSPOOL ON;
    SET TAB OFF
    COL text FORMAT A2048
    
    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, ' ') || '|'    text
    FROM USER
    WHERE CODE = '&1';
    Another way to do this would be to use utl_file to write directly to a file. This requires having a database directory defined.
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2014
    Posts
    3
    Hi gandolf989,

    Thank you for the reply. I tried the formatted sql but there's still a problem. The clob column still contains multiple lines.
    I'm not sure if there is a special character or a new line at the end of the clob data that's causing it to move to the next
    line. But if a row has null clob entry, it displays a single line. Below is the result of my query:

    120005|0|10|10-OCT-13|Kat|100001|12-DEC-13|Tulmo|26-NOV-13|26-NOV-13|0| |D|password123|JASON_GALLARDO_01| | |US04|10|10-OCT-13|0|0|MINIKEY|MIICLjCCAZegAwIBAgIEN18U6zANBgkqhkiG9w0BAQQFADBSMScwJQYDVQQEEx5CYW5rX29mX3RoZV9QaGlsa XBwaW5lX0lzbGFuZHMxJzAlBgNVBAMTHkJhbmtfb2ZfdGhlX1BoaWxpcHBpbmVfSXNsYW5kczAeFw0wNzA3MjMwNTIzMTRaFw0xN zA3MjQwNTIzMTRaMGUxCzAJBgNVBAYTAlBIMQwwCgYDVQQKEwNCUEkxDjAMBgNVBAsTBUZJUlMzMRwwGgYDVQQEExNKQVNPTl9HQ UxMQVJET19URVNUMRowGAYDVQQDExFKQVNPTl9HQUxMQVJET18wMTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAp2ODdoLfZ kEqZ7G02kGB4E38sfls3PRGduBrwLjFHx/FnLNRajqXoljM3IPTZ/7igJABLH9XagZ+6pAwNr/ngebpmt5oz3lxwT4XZqCw6yng04bYqXIvIbQJy9PtqDb/Ie9YDyO90J0xusNxtQqxxD3ANjphwUQpzZbJmL2uZykCAwEAATANBgkqhkiG9w0BAQQFAAOBgQAMxeMDzM7i0IihWjvE5xai/rV5nrpQ6j3qjesG3bIPsuxctq0L2OEWyy2wg5lXXfxcxvPxArhky5B4XPLor7suxNm+0hMYFh1tHkgvyRHyVlHnY+d5ukzUUgxOu bAv9V8ns0SaDwMDwHhXjcQdW1jiGF4u9hdnFWwcERlvHcufpQ==
    | |
    200001|0|0|25-OCT-13|Kat|100001|28-JAN-14|DataAdminTU|07-JAN-14|07-JAN-14|0| |D|12345678| | | |US01|0|25-OCT-13|0|0| | | |

    Do you know what's causing this problem? Is there a solution to this?

    P.S. I tried to increase the value of the subtrahend from 1 to 5 and it displays a single row of data except for one. So I think there's a hidden characters that's causing it to move to the next line.
    Last edited by gandolf989; 01-30-2014 at 09:01 AM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    First try editing the file with either vim or Sql Developer, then if you are still having an issue try using utl_file to write the file.
    You will have a lot more control over writing the file with utl_file.


    https://www.google.com/search?q=11g+...m=122&ie=UTF-8
    this space intentionally left blank

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Perhaps you also need to add the "chunk size"?:
    Code:
    SET LONGC 32000;
    -- -----^
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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