外部表相关说明
http://blog.csdn.net/tianlesoftware/article/details/6104099
创建外部表时没有指定外部表的字符集,现在语句增加默认字符集的操作
经过分析后发现,oracle的alter table 语句只能将外部表的access parameters 作为一个整体进行修改无法单独直接增加characterset 操作。所以我的想法是拼接一个alter table
语句,将整个access parameters 拼接进来一起修改。
以下为测试用例
--创建外部表
CREATE TABLE dept_external (
deptno NUMBER(6),
dname VARCHAR2(20),
loc VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY DATA_FILE_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
BADFILE 'ulcase1.bad'
DISCARDFILE 'ulcase1.dis'
LOGFILE 'ulcase1.log'
SKIP 20
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
deptno INTEGER EXTERNAL(6),
dname CHAR(20),
loc CHAR(25)
)
)
LOCATION ('abc.txt')
REJECT LIMIT UNLIMITED;
SELECT * from dept_external;
--创建中间表存储外部表定义
CREATE TABLE ABC ( TABLE_META VARCHAR2(2000)) AS
INSERT INTO abc
SELECT dbms_metadata.get_ddl('TABLE','DEPT_EXTERNAL','SYS') FROM dual;
ALTER TABLE abc ADD(para_value VARCHAR2(2000))
--取表定义中RECORDS DELIMITED BY newline 到LOCATION段 并在RECORDS DELIMITED BY newline后添加characterset utf8 拼接成alter table语句
SELECT 'alter table dept_external access parameters (RECORDS DELIMITED BY newline characterset utf8 ' ||
substr(table_meta,
instr(table_meta, 'RECORDS DELIMITED BY newline') +
length('RECORDS DELIMITED BY newline'),
instr(table_meta, 'LOCATION') -
instr(table_meta, 'RECORDS DELIMITED BY newline') -
length('RECORDS DELIMITED BY newline'))
FROM abc;
--执行拼接出来的语句,实现不重建外部表修改外部表字符集的操作