转自tom
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