Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: extract data from tables as INSERT statements?
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
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)? > > KennyReceived on Thu May 02 2002 - 19:41:28 CDT