大家好，又见面了，我是你们的朋友全栈君。
1、创建目录（create any directory）：
SQL> create user oracle identified by oracle;
用户已创建。
SQL> grant dba to oracle;
授权成功。
SQL> grant create any directory to oracle;
授权成功。
SQL> conn oracle/oracle 已连接。
2、创建外部表：
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH ——————– —————————————- ——————————————- — SYS ADMIN_DIR C:ADEaime_vista_shiporacle/md/admin SYS DATA_PUMP_DIR d:oracleproduct10.2.0adminorcldpdump SYS BDUMP D:oracleproduct10.2.0adminorclbdump SYS WORK_DIR C:ADEaime_vista_shiporacle/work
SQL> create table alert_log(text varchar2(400)) 2 organization external ( 3 type oracle_loader 4 default directory BDUMP 5 access parameters( 6 records delimited by newline 7 nobadfile 8 nodiscardfile 9 nologfile 10 ) 11 location(‘alert_orcl.log’) 12 ) 13 reject limit unlimited 14 /
表已创建。
SQL> select * from alert_log where rownum<10;
TEXT —————————————————————— Dump file d:oracleproduct10.2.0adminorclbdumpalert_orcl.log Sun May 16 11:25:15 2010 ORACLE V10.2.0.3.0 – Production vsnsta=0 vsnsql=14 vsnxtr=3 Windows NT Version V6.0 CPU : 2 – type 586 Process Affinity : 0x00000000 Memory (Avail/Total): Ph:372M/1021M Sun May 16 11:25:15 2010
已选择9行。
3、外部表acess paramter获得方式
sqlldr oracle/oracle control=user.ctl external_table=generate_only
eg:
1、user.ctl(语法 可以使用EM DBCONSOLE生成) :
load
infile ‘D:oracle_filedata.txt’
badfile ‘D:oracle_filedata.bad’
discardfile ‘D:oracle_filedata.dis’
errors=50
append
into table user_data
fields terminated by ‘,’ optionally enclosed by ‘”‘
trailing nullcols
(user_name varchar2(20),
user_id number)
2、sqlldr oracle/oracle control=user.ctl external_table=generate_only log=user_data.log
你会在日志中发现：
文件需要 CREATE DIRECTORY 语句 ———————————————————————— CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ‘D:oracle_file’
用于外部表的 CREATE TABLE 语句: ———————————————————————— CREATE TABLE “SYS_SQLLDR_X_EXT_USER_DATA” ( “USER_NAME” VARCHAR2(20), “USER_ID” NUMBER ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK BADFILE ‘SYS_SQLLDR_XT_TMPDIR_00000′:’data.bad’ DISCARDFILE ‘SYS_SQLLDR_XT_TMPDIR_00000′:’data.dis’ LOGFILE ‘user_data.log_xt’ READSIZE 1048576 FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( “USER_NAME” CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘, “USER_ID” CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ ) ) location ( ‘data.txt’ ) )REJECT LIMIT UNLIMITED
4、举例：
SQL> create or replace directory user_data_dir as ‘D:oracle_file’;
目录已创建。
SQL> CREATE TABLE user_data1 2 ( 3 “USER_NAME” VARCHAR2(20), 4 “USER_ID” NUMBER 5 ) 6 ORGANIZATION external 7 ( 8 TYPE oracle_loader 9 DEFAULT DIRECTORY user_data_dir 10 ACCESS PARAMETERS 11 ( 12 RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK 13 BADFILE ‘data.bad’ 14 DISCARDFILE ‘data.dis’ 15 LOGFILE ‘user_data.log’ 16 READSIZE 1048576 17 FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM 18 MISSING FIELD VALUES ARE NULL 19 REJECT ROWS WITH ALL NULL FIELDS 20 ( 21 “USER_NAME” CHAR(255) 22 TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘, 23 “USER_ID” CHAR(255) 24 TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ 25 ) 26 ) 27 location 28 ( 29 ‘data.txt’ 30 ) 31 )REJECT LIMIT UNLIMITED 32 /
表已创建。
SQL> select * from user_data1;
USER_NAME USER_ID ——————– ———- adfadf 1 adfa1 2 hdhgh 3 a4f 4 adf 5
已选择6行。
data.txt:
adfadf,1 adfa1,2 hdhgh,3 a4f,4 , adf, ,5
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/183005.html原文链接：https://javaforall.cn
