--
-- INSERT.SQL
--
-- Peter Robson
-- December 2000
--
-- Use this script to insert one row at a
-- time into any Oracle table owned by the user.
--
-- The construct is a 2-level script, in which
-- the outer, Level-2 script queries variously 'dual' and
-- 'user_tab_columns', to create one large Level-1
-- script, which does the actually insertion work.
--
-- Each field will be prompted for input data. For a
-- null entry, simply enter <return>
--
-- Once the script has run, additional records can be
-- inserted by simply re-running the script now
-- lodged in the SQL buffer, eg enter '/' and <return>.
--
-- A useful modification would be to adapt the code
-- to enable input to a table owned by any other
-- schema (assuming appropriate permissions).
--
-- PGR, Dec 2000
--
set echo off
set sqlprompt ""
set trims on
set pagesize 0
--
accept table prompt ' Enter name of table to populate: '
--
-- creates header insert line
--
set feedback off
set verify off
set termout off
--
spool b.sql
--
select
'insert into &&table' ,
' (' from dual
/
--
-- generates list of attributes EXCEPT
-- for the very last one
--
select
column_name||' ,'
from user_tab_columns
where table_name = upper('&&table')
and column_id <
( select max(column_id)
from user_tab_columns
where table_name = upper('&&table'))
;
--
-- adds the last attribute to the declaration list
--
select
column_name
from user_tab_columns
where column_id =
( select max(column_id)
from user_tab_columns
where table_name = upper('&&table'))
and table_name = upper('&&table')
;
--
-- now inserts the 'values' syntax
--
select ') values (' from dual
/
--
-- generates prompt list for all attributes
-- EXCEPT the very last one
--
select
'''&'||column_name||''' ,'
from user_tab_columns
where table_name = upper('&&table')
and column_id <
( select max(column_id)
from user_tab_columns
where table_name = upper('&&table'))
;
--
-- generates the last attribute prompt
--
select
'''&'||column_name||''' )'|| chr(10) ||
'/ '
from user_tab_columns
where column_id =
( select max(column_id)
from user_tab_columns
where table_name = upper('&&table'))
and table_name =upper('&&table')
;
spool off
undefine table
--
set sqlprompt "SQL>"
set feedback on
--
set echo off
--
set termout on
@ b.sql
--
set heading on
set feedback on
set trims off
set pagesize 9999
set verify on
--
-- end
-- |
