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***
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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21754115/viewspace-1723368/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21754115/viewspace-1723368/