外部表应用实例

利用外部表，实现对批量表自动授权功能。由于要授权的表名是由普通文件（.txt）的形式下发，所以此时利用了外部表，减轻了工作量。

read.txt中是一些表名，在此不再列出。

1.创建目录

SQL>create directory testdir as 'd:\oracle\temp\';

2.创建外部表
SQL>create table readtab
(table_name varchar2(50))
organization external
(type oracle_loader
default directory testdir
access parameters ( fields terminated by ',' )
location ('read.txt')
)
/

3应用1

select 'grant select on '||trim(table_name)|| ' to QH_YQCDTJC;' from readtab
/

4.创建外部表2
SQL>create table writetab
(table_name varchar2(300))
organization external
(type oracle_loader
default directory testdir
access parameters ( fields terminated by ',' )
location ('write.txt')
)

5.应用2.
select 'grant select on '||trim(table_name)|| ' to QH_YQCDTJC;' from readtab
union
select 'grant insert on '||trim(table_name)|| ' to QH_YQCDTJC;' from writetab
union
select 'grant delete on '||trim(table_name)|| ' to QH_YQCDTJC;' from writetab
union
select 'grant update on '||trim(table_name)|| ' to QH_YQCDTJC;' from writetab
union
select 'grant insert on '||trim(table_name)|| ' to QH_YQSJYCJL;' from writetab
union
select 'grant delete on '||trim(table_name)|| ' to QH_YQSJYCJL;' from writetab
union
select 'grant update on '||trim(table_name)|| ' to QH_YQSJYCJL;' from writetab
union
select 'grant select on '||trim(table_name)|| ' to QH_YQSJYCJL;' from readtab