【IT168 ORACLE开发】PL/SQL Stored Procedure Export

　　This tip comes from Thomas Marikle, a Senior Oracle DBA in Colorado Springs, CO.

　　This utility will export every PL/SQL stored procedure (procedures, functions, packages) from a user's schema and create individual scripts, which can be run from SQL*Plus to recreate the object in Oracle.     Each created script is named with the schema and stored procedure name so that they do not conflict with any similarly named scripts generated from another schema.

　　There are two scripts that make up the utility.

　　The first script, 'export_source.sql', builds a temporary script with each source program that will be extracted from Oracle's data dictionary.

　　The second script, 'extract_source.sql', is called from the temporary script for each stored procedure. This script actually performs the source extraction and some simple formatting so that trailing spaces are removed. It will also structure the final build scripts so that they can be executed in SQL*Plus as SQL scripts.


***************************************************************
--- Source Code ---
***************************************************************
　　export_source.sql

SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512

SET TERMOUT OFF
/**//*
----------------------------------------------------------------------------------
--
-- SCRIPT: export_source.sql
--
-- AUTHOR: Thomas Marikle
-- tmarikle@uswest.net
--
-- PURPOSE: Exports the current user's PL/SQL source code into a subdirectory
-- called .\export.
--
-- Each source program is prefixed with the Oracle username to
-- partition them in cases where multiple schemas may have different
-- versions of the same program unit.
--
-- PARAMETERS: none
--
-- DEPENDENCIES: extract_source.sql - SQL used to extract PL/SQL code from the
-- Oracle data dictionary and perform some
-- simple formatting on it. It also
-- strips trailing spaces from the source code.
--
-- REVISIONS:
-- Ver Date Author Description
-- --------- ---------- ------------------ ------------------------------------
-- 1.0 09/15/2000 Thomas Marikle Initial implementation
--
---------------------------------------------------------------------------------
*/
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

*******************************************************************************
extract_source.sql

SET HEAD OFF VERIFY OFF
/**//*
------------------------------------------------------------------------------
--
-- SCRIPT: extract_source.sql
--
-- AUTHOR: Thomas Marikle
-- tmarikle@uswest.net
--
-- PURPOSE: Supports export_source.sql, which exports the current user's PL/SQL
-- source code into a subdirectory called .\export.
--
-- This script does the actual extraction of the source code and then
-- performs some simple formatting.
--
-- PARAMETERS: Procedure Name
-- Procedure Type
--
-- DEPENDENCIES: none
--
-- REVISIONS:
-- Ver Date Author Description
-- --------- ---------- ------------------ ------------------------------------
-- 1.0 09/15/2000 Thomas Marikle Initial implementation
--
------------------------------------------------------------------------------
*/

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