Subject:            RETRIEVING DATA FROM THE DATABASE
Author:             RADRANLY
Last Revision Date: 10 September 1996



                        Retrieving Data from the Database

This bulletin covers two methods for retrieving data in the database for
reloading.  The first, is a script that will generate insert statements for
an existing table. The second, is a script that once executed will
generate a control file that can be used with sql*loader as well as spools
the data to a file to be used by the control file.  Both scripts do all the
work automatically, you simply need to provide a table name.  This
information has been provided to Oracle Worldwide Support by a customer,
Ramesh K Meda.

Preliminatry testing was done on these scripts, but we urge all users to conduct
tests in their environment.  The end user is solely responsible for results of
the execution of these scripts.

SCRIPT TO GENERATE INSERT STATEMENTS:
------------------------------------

/*
|| File:
|| Dump.SQL
|| Description:
|| Creates insert statements for existing data
|| Author:
|| Ramesh K Meda.
||
*/

set pages 0
set lines 132
set verify off
set feedback off

accept sTab prompt 'Enter table name: '

column MaxColId noprint new_val sMaxColId

select max(column_id) MaxColId
from   user_tab_columns
where  table_name = upper('&sTab')
/

spool junk.sql
prompt Select
select decode(column_id, 1, '''insert into &sTab. Values ('' || chr(10) || ')
    || decode(column_id, 1, '', ' || ')
    || decode(column_id, 1, '', ''','' ||')
    || 'decode(' || column_name || ', null, ''Null'',' || '''''''''|| ' ||
       column_name || ' || '''''''''  || ')'
    || '|| chr(10)'
    || decode(column_id, &sMaxColId, ' || '')'' || chr(10) || ''/'' ', null)
from   user_tab_columns
where  table_name = upper('&sTab')
order  by column_id
/
Prompt from &sTab.;
Prompt /
spool off

spool &sTab..dat
@junk.sql
spool off
Prompt Output spooled to &sTab..dat

SCRIPT TO GENERATE CONTROL FILE AND DATA FILE FOR USE WITH SQL*LOADER:
---------------------------------------------------------------------

/*
||
||  Description:
||    Given a tablename generates
||        1. Flat ascii file with delimiters
||        2. SQL*Load control file to load the data
||
*/

set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause off

accept vtbl_name prompt 'Enter table name: '

define ColumnDelim = "|"
define DateFormat = "yyyymmddhhmiss"

define dataname = &&vtbl_name..dat

spool data.sql

prompt ALTER SESSION SET NLS_DATE_FORMAT="&DateFormat.";;
--
-- Header info
--
prompt /*
prompt ||  Script Name:  data.sql
prompt ||  Author:  Ramesh K Meda
prompt ||  Date:  Feb 1995 (Does day matter?)
prompt ||  Copyright info: Feel free to copy!
prompt ||  Fees:  As you please!
prompt */

prompt clear columns

--
--  Set up column formats
--

select 'column ' || column_name || ' format ' ||
       DECODE (data_type,
             'DATE', 'A14'
            ,'NUMBER' ,
              decode (data_scale, 0,
              rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
              , rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
             || '.' ||
              rpad('0', nvl(data_scale, 5), '0'))
            ,'A' || data_length
               )
from user_tab_columns
where table_name = UPPER('&&vtbl_name')
order by column_id;

--
-- Set Line size to export data
--
select 'set linesize '||
       sum(DECODE(data_type
                    ,'DATE', 25
                    ,'NUMBER', nvl(data_precision,45) + 5
                    , data_length + 5
                 )
          )
from  user_tab_columns
where table_name = UPPER('&&vtbl_name');

prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout off

prompt spool &dataname

prompt select
select decode(column_id, 1, '  ', ',')
,      column_name
,      ',''&ColumnDelim.'''
from   user_tab_columns
where  table_name = upper('&&vtbl_name')
order  by column_id
/
prompt from &&vtbl_name;;

prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout on

spool off

--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctl

prompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (

select  decode (column_id, 1, ' ', ',')                     ||
 column_name                                                ||
 chr(9)                                                     ||
 decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
 chr(9)                                                     ||
 ' terminated by ''&ColumnDelim.'' '                        ||
 ' nullif '                                                 ||
 column_name                                                ||
 ' = blanks '
from    user_tab_columns
where   table_name = upper('&&vtbl_name')
order   by column_id
/

prompt )

spool off

clear scree
prompt Generating data file

@data.sql

prompt  Files generated:
prompt  SQL script:     data.sql
prompt  data file:      &&vtbl_name..dat
prompt  control:        &&vtbl_name..ctl