Tables to Files

[eyetry]

I thought I had this working a while back but can't get it now.... I need to spool the contents of some tables into extract files. Problem I'm having is that I get either an 80 byte wrap (default line length) or truncated records.

----------------------------------------------------
set heading off
set echo off
set feedback off
set termout off
set wrap on
set newpage 0
set space 0
set pagesize 0
set colsep "|"

spool C:\export_temp\file_config_export.txt

select * from fnd_file_config_export;

spool off
spool C:\export_temp\eep_customer_set_export.txt

select * from fnd_eep_customer_set_export;

spool off

-------------------------------------------------

I've tried it without newpage, pagesize and space with wrap set on and off. doesn't seem to make a difference.

the exported record length will vary between tables but if I don't have to I don't want to set the 'linesize'. I also want to stay away from defining the columns.

Any ideas here?

 

[Turkbear]

Hi,
and your problem with setting linesize is?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Eyetry,

Do you want a flat file of comma-separated values (.csv) from your tables? If so, here is a script, "GenASCII.sql" that I use:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept w prompt "Enter the Oracle username that owns the source table: "
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"'''
  from all_tab_columns
 where owner = upper('&w')
   and table_name = upper('&x');
prompt from &w..&x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Here is a sample invocation of GenASCII.sql:

SQL> @genascii
Enter the Oracle username that owns the source table: test
Enter the table to ASCII: s_dept
Enter the flat file to write: dept.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool dept.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
||',"'||REGION_ID|| '"'
from test.s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "dept.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"
"99","Extra","1"

Output file = "dept.txt"

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

Turkbear I want the script to dynamically deterimine the record length of the row being extracted. So if rows from table A are 16498 bytes, byte 16499 is a newline. The same for able 'B' if it's only 422 bytes.

SantaMufasa, I know I can write the xtract similar to the way you've displayed but this is a one off and I didn't really want to get into defining each column. Several of the tables are 10s of columns long, one is 100s of cols.
(Already told the users this would probably take less time to re-enter manually into new DB than it will take to write all the scripts. Plus manually addresses potential issues that the update wouldn't. Only reason I haven't appealed the request is because I'm not an Oracle guy and want to pick more of it up.)

I had hoped to define line size on the fly. It'll be easier to add/change tables in the future if I ever need to do this again. Really only want this to be a 15 - 30 minute setup...

set heading off
set echo off
set feedback off
set pagesize 0
set colsep "|"
set linesize 16498
spool C:\table_a.txt
select * from table_a;
spool off
set linesize 422
spool C:\table_b.txt
select * from table_b;
spool off

 

[LKBrwnDBA]

You only need to indicate the 'maximum' line size and use the TRIMS ON option to 'trim' output:

set head off echo off feed off colsep "|"
set pages 0 lin 16498 trims on
spool C:\table_a.txt
select *, '|' from table_a;
spool off
spool C:\table_b.txt
select *, '|' from table_b;
spool off

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

I didn't really want to get into defining each column.

Perhaps you didn't catch the drift of the script I posted, but you don't have to define any columns...the script DYNAMICALLY does it all for you. It automatically sets up the column output; it outputs up to 32K per line, but actually outputs only the number of characters that exist on each row.


All you have to do is run the script from SQL*Plus, enter the name of the owner, the table, and the output flat file. I don't think it gets much easier than that.

Try it...you'll like it !

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Besides, the script provided by Santamufasa will eliminate the excess "whitespace" by trimming each column value to the actual value length (no trailing spaces).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[eyetry]

Thanks, working now