外部表处理特殊字体-----‘㳇’

3.使用外部文件数据,使用oracle_loader来填充数据来生成外部表

 a.准备外部数据源文件

cat /u01/app/dump/1.txt "7369","SMITH","小","7902","17-DEC-80","100","0","20"

"7499","ALLEN","SALESMAN","7698","20-FEB-81","250","0","30"

"7521","WARD","SALESMAN","7698","22-FEB-81","450","0","30"

"7566","JONES","MANAGER","7839","02-APR-81","1150","0","20"

"7369","SMITH","赵","7902","17-DEC-80","100","0","20"

"7369","SMITH","㳇","7902","17-DEC-80","100","0","20"

b.创建外部表，这一步注意，如果数据库是AMERICAN_AMERICA.ZHS16GBK，

而我们的文件保存时是UTF8，++notepad可以转换，那就需要在创建语句中指定操作系统文件的类型characterset 'AL32UTF8'，并在操作系统上export NLS_LANG='AMERICAN_AMERICA.AL32UTF8'

 

create table emp_new1(

emp_id number(4),

ename varchar2(15),

job varchar2(12),

mgr_id number(4),

hiredate date,

salary number(8),

comm number(8),

dept_id number(2)

)

organization external

(

type oracle_loader

default directory ogg

access parameters(

records delimited by newline

badfile 'emp_new%a_%p.bad'

logfile 'emp_new%a_%p.log'

characterset 'AL32UTF8'

fields terminated by ','

optionally enclosed by '"'

lrtrim missing field values are null

reject rows with all null fields

)

location ('1.txt')

)

parallel

reject limit unlimited;

c.验证外部表，对于一些特殊字符，有可能需要转换，才能显示

 

 

4.重新创建一张外部表，job这一列使用的是nvarchar2（12）

create table emp_new1(

emp_id number(4),

ename varchar2(15),

job nvarchar2(12),

mgr_id number(4),

hiredate date,

salary number(8),

comm number(8),

dept_id number(2)

)

organization external

(

type oracle_loader

default directory ogg

access parameters(

records delimited by newline

badfile 'emp_new%a_%p.bad'

logfile 'emp_new%a_%p.log'

characterset 'AL32UTF8'

fields terminated by ','

optionally enclosed by '"'

lrtrim missing field values are null

reject rows with all null fields

)

location ('1.txt')

)

parallel

reject limit unlimited;

 

 

 5.外部表相关视图

a.查看外部表信息

select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;

 

b.获得平面文件的位置

select * from user_external_locations order by table_name; TABLE_NAME LOCATION DIRECTORY DIRECTORY_NAME ---------- ---------- --------- -------------------- EMP_NEW 1.txt SYS DATA_DIR EMP_NEW 2.txt SYS DATA_DIR EX_TB1 ex_tb1 SYS DATA_DIR IN_TB1 in_tb1 SYS DATA_DIR