Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, annapoorani.

Asked: March 22, 2022 - 2:54 pm UTC

Last updated: March 25, 2022 - 2:58 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I am trying to export about 2 million records from a DB with 100 over columns in a particular format. Below is the format. Pipe between columns and double quote on each column and spool it to a txt file,

"column1"|"column2"|"column3"|"column100" for all the records.

I am using SQLPLUS and spooling data into a txt file.

code:
SELECT
'"'||column1||'"'||'|'||'"'||
column2||'"'||'|'||'"'||
column3||'"'||'|'||'"'||
column4||'"'||'|'||'"'||
column5||'"'||'|'||'"'||
column100||'"'
FROM table;

However below is a error I get,
ORA-01489: Result of String Concatenation is Too Long

Hence changed the data type of each column to a clob (clob(column2)||'"'||'|'||'"'||)and do the same and it is taking more than a day. I will need to export everything in 2-3 hours atleast.

Is there any way this can be done? I only have read access to this DB and I cannot execute a stored procedure. Other than SQLPLUS, can I use something else? Would a java program take the same amount of time? Can I export the data in batches instead using SQLPLUS and then merge all the files? and would that be faster?

and Connor said...

Simply setting the "markup" option in SQL Plus should solve this for, and speed things up.

Check out this vid for lots of options... and dont forget to subscribe :-)

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Still having issues with the space

Annapoorani, March 23, 2022 - 2:56 pm UTC

Hi Tom,

I did try using SET MARKUP CSV ON. However that produces a space between the columns.

eg.
column1 |column2 |column3 ....and so on

I do not want the space between them. This space is not coming from the DB. Its something produced by SQLPLUS. Moreover, I would want the flat file to be a text file.

March 25, 2022 - 2:58 am UTC

Its not coming from SQL Plus

SQL> set markup csv on delimiter '|'
SQL> select * from emp;

"EMPNO"|"ENAME"|"JOB"|"MGR"|"HIREDATE"|"SAL"|"COMM"|"DEPTNO"
7369|"SMITH"|"CLERK"|7902|"17-DEC-80"|800||20
7499|"ALLEN"|"SALESMAN"|7698|"20-FEB-81"|1600|300|30
7521|"WARD"|"SALESMAN"|7698|"22-FEB-81"|1250|500|30
7566|"JONES"|"MANAGER"|7839|"02-APR-81"|2975||20
7654|"MARTIN"|"SALESMAN"|7698|"28-SEP-81"|1250|1400|30
7698|"BLAKE"|"MANAGER"|7839|"01-MAY-81"|2850||30
7782|"CLARK"|"MANAGER"|7839|"09-JUN-81"|2450||10
7788|"SCOTT"|"ANALYST"|7566|"09-DEC-82"|3000||20
7839|"KING"|"PRESIDENT"||"17-NOV-81"|5000||10
7844|"TURNER"|"SALESMAN"|7698|"08-SEP-81"|1500||30
7876|"ADAMS"|"CLERK"|7788|"12-JAN-83"|1100||20
7900|"JAMES"|"CLERK"|7698|"03-DEC-81"|950||30
7902|"FORD"|"ANALYST"|7566|"03-DEC-81"|3000||20
7934|"MILLER"|"CLERK"|7782|"23-JAN-82"|1300||10

Annapoorani, March 23, 2022 - 2:58 pm UTC

Hi Tom,

I did try using SET MARKUP CSV ON. However that produces a space between the columns.

eg.
column1 | column2 | column3 ....and so on

I do not want the space between them. This space is not coming from the DB. Its something produced by SQLPLUS. Moreover, I would want the flat file to be a text file.

Annapoorani, March 23, 2022 - 3:00 pm UTC

when I try putting a space between the columns, it is not getting reflected. Hence going to use '*' instead of space...

eg.

column1********|********column2|*********column3 ....and so on

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Still having issues with the space

More to Explore

CLOB/BLOB/etc

SQLPLUS CLOB taking too long fetching 2 million records.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Still having issues with the space

More to Explore

CLOB/BLOB/etc