Oracle sqlload脚本,自动生成sqlldr 控制文件的脚本（Script To Generate SQL*Loader Control File）...

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

APPLIES TO:

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***

PURPOSE

Script to generate

SQL*Loader control file.

REQUIREMENTS

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.

CONFIGURING

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

)

INSTRUCTIONS

No special

instructions.

CAUTION

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.

SAMPLE CODE

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

SAMPLE OUTPUT

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.