OCM_Session4_3_Oracle_LoaderExternalTables - 数据库编程

dr scott/tiger control=foo logfile=log' is allowed, but 'sqlldr scott/tiger control=foo log' is not, even though the position of the parameter 'log' is correct. [oracle@ocm1 script]$

sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY
3.执行sqlldr，生成外部表，不过这里应该先创建sh.exm1表，否则会报错 先生成创建外部表的生成语句，创建的语句放在日志里面，之后再查日志，查看创建表的语句。
[oracle@ocm1 script]$ sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 12:04:00 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-941: Error during describe of table SH.EXM1 ORA-04043: object SH.EXM1 does not exist [oracle@ocm1 script]$

a.所以首先要创建一个表名为sh.exm1
SH@PROD> create table sh.exm1 (emp_no int,dept_no int ,name varchar2(30),num int);
Table created.
b.再来导入一次：
[oracle@ocm1 script]$ sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 12:04:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

[oracle@ocm1 script]$

c.查看日志：
[oracle@ocm1 script]$ cat prod_master.log
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 12:04:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: prod_master.ctl Data File: /home/oracle/script/prod_master.dat Bad File: prod_master.bad Discard File: none specified (Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: External Table
Table SH.EXM1, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMP_NO FIRST * WHT CHARACTER DEPT_NO NEXT * WHT CHARACTER NAME NEXT * WHT CHARACTER NUM NEXT * WHT CHARACTER

CREATE DIRECTORY statements needed for files ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script' CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'

CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_EXM1" ( "EMP_NO" NUMBER(38), "DEPT_NO" NUMBER(38), "NAME" VARCHAR2(30), "NUM" NUMBER(38) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad' LOGFILE 'prod_master.log_xt' READSIZE 1048576 FIELDS TERMINATED BY WHITESPACE LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "EMP_NO" CHAR(255) TERMINATED BY WHITESPACE, "DEPT_NO" CHAR(255) TERMINATED BY WHITESPACE, "NAME" CHAR(255) TERMINATED BY WHITESPACE, "NUM" CHAR(255) TERMINATED BY WHITESPACE ) ) location ( 'prod_master.dat' ) )REJECT LIMIT UNLIMITED
-------以上红色部分是创建外部表的语句
INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO SH.EXM1 ( EMP_NO, DEPT_NO, NAME, NUM ) SELECT "EMP_NO", "DEPT_NO", "NAME", "NUM" FROM "SYS_SQLLDR_X_EXT_EXM1"
------以上是加载数据的语句
statements to cleanup objects created by previous statements: ------

OCM_Session4_3_Oracle_LoaderExternalTables(二)