Extracting data from Oracle tables

Extracting data from Oracle tables

Post by Neville Grill » Wed, 21 Aug 1996 04:00:00

A simple utility I wrote to extract data from an Oracle table as a
series of
insert into ... values ... statements .

Any comments or better ideas out there?


        extract.sql : Neville Grills  3.8.96
                SQL Utility to extract rows from any oracle table
                as a file of SQL insert into ... values (..) statements.
        Input : 1. table_name
                2. owner
                3. where statement
        Output: File of insert statements for use in SQL*Plus
                file name of e<table_name>.sql
        Requires: Read access to the table and write access to the
                  current working directory.
        NB. may need to SET SCAN OFF before loading the extracted file
            to overcome embedded ampersands in the data.                

                Produces the file ./edept.sql
                Note the double quotes around text data

set serveroutput ON size 999999
set line 200
set hea off
set verify off
set feed off
spool x.sql
cursor atabs is
        data_type ,  column_name
        from all_tab_columns where table_name = upper('&1') and
        order by column_id;
scol varchar2(200);
stemp varchar2(200);
stempbuff varchar2(200) := 'DO NOT PRINT';
num_data varchar2(9) := 'NUM'||'BER';
q varchar2(4) := '''||''';
q1 varchar2(1) := '''';
cc varchar2(2) := '||';
qc varchar2(3) := ''',''';
dbms_output.put_line ( 'select ''insert into &1 (''|| ');
        for x in atabs loop
                begin   stemp := x.column_name;
                        if stempbuff <> 'DO NOT PRINT' then
                                dbms_output.put_line ( 'chr(10)||'||q1||stempbuff||q1||cc||qc||cc );
                        end if;
                        stempbuff := stemp;
                        stemp := NULL;
        end loop;
        dbms_output.put_line ( q1||stempbuff||q1||cc||q1|| ') '||q1||cc );
dbms_output.put_line('chr(10)|| '' values ('' ||' );
        stempbuff := NULL;
        for x in atabs loop
                        if x.data_type = num_data then
                                stemp := 'nvl(to_char('||x.column_name||'),''NULL'')';
                        elsif x.data_type = 'VARCHAR2' then
                                stemp := q1||q1||q1||q1||cc||x.column_name||cc||q1||q1||q1||q1;
                        elsif x.data_type = 'DATE' then
                                stemp := '''to_date('''||cc||q1||q1||q1||q1||cc||'to_char('||
''DD-MM-YYYY HH24:MI:SS'' '||cc||q1||q1||q1||q1||cc||' '' )'' ';
                        end if;
                        if stempbuff <> 'DO NOT PRINT' then
                                dbms_output.put_line ( 'chr(10)||'||stempbuff||cc||qc||cc||''' --
'||scol||''''||'||' );
                        end if;
                        scol := x.column_name;
                        stempbuff := stemp;
                        stemp := NULL;
        end loop;
        dbms_output.put_line ( 'chr(10)||'||stempbuff||cc||q1||
');'||q1||cc||'chr(10)||'||''' -- '||scol||'''' );
        dbms_output.put_line ( 'from '||'&2'||'.'||'&1'||' '||'&3'||';' );

spool off
set pages 0
set line 150
set termout off

spool off
set termout on
prompt SQL spooled to e&1..sql in current working directory
prompt Finished


1. Extract table data to file in ascii format

Is there an Oracle utility to extract the rows of a table into an external
file in ASCII format?

I can just spool the results of a query end edit the resulting file, but
I was wondering if there is an inverse of SQL*Loader.


CSIRO Division of Information Technology
PO Box 664                                      tel: +61 6 275 0960
Canberra ACT 2601 AUSTRALIA.                    fax: +61 6 257 1052

2. UUPC-Info Digest 1996 #16

3. Extracting data from tables

4. Sendmail gateway sending to multiple hosts

5. What's quicker, table to table insert or extract and sqlload?

6. should I upgrade to Acrobat v.5?

7. Extracting Data From Oracle to Access across TCP/IP

8. Ada to C

9. Extracting data from VSAM files to Oracle database

10. Extracting Binary Data From Oracle

11. Extracting data from Oracle 2.1

12. Extracting clob length data from an xml attribute using oracle xml packages

13. Oracle Extract or put entire table