Home » SQL & PL/SQL » SQL & PL/SQL » Export table data into text file through procedure/package (8 merged) (Oracle 10g)
Export table data into text file through procedure/package (8 merged) [message #567877] Mon, 08 October 2012 02:21 Go to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi all,

I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?

Please noted that the data does exist 32k.

Thank you.

[Updated on: Mon, 08 October 2012 03:16] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #567878 is a reply to message #567877] Mon, 08 October 2012 02:26 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

you can use UTL_FILE it will be better..
Re: Export table data into text file through procedure/package [message #567886 is a reply to message #567878] Mon, 08 October 2012 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nope! "spool" will be better.

Or even better, all methods at: How Can I unload data to a flat file

Regards
Michel

Re: Export table data into text file through procedure/package [message #567901 is a reply to message #567886] Mon, 08 October 2012 04:40 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi muralikri,
Using DBMS_LOB will not result a better performance?
If using UTL_FILE, I need to check and write every 32k right?




Hi Michel,
Is it possible that I just spool the table data part?



Thank you
Re: Export table data into text file through procedure/package [message #567903 is a reply to message #567901] Mon, 08 October 2012 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible that I just spool the table data part?


What does than mean?

Regards
Michel
Re: Export table data into text file through procedure/package [message #567904 is a reply to message #567903] Mon, 08 October 2012 04:47 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

Do you have any reference on how spool can be done?


Thank you.
Re: Export table data into text file through procedure/package [message #567906 is a reply to message #567904] Mon, 08 October 2012 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spool myfile
select * from emp;
spool off

Regards
Michel
Re: Export table data into text file through procedure/package [message #567907 is a reply to message #567906] Mon, 08 October 2012 04:55 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
thank you Michel...
Re: Export table data into text file through procedure/package [message #567908 is a reply to message #567907] Mon, 08 October 2012 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But there are better ways in the link I posted.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568023 is a reply to message #567908] Tue, 09 October 2012 03:28 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

I just need to copy and paste the unload script into a text file and run it? Does the script same for windows and unix platform?

Thank you
Re: Export table data into text file through procedure/package [message #568025 is a reply to message #568023] Tue, 09 October 2012 03:32 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Which platform have you tried?
Re: Export table data into text file through procedure/package [message #568030 is a reply to message #568025] Tue, 09 October 2012 03:44 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
muralikri wrote on Tue, 09 October 2012 16:32
Which platform have you tried?


Hi Muralikri,

I tried in unix with this command: sqlldr_exp username/servicename tablename > tablename.ctl
But I get error "-bash: sqlldr_exp: command not found"


Please advice.
Re: Export table data into text file through procedure/package [message #568033 is a reply to message #568023] Tue, 09 October 2012 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I posted is (an extract of) a SQL*Plus script, it does not care about the platform.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568038 is a reply to message #568033] Tue, 09 October 2012 04:02 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Michel Cadot wrote on Tue, 09 October 2012 16:46
What I posted is (an extract of) a SQL*Plus script, it does not care about the platform.

Regards
Michel


Hi Michel,

I am a bit lost. You refering to the spool off script right?

Re: Export table data into text file through procedure/package [message #568043 is a reply to message #568038] Tue, 09 October 2012 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568048 is a reply to message #568043] Tue, 09 October 2012 04:54 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

But the result in retrieving 3,000,000 records is slow if compared with the code below:

SET SERVEROUTPUT ON

DECLARE
	v_file UTL_FILE.FILE_TYPE;
	v_buffer  VARCHAR2(32767);
	v_name    VARCHAR2(128) := 'utl_file_buffered.txt';
	v_lines   PLS_INTEGER := 0;
	c_eol     CONSTANT VARCHAR2(1) := CHR(10);
	c_eollen  CONSTANT PLS_INTEGER := LENGTH(c_eol);
	c_maxline CONSTANT PLS_INTEGER := 32767;
BEGIN
	v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767);

	FOR r IN (SELECT u || ',' || v || ',' || w || ',' || x || ',' || y || ',' || z as csv FROM tbl1)
	LOOP
		IF LENGTH(v_buffer) + c_eollen + LENGTH(r.csv) <= c_maxline THEN
			v_buffer := v_buffer || c_eol || r.csv;
		ELSE
			IF v_buffer IS NOT NULL THEN
				UTL_FILE.PUT_LINE(v_file, v_buffer);
			END IF;
			v_buffer := r.csv;
		END IF;
 
 		v_lines := v_lines + 1;

	END LOOP;

	UTL_FILE.PUT_LINE(v_file, v_buffer);
	UTL_FILE.FCLOSE(v_file);

	DBMS_OUTPUT.PUT_LINE('File='||v_name||'; Lines='||v_lines);

END;
/



Any advice?

[Updated on: Wed, 10 October 2012 04:28] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568052 is a reply to message #568048] Tue, 09 October 2012 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any advice?


Read the link I provided.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568153 is a reply to message #568052] Tue, 09 October 2012 19:57 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

I tried in unix with this command: sqlldr_exp username/servicename tablename > tablename.ctl
But I get error "-bash: sqlldr_exp: command not found" Sad

[Updated on: Wed, 10 October 2012 04:28] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568154 is a reply to message #568153] Tue, 09 October 2012 20:29 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
place the following lines in a file called unload.sql

set colsep ,
spool unload.csv
select * from t11;
spool off
exit

then on the command line from the same directory where unload.sql resides invoke the line below

sqlplus <username>/<password> @unload.sql

COPY the whole session then PASTE all back here

[Updated on: Tue, 09 October 2012 20:29]

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568164 is a reply to message #568154] Wed, 10 October 2012 00:42 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi BlackSwan,

Below is the error I get after I run the @unload.sql


3000000 rows selected.

SP2-0308: cannot close spool file
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production


Thank you.

[Updated on: Wed, 10 October 2012 04:29] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568172 is a reply to message #568164] Wed, 10 October 2012 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste:
- the content of the script (cat/type unload.sql)
- the WHOLE sqlplus session starting from @unload.sql (except the result of the query); before add 'set echo on' on top of the file

Regards
Michel
Re: Export table data into text file through procedure/package [message #568182 is a reply to message #568172] Wed, 10 October 2012 02:23 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

1) This is the content of the script

set colsep ','
spool unload.csv
 select * from tbl_1;
spool off
exit
/


2) The WHOLE sqlplus session? The result already overwrite the @unload.sql part.
Can set not do display the result in sqlplus screen?


Sorry for inconvenience

[Updated on: Wed, 10 October 2012 04:29] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568184 is a reply to message #568182] Wed, 10 October 2012 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is the content of the script


I don't want you say what is the content, I want you execute a cat/type command and copy and paste the result here.

Quote:
Can set not do display the result in sqlplus screen?


What does that mean?

Regards
Michel

Re: Export table data into text file through procedure/package [message #568185 is a reply to message #568184] Wed, 10 October 2012 02:35 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member

1) What is a cat/type command? Sad
2) After I run @unload.sql, all the data in table will be printed in screen. Since you need the WHOLE sqlplus session starting from @unload.sql, if the table data are printed in the screen, then how can I get the text starting from @unloadl.sql?


Please advice.

[Updated on: Wed, 10 October 2012 04:30] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568188 is a reply to message #568185] Wed, 10 October 2012 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) How do you see the content of a file on the commande line?
2) Use "set autotrace traceonly statistics":
SQL> set autotrace traceonly statistics
SQL> select * from dual;

1 row selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

(and ignore the statistics part.)

Regards
Michel
Re: Export table data into text file through procedure/package [message #568192 is a reply to message #568188] Wed, 10 October 2012 03:13 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
1)
[xyz@xyz-training ~]$ cat unload.sql
set colsep ','
spool unload.csv
 select * from tbl_1;
spool off
exit
/[xyz@xyz-training ~]$


2)
[xyz@xyz-training ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 10 09:11:27 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn username@xe
Enter password:
Connected.
SQL> @unload.sql

3000000 rows selected.


Statistics
----------------------------------------------------------
        198  recursive calls
          0  db block gets
     237715  consistent gets
      40253  physical reads
          0  redo size
  217141868  bytes sent via SQL*Net to client
    2200374  bytes received via SQL*Net from client
     200001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    3000000  rows processed

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[xyz@xyz-training ~]$



I am not sure I am giving what you want Embarassed

[Updated on: Wed, 10 October 2012 04:30] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568193 is a reply to message #568192] Wed, 10 October 2012 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have (almost) given what I wanted (you forgot the "set echo on") and I can see there is no more error on "spool off" and you should have the file.
Now remove the trace part and you should have your result.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568194 is a reply to message #568193] Wed, 10 October 2012 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For information, see T. Kyte's latest comment on unload methods:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5526689500346450959

Regards
Michel
Re: Export table data into text file through procedure/package [message #568200 is a reply to message #568193] Wed, 10 October 2012 04:17 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member

Hi Michel, is this what you expecting?

[xyz@xyz-training ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 10 10:13:54 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn username@xe
Enter password:
Connected.
SQL> @unload.sql
SQL> set autotrace traceonly statistics
SQL>
SQL> set colsep ','
SQL> spool unload.csv
SQL>  select * from tbl_1;

3000000 rows selected.


Statistics
----------------------------------------------------------
        534  recursive calls
          0  db block gets
     237766  consistent gets
      40261  physical reads
          0  redo size
  217141868  bytes sent via SQL*Net to client
    2200374  bytes received via SQL*Net from client
     200001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    3000000  rows processed

SQL> spool off
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[xyz@xyz-training ~]$


[Updated on: Wed, 10 October 2012 04:30] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568206 is a reply to message #568200] Wed, 10 October 2012 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to repost it.
I already gives you my conclusion:

Quote:
I can see there is no more error on "spool off" and you should have the file.
Now remove the trace part and you should have your result.


Note there is no need to quote my whole post in your post (I will remove them), we are the only 2 ones to post there is no problem to follow the topic.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568209 is a reply to message #568206] Wed, 10 October 2012 04:33 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

This below is the unload.csv file content, but that is not I want Sad

[xyz@xyz-training ~]$ cat unload.csv
SQL>  select * from tbl_1;

3000000 rows selected.


Statistics
----------------------------------------------------------
        534  recursive calls
          0  db block gets
     237766  consistent gets
      40261  physical reads
          0  redo size
  217141868  bytes sent via SQL*Net to client
    2200374  bytes received via SQL*Net from client
     200001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    3000000  rows processed

SQL> spool off
[xyz@xyz-training ~]$
Re: Export table data into text file through procedure/package [message #568210 is a reply to message #568209] Wed, 10 October 2012 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you understand what I said:

Quote:
Now remove the trace part and you should have your result.


Regards
Michel
Re: Export table data into text file through procedure/package [message #568213 is a reply to message #568210] Wed, 10 October 2012 04:44 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

Just remove "set autotrace traceonly statistics"? How about "set echo on"?

Please forgive me for the mistake.
Re: Export table data into text file through procedure/package [message #568215 is a reply to message #568213] Wed, 10 October 2012 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and also. They were only there to check if all happen well.

Regards
Michel

[Updated on: Wed, 10 October 2012 04:57]

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568331 is a reply to message #568215] Wed, 10 October 2012 19:46 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member
Hi Michel,

I removed both "set autotrace traceonly statistics" and "set echo on" and run @unload.sql again.

But I still get the below error message at the end:

3000000 rows selected.

SP2-0308: cannot close spool file
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[xyz@xyz-training ~]$


Is it the records too big till cannot write into csv file?


Thank you.
Re: Export table data into text file through procedure/package [message #568333 is a reply to message #568331] Wed, 10 October 2012 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
Does another session hold the file open?

[oracle@localhost bin]$ oerr sp2 308
00308,0, "cannot close spool file\n"
// *Cause:  The file is currently being used.
// *Action: Release the file from the other process.
Re: Export table data into text file through procedure/package [message #568334 is a reply to message #568333] Wed, 10 October 2012 21:27 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member

Only one session is used to run the @unload.sql which will create and write to the file. Sad

[Updated on: Thu, 11 October 2012 01:36] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568348 is a reply to message #568334] Thu, 11 October 2012 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it the records too big till cannot write into csv file?


What is the total size? In some OS the file cannot exceed 2GB.

Regards
Michel
Re: Export table data into text file through procedure/package [message #568367 is a reply to message #568348] Thu, 11 October 2012 03:08 Go to previous messageGo to next message
ashly
Messages: 23
Registered: October 2012
Junior Member

The file size stop growing right after approximately 130MB. Sad

[Updated on: Thu, 11 October 2012 03:11] by Moderator

Report message to a moderator

Re: Export table data into text file through procedure/package [message #568369 is a reply to message #568367] Thu, 11 October 2012 03:10 Go to previous messageGo to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, do NOT quote the previous whole post.

And what is the total size of your data?

Regards
Michel
Previous Topic: cannot use variable in PL/SQL code for Timezone
Next Topic: Function very slow after Oracle upgrade
Goto Forum:
  


Current Time: Mon Sep 01 17:33:26 CDT 2014

Total time taken to generate the page: 0.12016 seconds