1.存储过程的导出。 SET PAGESIZE 49999 select DECODE (line,1,'CREATE OR REPLACE '|| RTRIM(RTRIM(us.text,CHR(10))),RTRIM(RTRIM(us.text,CHR(10)))) text FROM USER_SOURCE us WHERE TYPE='PROCEDURE'
先修改init.ora 例如: utl_file_dir=/usr //路径为 oracle所在的盘:/usr 此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中 create or replace procedure TEST is file_handle utl_file.file_type; STOR_TEXT VARCHAR2(4000); N NUMBER; I NUMBER; begin I:=1; SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1'; file_handle:=utl_file.fopen('/usr','test.txt','a'); WHILE I<=N LOOP SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1' AND LINE= I; I:=I+1; utl_file.put_line(file_handle,stor_text); END LOOP; utl_file.fclose(file_handle); commit; end TEST; /
我上面写的没有‘/’符号,下面: SELECT DECODE (line,1,'/'||CHR(10)||'CREATE OR REPLACE '|| RTRIM(RTRIM(us.text)),RTRIM(RTRIM(us.text,CHR(10)))) text FROM USER_SOURCE us WHERE TYPE='PROCEDURE' /
set heading off set echo off set feedback off set pages off set long 90000 SQL> select dbms_metadata.get_ddl('PROCEDURE',u.OBJECT_NAME) from User_Objects u where u.object_type='PROCEDURE';DBMS_METADATA.GET_DDL('PROCEDU ------------------------------ CREATE OR REPLACE PROCEDURE as cursor v_cursor CREATE OR REPLACE PROCEDUREcursor t_sor is select
例如:
utl_file_dir=/usr //路径为 oracle所在的盘:/usr
此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中
create or replace procedure TEST
is
file_handle utl_file.file_type;
STOR_TEXT VARCHAR2(4000);
N NUMBER;
I NUMBER;
begin
I:=1;
SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1';
file_handle:=utl_file.fopen('/usr','test.txt','a');
WHILE I<=N LOOP
SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1' AND LINE= I;
I:=I+1;
utl_file.put_line(file_handle,stor_text);
END LOOP;
utl_file.fclose(file_handle);
commit;
end TEST;
/
SELECT
DECODE (line,1,'/'||CHR(10)||'CREATE OR REPLACE '||
RTRIM(RTRIM(us.text)),RTRIM(RTRIM(us.text,CHR(10)))) text
FROM USER_SOURCE us
WHERE TYPE='PROCEDURE'
/
set echo off
set feedback off
set pages off
set long 90000
SQL> select dbms_metadata.get_ddl('PROCEDURE',u.OBJECT_NAME) from User_Objects u where u.object_type='PROCEDURE';DBMS_METADATA.GET_DDL('PROCEDU
------------------------------ CREATE OR REPLACE PROCEDURE
as
cursor v_cursor
CREATE OR REPLACE PROCEDUREcursor t_sor is
select
如SQL/Developer