Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export Data 2

Status
Not open for further replies.
gregsimpson

gregsimpson

Programmer
Apr 18, 2002
521
Hi,

moving across from the DB2 forum to the Oracle forum, so apologies for starting failry high level.

I have requirement to export data from a number of Oracle tables under 9i. The requirement basically splits into 3, some tables need exporting in CSV, some need exporting in fixed width and finally some tables are being picked up by 2 customers, as such one requires CSV, the other fixed width. Can anyone point me in the right direction, as regards the ways Oracle provides for me to take a flat file copy of this data.

Cheers
Greg
 
Sort by date Sort by votes
Look at the SQL*Loader utility. This can produce flat files in any format you want.
 
Upvote 0 Downvote
Sorry, ignore that. I was thinking you were importing the files. Options are:

a) spool data using sql*plus by writing a script to select data in the format you want (e.g. to produce a flat file in comma-delimited format select col1||','||col2||','...

b) use the utl_file package to write to the output file.
 
Upvote 0 Downvote
Greg, in addition to the great advice from Dagon and Simon, here are output samples and scripts that I use for both Comma-Separated-Values (.csv) flat-file generation and fixed-length flat-file generation:

Section 1 -- a DESCRIBE of the DEPT table:
Code: 
SQL> desc dept
 Name              Null?    Type
 ----------------- -------- ------------
 ID                NOT NULL NUMBER(7)
 NAME              NOT NULL VARCHAR2(25)
 REGION_ID                  NUMBER(7)
 DATE_CREATED               DATE

Section 2 -- Comma-Separated-Value flat-file output for the DEPT table:
Code: 
"10","Finance","1","20-SEP-04"
"31","Sales","1","11-OCT-04"
"32","Sales","2","12-OCT-04"
"33","Sales","3","13-OCT-04"
"34","Sales","4","14-OCT-04"
"35","Sales","5","15-OCT-04"
"41","Operations","1","21-OCT-04"
"42","Operations","2","22-OCT-04"
"43","Operations","3","23-OCT-04"
"44","Operations","4","24-OCT-04"
"45","Operations","5","25-OCT-04"
"50","Administration","1","30-OCT-04"

Section 3 -- Fixed-Length flat-file output for the DEPT table:
Code: 
      10Finance                         12004/09/20
      31Sales                           12004/10/11
      32Sales                           22004/10/12
      33Sales                           32004/10/13
      34Sales                           42004/10/14
      35Sales                           52004/10/15
      41Operations                      12004/10/21
      42Operations                      22004/10/22
      43Operations                      32004/10/23
      44Operations                      42004/10/24
      45Operations                      52004/10/25
      50Administration                  12004/10/30
Note 1: You must save the contents of the two following scripts to disk and execute the scripts from your SQL*Plus prompt (format: "SQL> @<script_name>") since both scripts prompt for keyboard input. You cannot successfully execute the code via a copy-and-paste from here directly to your SQL*Plus prompt because of the "ACCEPT" commands.)

Note 2: I have artificially widened the display of this thread to avoid unsightly/otherwise confusing line wrapping on longer lines of code.

Section 4 -- Script that generates .csv flat-files:
Code: 
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 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 user_tab_columns
 where table_name = upper('&x');
prompt from &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
Section 5 -- Script that generates fixed-length flat-files:
Code: 
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 **************************************************************
@GetColumns
spool Temp.sql
prompt prompt
prompt prompt Writing file "&schemaname..&tabname..txt" to the default path. Please wait...
prompt prompt
prompt set termout off
prompt set pagesize 0
prompt set feedback off
prompt spool &schemaname..&tabname..txt
prompt Select
select     decode(column_id,1,null,'||')
   ||decode(substr(data_type,1,4)
           ,'NUMB','to_char('||column_name ||','''||
                   lpad('9',nvl(data_precision,38)-nvl(data_scale,0),'9') ||
                   decode(nvl(data_scale,0),0,null,'.'||lpad('9',data_scale,'9'))||
                   chr(39)||')'
           ,'DATE','to_char('||column_name||',''yyyy/mm/dd'')'
           ,'LONG','rpad('||chr(39)||'Cannot output LONG column, "'||column_name||
                   '".'||chr(39)||','||to_char(length(column_name)+31)||')'
           ,'rpad('||column_name||','||trim(to_char(data_length,'9999'))||')'
           )
