Re: Creating Flat Files from Oracle Tables

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/15
Message-ID: <8iben9$dsf$1@nnrp1.deja.com>#1/1

In article <XX925.1389$7W2.12448_at_news1.wwck1.ri.home.com>, "Sally Madeira" <sallym1_at_home.com> wrote:
> Hi,
>
> I am looking for a way to create an export that sends data to a file
in
> either Ascii or Fixed
> Length. I do not want to have to use VB to do this (Of course if it
is the
> only way then I will
> have to live with that). Does anyone have any tricks that I could use
to do
> this whether through
> SQLPlus or someother oracle tools.
>
> Thanks in advance
> Sally
>
>

Sally,

You can accomplish this with SQL*Plus and the concatenation operator, ||. Let us take an example table named CURRENT_ORDERS with the following column definitions:

ORDER_NUM              NUMBER
CUST_NUM               VARCHAR2(12)
ITEM_NUM               VARCHAR2(10)
ITEM_DESC              VARCHAR2(40)
ITEM_PRICE             NUMBER(10,2)
QTY                    NUMBER
ORDER_DT               DATE
BACKORDER              VARCHAR2(1)
SHIP_DT                DATE

To create a flat file of delimited data the query would be:

set echo off feedback off verify off termout off head off pagesize 0 select

'"'||order_num||'","'||cust_num||'","'||item_num||'","'||item_desc||
'","'||item_price||'","'||qty||'","'||to_char(order_dt,'MM-DD-YYYY')||
'","'||backorder||'","'||to_char(ship_dt, 'MM-DD-YYYY')||'"'

from current_orders

spool curr_ords
/
spool off

set feedback on verify on termout on head on pagesize 60

The concatenation operator is placed wherever two fields, or fields and literal text, are to be joined. The '","' places a trailing quotation, a comma and a leading quotation between fields. The ' is the delimiter for literal text in SQL*Plus. The above query, then, will create a comma-separated, quotation delimited data file of all records in CURRENT_ORDERS:

"1","12","A43","Doggie Disk","4.95","300","12-12-1999","","12-17-1999"

All other records in the table would be output in the same format.

To create a file of fixed-length records a few modifications will be necessary to the above query:

set echo off feedback off verify off termout off head off pagesize 0

select

rpad(order_num,9)||rpad(cust_num,12)||rpad(item_num,10)||
rpad(item_desc,40)||rpad(item_price,10)||rpad(qty,9)||
rpad(to_char(order_dt, 'MM-DD-YYYY'), 10)||rpad(backorder,1)||
rpad(to_char(ship_dt,' MM-DD-YYYY'), 10)

from current_orders

spool curr_ords
/
spool off

The rpad function right pads spaces, by default, to a column to the maximum length specified. The order_num column will be 9 characters wide regardless of the length of the data; the rest of the definitions should be easily understood. The columns can also be made larger than defined by the column specification by increasing the numeric value to the rpad function; the item_desc column could be 80 characters wide by coding this:

rpad(item_desc, 80)

in place of the current code in the example:

rpad(item_desc, 40)

Notice, also, that the commas and quotation marks have been removed, since they are no longer necessary for a fixed-length data record. This record would be 111 characters long, fields starting at 1, 10, 22, 32, 72, 82, 91, 100 and 102. This data would look like:

1        12          A43       Doggie Disk
4.95      12-12-1999 12-17-1999

I hope this helps you.

David Fitzjarrell
Oracle Certified DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 15 2000 - 00:00:00 CDT