Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: extract data from tables as INSERT statements?

Re: extract data from tables as INSERT statements?

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 03 May 2002 00:41:28 GMT
Message-ID: <Y0lA8.4365$M7.1515269@twister.socal.rr.com>


First off, let me say that you're probably better off exporting the data only and using sql*loader when you need to load it. However, I made some changes to my unload.sql script that I think will do what you want. Use it at your own risk since there are some things it can't handle (like LONGs, LOBs, some embedded escape characters, etc...)

accept table_name default '&&table_name' prompt 'enter the table_name [&&table_name]: '
accept owner default '&&owner' prompt 'enter the owner [&&owner]: '

variable insert_header varchar2(4000);

begin

    :insert_header := 'insert into &&table_name (';

    for rec in (

        select column_name, column_id 
        from all_tab_columns
        where table_name = upper('&&table_name') 
            and owner = upper('&&owner')) loop

        if rec.column_id > 1 then
            :insert_header := :insert_header || ',' || rec.column_name;
        else :insert_header := :insert_header || rec.column_name;
        end if;

    end loop;

    :insert_header := :insert_header || ') values (';

end;
/

set feedback off
set timing off
set pagesize 0
set termout off
set linesize 32767

spool make_ins_script.sql

prompt select
select '''' || :insert_header || '''||' from dual;

select decode(column_id, 1, null, '||'',''||')

    || decode(data_type,

'DATE', ''' to_date('''''' || ' || column_name

            || ' || '''''',''''DD-MON-YYYY HH24:MI:SS'''') ''',

'CHAR', ''''''''' || replace(' || column_name
|| ', '''''''', '''''''''''') || ''''''''',
'VARCHAR2', ''''''''' || replace(' || column_name
|| ', '''''''', '''''''''''') || ''''''''', column_name) from all_tab_columns

where table_name = upper('&&table_name')

    and owner = upper('&&owner')
order by column_id;

prompt || ');';
prompt from &&table_name
prompt /

spool off

spool insert_script.sql
@make_ins_script.sql
spool off

Richard

Kenny Yu wrote:

> 
> This can be done in TOAD7.2 through clicks from 'Export Data' on a table.
> But I want to automate it into a script and run it regularly. Does anyone
> have a script for make_insert_stmt_from_table(p_table_name in varchar2)?
> 
> Kenny
Received on Thu May 02 2002 - 19:41:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US