from tab_columns
order by column_id
/
prompt from &schemaname..&tabname;;
prompt spool off
prompt set termout on
prompt set pagesize 0
prompt set feedback on
spool off
prompt
prompt Wrote 'temp.sql' to the default path. To execute: 'SQL> @temp'
prompt
set heading on
set pagesize 23
set feedback on
REM @temp
Section 6 -- Sample execution of .csv-generating script (which I called, "GENASCII.sql"):
Code: 
SQL> @genascii
Enter the table to ASCII: dept
Enter the flat file to write: test.dept.csv.txt

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

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

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

"10","Finance","1","20-SEP-04"
"31","Sales","1","11-OCT-04"
"32","Sales","2","12-OCT-04"
"33","Sales","3","13-OCT-04"
"34","Sales","4","14-OCT-04"
"35","Sales","5","15-OCT-04"
"41","Operations","1","21-OCT-04"
"42","Operations","2","22-OCT-04"
"43","Operations","3","23-OCT-04"
"44","Operations","4","24-OCT-04"
"45","Operations","5","25-OCT-04"
"50","Administration","1","30-OCT-04"

Output file = "test.dept.csv.txt"
Section 7 -- Sample execution of fixed-length/flat-file-generating script (which I called, "FIXLEN.sql"):
Code: 
SQL> @fixlen


Enter Oracle owner of table: test
Enter table name: dept


 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ID                                        NOT NULL NUMBER(7)
 NAME                                      NOT NULL VARCHAR2(25)
 REGION_ID                                          NUMBER(7)
 DATE_CREATED                                       DATE

prompt
prompt Writing file "test.dept.txt" to the default path. Please wait...
prompt
set termout off
set pagesize 0
set feedback off
spool test.dept.txt
Select
to_char(ID,'9999999')
||rpad(NAME,25)
||to_char(REGION_ID,'9999999')
||to_char(DATE_CREATED,'yyyy/mm/dd')
from test.dept;
spool off
set termout on
set pagesize 0
set feedback on

Wrote 'temp.sql' to the default path. To execute: 'SQL> @temp'

SQL> @temp

Writing file "test.dept.txt" to the default path. Please wait...

SQL>
For the fixed-length-column generation, the script takes its fixed output lengths from the data dictionary's maximum-column-length specifications from the respective columns, except for LONG (not allowed) and DATE, which uses the "YYYY/MM/DD" format.

Let us know if this information helps resolve your needs.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Santa,

seeing as how you're doing great in the purple star department, I hate to have to do this, but what a great posting. All that effort, dished up for free.

Thanks for being so willing to share.

Tharg.

Grinding away at things Oracular
 
Upvote 0 Downvote
Tharg, as always, you are very kind. And no matter how many Purple Stars I might ever have, I always sincerely appreciate the thoughtfulness of the awarder. I'm glad that you found the scripts valuable.

Cheers, [cheers]

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
SantaMufasa,

what can I say, other than I'll post on here again. I've answered loads of posts on the db2 tek-tips forum in the past, but must admit at times, I've left it scant thinking it's not that hard, they can google the rest. However coming from last week db2 (for the last 18 years), to this week Oracle and needing a leg up, this post is fantastic and I appreciate the extra 10 minutes can make a big difference to someone else.

I can't thank you enough, but suffice to say you've earned your star.

Cheers
Greg
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Locked
  • Question
Oracle Data Validation 1
Replies
4
Views
251
TheLazyPig
TheLazyPig
rk68
  • Locked
  • Question
inserting multi lingual data
Replies
0
Views
148
rk68
rk68
EM1107
  • Locked
  • Question
How to convert CLOB data into number and compare it to other table.
Replies
4
Views
654
carp
carp
Andrzejek
  • Locked
  • Question
Show value from DATA_DEFAULT field 1
Replies
2
Views
901
Andrzejek
Andrzejek
hmckillop
  • Locked
  • Question
Exporting specific data from DB into another DB
Replies
1
Views
154
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top