-
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!
-
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.
-
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 10:01 AM.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|