Re: No SQL - Unloader in Oracle 9i

From: Mark D Powell <mark.powell_at_eds.com>
Date: 29 Jun 2001 06:34:07 -0700
Message-ID: <178d2795.0106290534.18cced38@posting.google.com>

Mark Townsend <markbtownsend_at_home.com> wrote in message news:<B7615758.2E2D%markbtownsend_at_home.com>...
> in article 20010629003225.02486.00001439_at_ng-mj1.aol.com, Uchakra at
> uchakra_at_aol.com wrote on 6/28/01 9:32 PM:
>
> > It will be helpful.
>
> Well - have a look at DBMS_METADATA in the docs - it won't unload the data,
> but it will give you the DDL. See
> http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88894/ad
> x13met.htm

I for one, do not think XML is all that great of an idea. It reminds me of a long winded version of EDI.

Nevertheless back to the main point of this thread, better data unloading. Yes it would be nice if Oracle provided a data unloader utility, but you can use SQL to mimic some of its desired features such creating comma delimited output. I lost a better version but here is some SQL to generate a CSV file:

In sqlplus you just spool the resuts of the select to a file. You can use the following sqlplus commands to format the file for use:

set pagesize 0      <== eliminate headings, pagebreaks
set linesize 133    <== adjust to write entire row to one line
set feedback off    <== eliminate number of rows processed msg
set echo off        <== eliminate sql from showing in result set
set verify off      <== eliminate substitution var from showing

To create a delimited file you just concatenate all the columns together something like col1||','||col2||',' etc....

You can generate the sql to create the delimited file with code like this:
set pagesize 0
set feedback off
spool george3.sql
select column_name||'||'',''||'
from sys.dba_tab_columns
where table_name = 'ITEM_MASTER'
/
spool off

Which will give you a file that looks something like:

X,,,,,,,,,,,,,,,,,,,1,,,,,EZ SPEC LH ROT PORT PLEASURE

MARINE,,,,N,N,,,80140 set echo off
rem
rem SQL*Plus script to generate SQL to create comma delimeted file rem for a specified table.
rem
rem 20000511 Mark D Powell New script for common request. rem
set pagesize 0
set verify off
set feedback off
spool csv2.sql
select 'select ' from sys.dual;
select decode(column_id,1,column_name,

              '||'',''||'||
              decode(data_type,'VARCHAR2','''"''||'||column_name||'||''"''',
                    column_name))

from sys.dba_tab_columns
where table_name = upper('&tblname')
and owner = upper('&owner')
order by column_id;
select 'from &&owner..&&tblname;'
from sys.dual;
spool off

• Mark D Powell --

Received on Fri Jun 29 2001 - 08:34:07 CDT