Very Computer

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

-Ethan
http://www.freetechnicaltraining.com/fldoracle.htm

Quote:> 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

Sent via Deja.com http://www.deja.com/
Before you buy.

Quote:> 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

Quote:> 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

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.

Toad, including the free version can do it, jsut do the query that selects the
approprite rows, then right click on the data grid select save as, then save
to a file as text or html table and pick the delimiter or give it you own
choice, you can also double quote text include null and column headers

you can also for smaller resultset save to the clipboard and then pste
directly into excel as tab delimited works great