Re: No SQL - Unloader in Oracle 9i

Mark,

Could you volunteer yourself to download a template and pretty this up a little for the Co-operative FAQ.

Thanks.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Mark D Powell wrote in message
<178d2795.0106290534.18cced38_at_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 --