OCM_Session4_3_Oracle_LoaderExternalTables - 数据库编程

------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_EXM1" DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
------以上是删除临时表的语句
Run began on Thu Mar 27 12:04:44 2014 Run ended on Thu Mar 27 12:04:45 2014
Elapsed time was: 00:00:00.91 CPU time was: 00:00:00.23 [oracle@ocm1 script]$

4.SH用户下创建目录，创建外部表 CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'
a.创建目录
SH@PROD> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'; CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/' * ERROR at line 1: ORA-01031: insufficient privileges

SH@PROD> conn /as sysdba Connected. SYS@PROD> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/' 2 ;
Directory created.
SYS@PROD> grant read,write on directory SYS_SQLLDR_XT_TMPDIR_00000 to sh;
Grant succeeded.
SYS@PROD>

或者直接是sys授权创建目录给sh用户，sh用户再创建 SYS@PROD>grant create any directory to sh; SH@PRODCREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'
b.创建外部表语句
CREATE TABLE " PROD_MASTER" ---修改成题目要求的名字 ( "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
c.使用SH用户在SQLPLUS命令下执行
SH@PROD> CREATE TABLE "PROD_MASTER" 2 ( 3 "EMP_NO" NUMBER(38), 4 "DEPT_NO" NUMBER(38), 5 "NAME" VARCHAR2(30), 6 "NUM" NUMBER(38) 7 ) 8 ORGANIZATION external 9 ( 10 TYPE oracle_loader 11 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 12 ACCESS PARAMETERS 13 ( 14 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 15 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad' 16 LOGFILE 'prod_master.log_xt' 17 READSIZE 1048576 18 FIELDS TERMINATED BY WHITESPACE LDRTRIM 19 MISSING FIELD VALUES ARE NULL 20 REJECT ROWS WITH ALL NULL FIELDS 21 ( 22 "EMP_NO" CHAR(255) 23 TERMINATED BY WHITESPACE, 24 "DEPT_NO" CHAR(255) 25 TERMINATED BY WHITESPACE, 26 "NAME" CHAR(255) 27 TERMINATED BY WHITESPACE, 28 "NUM" CHAR(255) 29 TERMINATED BY WHITESPACE ) 30 31 ) 32 location 33 ( 34 'prod_master.dat' 35 ) 36 )REJECT LIMIT UNLIMITED 37 ;
Table created.
SH@PROD> desc PROD_MASTER Name Null Type ----------------------------------------- -------- ---------------------------- EMP_NO NUMBER(38) DEPT_NO NUMBER(38) NAME VARCHAR2(30) NUM NUMBER(38)
SH@PROD> select * from PROD_MASTER;
EMP_NO DEPT_NO NAME NUM ---------- ---------- ------------------------------ ---------- 1 1 tom 1 2 2 rose 2 3 1 jone 2 4 3 jack 3 5 2 jacky 4
5 rows selected.
SH@PROD>

OCM_Session4_3_Oracle_LoaderExternalTables(三)