--This is a modification of CREATE_CONTROL.SQL that just creates a
--  SQL query file.

REM NAME:   TFSCONTR.SQL
REM USAGE:"@path/tfscontr table_name"
REM (Modified to use an 'ACCEPT' statement for table name input.)
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT privileges on the table
REM --------------------------------------------------------------------------
REM PURPOSE:
REM    Prepares a SQL*Loader control file for a table already existing in the
REM    database.  The script accepts the table name and automatically creates
REM    a file with the table name and extension 'ctl'.
REM    This is especially useful if you have the DDL statement to create a
REM    particular table and have a free-format ASCII-delimited file but have
REM    not yet created a SQL*Loader control file for the loading operation.
REM
REM    Default choices for the file are as follows (alter to your needs):
REM     Delimiter:    comma (',')
REM     INFILE file extension:   .dat
REM     DATE format:   'MM/DD/YY'
REM
REM    You may define the Loader Data Types of the other Data Types by
REM    revising the decode function pertaining to them.
REM
REM ---------------------------------------------------------------------------
REM EXAMPLE:
REM    SQL> start control.sql emp
REM
REM    LOAD DATA
REM    INFILE 'EMP.dat'
REM    INTO TABLE EMP
REM    FIELDS TERMINATED BY ','
REM    (
REM
REM       EMPNO
REM     , ENAME
REM     , JOB
REM     , MGR
REM     , HIREDATE      DATE "MM/DD/YY"
REM     , SAL
REM     , COMM
REM     , DEPTNO
REM
REM    )
REM
REM ---------------------------------------------------------------------------
REM DISCLAIMER:
REM    This script is provided for educational purposes only. It is NOT
REM    supported by Oracle World Wide Technical Support.
REM    The script has been tested and appears to work as intended.
REM    You should always run new scripts on a test instance initially.
REM --------------------------------------------------------------------------
REM Main text of script follows:

PROMPT
PROMPT First query for 'table' LIKE '???'

--@v_t   --This is a query that I use sometimes when I want to look for 'tables like ...' or 'owner like ...'

SET echo OFF -
 heading OFF -
 verify OFF -
 feedback OFF -
 show OFF -
 trim OFF -
 pages 0 -
 lines 200 -
 concat ON -
 trimspool ON

ACCEPT v_tbl PROMPT "Enter TABLE NAME for which you want a SQL query:     "
ACCEPT v_own PROMPT "Enter the OWNER of the table name:                   "
ACCEPT v_syn PROMPT "Enter a SYNONYM for the table name you just entered: "

SPOOL &&v_syn..sql

PROMPT --This query selects NON-NULL columns by default, and you can pick the others to display later.
PROMPT

SELECT
 'SET pages 1000 -' || CHR(10) || CHR(9) || 'lines 1000' || CHR(10)
FROM
 sys.dual
/

SELECT
 DECODE( column_id,
  1, 'SELECT' || CHR(9) || '&&v_syn' || '.'
  , DECODE( nullable
   , 'Y', '--')
  || CHR(9) || ', ' || '&&v_syn' || '.')
 || LOWER( RPAD( column_name, 33, ' '))
FROM
 sys.all_tab_columns
WHERE 1=1
AND table_name = UPPER( '&&v_tbl')
ORDER BY
 COLUMN_ID
/

SELECT
 'FROM ' || CHR(10) || CHR(9) || DECODE( NVL( '&&v_own', 'NO_OWNER_LISTED')
     , 'NO_OWNER_LISTED', NULL
     , '&&v_own' || '.')
  || '&&v_tbl' || CHR(9) || CHR(9) || '&&v_syn' || CHR(10) ||
 'WHERE 1=1' || CHR(10) ||
 '/' || CHR(10) ||
 'PROMPT' || CHR(9) || 'Running &&v_syn..SQL' || CHR(10) || 'PROMPT' || CHR(10)
FROM
 sys.dual
/

SPOOL OFF

PROMPT **************************************************
PROMPT ** Created file &&v_syn..sql
PROMPT **************************************************
PROMPT
PROMPT Now just 'ed &&v_syn' to edit the query so that it shows more or less of your data,
PROMPT       OR 'run &&v_syn' to see the non-null columns displayed now.

SET heading ON -
 feedback ON -
 pages 34

Return to : Oracle Database, SQL, Application, Programming Tips