Very Computer

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?

Thanks,

Mark Tortolano

Hi Mark,

Yeah, you have to write a fiddly query like so:

-- Set up the environment to minimize editing of the output
SET HEAD OFF
SET PAGES 0
SET FEEDBACK OFF
SET COLSEP ','
SPOOL <file_name>.SQL

SELECT * FROM <table_name>;

SPOOL OFF

-- If you are a neatnik clean up the environment

SET COLSEP ' '
SET FEEDBACK ON
SET PAGES 24
SET HEAD ON

The output will be a nice comma delimited file all ready to load
into any RDBMS with a utility smart enough to handle a delimited file.

Regards

Jerry

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;

host del sletmig.sql
set termout on
------------------------ end insertgen.sql -----------------------

--

                         Standard disclaimers apply
          This posting was typed in front of a large studio audience