oracle这么导入,如何导入存储过程 - Oracle数据库管理 - ITPUB论坛－中国专业的IT技术社区...

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