SQL Script to create control
file for a table
--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
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle
Certification, Database Administration, SQL, Application, Programming Reference
Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|