mark_tortolano_at_dial.pipex.com (Mark Tortolano) writes:
>
> Hi there,
>
> I'm trying to dump the contents of a table, in order to create a
> series of SQL INSERT statements. I know the functionality of Export,
> but I just want to create INSERT statements, not an Oracle-specific
> binary.
>
> How do I do this? Is the only way to create a (fiddly) SQL*Plus
> report, using headings, separators, etc, or is there a better way?
Here's a (fiddly) SQL*Plus script that does what you want.
The quotes are messy but it works.
Lighter, mite.
--Anders.
- begin insertgen.sql -----------------------
-- Script to generate insert stmts from table data.
-- Anders Pedersen, RAMBOLL.
--
-- Known bugs:
-- If there are many fields in the table, either
-- the temp script or the final insert script may
-- have broken lines so it won't work.
-- Fix: increase linesize.
-- The script only handles NUMBER, DATE and text.
-- Everything else is treated as text.
-- No input validation.
-- Only works on owner's tables.
--
-- 17/1-97 ASP
--
prompt Insert-script generator.
prompt The data from the specified table will be
prompt written as insert statements
prompt to a script <tablename>_data.sql
accept tabelnavn prompt 'Table name: '
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 300
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
SET TRIMSPOOL ON
set termout off
spool sletmig.sql
select '--Temp. script from execution of Insertgen. Delete it.' from dual;
select 'set newpage 0' from dual;
select 'set space 0' from dual;
select 'set linesize 300' from dual;
select 'set pagesize 0' from dual;
select 'set echo off' from dual;
select 'set feedback off' from dual;
select 'set heading off' from dual;
select 'set verify off' from dual;
select 'spool &tabelnavn._data.sql' from dual;
select 'select ''insert into &tabelnavn (' from dual;
select decode(rownum, 1, ' ', ', ')||column_name from user_tab_columns
where table_name=upper('&tabelnavn');
select ')values(''' from dual;
select decode(rownum, 1, '|| ','||'', ''||')||
decode(data_type,
'NUMBER',
'NVL(TO_CHAR('||column_name||'), ''NULL'')',
'DATE',
'decode('||column_name||', NULL, ''NULL'', ''TO_DATE(''''''||TO_CHAR('||column_name||',''DDMMYYYYHH24MISS'')||'''''', ''''DDMMYYYYHH24MISS'''')'')',
/* VARCHAR2 etc. is default. */
'decode('||column_name||', NULL, ''NULL'',''''''''||replace('||column_name||', '''''''', '''''''''''')||'''''''')'
)
from user_tab_columns
where table_name=upper('&tabelnavn');
select '||'');'' from &tabelnavn;' from dual;
rem select 'select ''commit;'' from dual;' from dual;
select 'spool off' from dual;
spool off
@sletmig
host del sletmig.sql
set termout on
------------------------ end insertgen.sql -----------------------
--
asp_at_ramboll.dk Consultant Engineer, M.Sc.EE RAMBOLL, Denmark
Standard disclaimers apply
This posting was typed in front of a large studio audience
Received on Thu May 28 1998 - 04:16:04 CDT
