Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1106657
  • 博文数量: 178
  • 博客积分: 2776
  • 博客等级: 少校
  • 技术积分: 2809
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-22 15:36
文章分类

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2013-03-21 13:18:57

--1、用sys用户等陆的话:
Sql代码
select text from dba_source where owner= 'lingfeng' and type = 'PROCEDURE';



oracle认证考试--2、用一般用户(要导出其下存储过程的用户):
Sql代码
select text from user_source;
spool off;


oracle认证考试法三:(自己写存储过程导出)

1.创建DIRECTORY
Sql代码
create or replace directory PROCE_DIR as 'C:/Documents and Settings/Administrator/桌面';



如创建报没权限的错误,则以system的用户登录,给当前用户赋权

oracle认证考试--赋权语句
Sql代码
grant create any directory to bijian;



oracle认证考试--撤权语句
Sql代码
revoke create any directory from bijian;



创建之后可通过如下语句查询是否创建成功
Sql代码
select * from dba_directories;



oracle认证考试2.创建导出存储过程
Sql代码
create or replace procedure loadProce(owner varchar2)
is
type user_source_table_type is table of user_source.text%TYPE INDEX BY BINARY_INTEGER;
user_source_table user_source_table_type;
file_handle utl_file.file_type;
stor_text VARCHAR2(4000);
sql_stat varchar2(1000);
sql_stat2 varchar2(1000);
sql_stat3 varchar2(1000);
nCount NUMBER;
i NUMBER;
begin
sql_stat:='select distinct(name) from all_source where owner = ''' || upper(owner) || '''';
execute immediate sql_stat bulk collect into user_source_table;
file_handle:=utl_file.fopen('PROCE_DIR','test.sql','W');
for j in 1..user_source_table.count loop
i:=1;
sql_stat2:='SELECT MAX(LINE) FROM ALL_SOURCE WHERE OWNER=''' || upper(owner) || ''' AND NAME=''' || user_source_table(j) || '''';
--dbms_output.put_line(sql_stat2);
execute immediate sql_stat2 into nCount;
WHILE i<=nCount LOOP
sql_stat3:='SELECT TEXT FROM ALL_SOURCE WHERE OWNER=''' || upper(owner) || ''' AND NAME=''' || user_source_table(j) || ''' and line = ' || i;
--dbms_output.put_line(sql_stat3);
execute immediate sql_stat3 into stor_text;
i:=i+1;
utl_file.put(file_handle,stor_text);
END LOOP;
end loop;
utl_file.fclose(file_handle);
commit;
end loadProce;
阅读(2734) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~