自動生成sqlldr 控制檔案的指令碼（Script To Generate SQL*Loader Control File）

Script To Generate SQL*Loader Control File (文件 ID 1019523.6)

Oracle Server - Enterprise Edition - Version 7.3.4.0 to 11.2.0.4 [Release 7.3.4 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 22-NOV-2012***

Script to generate SQL*Loader control file.

Execution Environment: SQL*Plus

Access Privileges: SELECT privileges on the table

Usage: sqlplus / @control.sql 

Instructions: PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. The script will produce an output file named .ctl. This file can be viewed in a browser or uploaded for support analysis.

This script prepares a SQL*Loader control file for a table already existing in the database. The script accepts the table name and automatically creates a file with the table name and extension 'ctl'. This is specially useful if you have the DDL statement to create a particular table and have a free-format ASCII-delimited file but have not yet created a SQL*Loader control file for the loading operation.

Default choices for the file are as follows (alter to your needs):

Delimiter: comma (',') 
INFILE file extension: .dat 
DATE format: 'MM/DD/YY'

You may define the Loader Data Types of the other Data Types by revising the DECODE function pertaining to them.

Please note: The name of the table to be unloaded needs to be provided when the script is executed as follows:

SQL> start control.sql emp

Example:

SQL> start control.sql emp 

LOAD DATA 
INFILE 'EMP.dat' 
INTO TABLE EMP 
FIELDS TERMINATED BY ',' 
( 
    EMPNO 
  , ENAME 
  , JOB 
  , MGR 
  , HIREDATE DATE "MM/DD/YY" 
  , SAL 
  , COMM 
  , DEPTNO 
)

No special instructions.

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

SQL*Plus script control.sql:

set echo off
set heading off 
set verify off 
set feedback off 
set show off 
set trim off 
set pages 0 
set concat on 
set lines 300
set trimspool on
set trimout on

spool &1..ctl 

select 'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       'INTO TABLE '||table_name||chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS'||chr (10)||'(' 
from   all_tables
where  table_name = upper ('&1');

select decode (rownum, 1, ' ', ' , ')||
       rpad (column_name, 33, ' ')||
       decode (data_type, 'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)', 
                          'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
                          'NUMBER', decode (data_precision, 0, 'INTEGER EXTERNAL NULLIF ('||column_name||'=BLANKS)', 
                                    decode (data_scale, 0, 'INTEGER EXTERNAL NULLIF ('||column_name||'=BLANKS)', 
                           'DECIMAL EXTERNAL NULLIF ('||column_name||'=BLANKS)')), 
                           'DATE', 'DATE "MM/DD/YY" NULLIF ('||column_name||'=BLANKS)', null) 
from   user_tab_columns 
where  table_name = upper ('&1') 
order  by column_id;

select ')' 
from   sys.dual; 
spool  off

No sample output included.

DISCLAIMER: EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE, PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY TIME WITHOUT NOTICE.

LIMITATION OF LIABILITY: IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE, DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY. ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.