oracle导出object,Oracle导出PL/SQL Object定义

1. 文件export_source.sql

/*

function: export all the PL/SQL objects belongs to current user

(including TYPE,TYPE BODY, PROCEDURE , FUNCTION, PACKAGE, PACKAGE BODY and JAVA SOURCE )

usage:

1. mkdir ./export

2. run as: sqlplus scott/tiger@tns1 @export_source.sql

3. done: the source code exported will be stored in ./export

.PKS means PACKAGE

.PKB means PACKAGE BODY

.SQL means other PL/SQL objects

*/

SET SERVEROUTPUT ON SIZE 1000000

SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512

SET TERMOUT OFF

SET TERMOUT ON

PROMPT

PROMPT PL/SQL export utility

PROMPT

PROMPT This utilty exports all of the current schema's PL/SQL source code into

PROMPT a subdirectory called export.

PROMPT

PROMPT Exporting current user's source to folder ./export

SET TERMOUT OFF

SPOOL temp_source_extract.sql

PROMPT SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF PAGES 0 LINES 512

DECLARE

/*

|| This cursor extracts each PL/SQL stored procedure's name and procedure type

*/

CURSOR cur_source_programs

IS

SELECT distinct us.name, us.type,

us.name || decode(us.type, 'PACKAGE', '.PKS',

'PACKAGE BODY', '.PKB',

'.SQL') spool_file

FROM user_source us

ORDER BY us.name, us.type;

BEGIN

FOR cur_source_programs_row IN cur_source_programs

LOOP

dbms_output.put_line('spool export/' || user || '_' || cur_source_programs_row.spool_file);

dbms_output.put_line('@extract_source ' || cur_source_programs_row.name || ' "' || cur_source_programs_row.type || '"');

dbms_output.put_line('spool off');

END LOOP;

END;

/

SPOOL OFF

@temp_source_extract

SET FEEDBACK ON VERIFY ON TERMOUT ON

PROMPT Export complete!

PROMPT

2. 文件extract_source.sql

SET HEAD OFF VERIFY OFF

prompt --************************************************************************************--;

prompt --*;

prompt --* SCRIPT: &2 &1;

prompt --*;

prompt --* AUTHOR:;

prompt --*;

prompt --*;

prompt --* PURPOSE:;

prompt --*;

prompt --*;

prompt --*;

prompt --*;

prompt --*;

prompt --* PARAMETERS:;

prompt --*;

prompt --*;

prompt --* DEPENDENCIES: none;

prompt --*;

prompt --* REVISIONS:;

prompt --* Ver Date Author Description;

prompt --* --------- ---------- ------------------ ------------------------------------;

prompt --* ;

prompt --*;

prompt --*************************************************************************************--;

SELECT DECODE(ROWNUM, 1, 'CREATE OR REPLACE '|| RTRIM(RTRIM(us.text, CHR(10) )),

RTRIM(RTRIM(us.text, CHR(10) ))) text

FROM user_source us

WHERE us.name = '&1'

AND us.type = '&2'

ORDER BY us.line;

PROMPT /

PROMPT

3. 执行 sqlplus scott/tiger@ora @export_source.sql