/* *GANG1000@hotmail.com*//
Function: All PL / SQL Objects used to back up the current user (including
TYPE, TYPE BODY, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY OR JAVA SOURCE
Principle: Calling the USER_SOURCE data dictionary to get all
PL / SQL code.
Usage method example:
1. Establish a directory in the C: / export
2. Copy export_source.sql and extract_source.sql to the root directory of the C.
3. Log in to SQLPLUS, Connect Scott / Tiger
4. Run @c: /export_source.sql
5. End, all Scott's PL / SQL Object's code file is built in the C: / Export directory.
The suffix name .pks Represents package
Suffix name .pkb Represents package body
The suffix name. SQL means other Objects
Remarks:
If you want to get the PL / SQL Objects of each Schema in the database, simply change user_source in the tool code to DBA_SOURCE, by system
Operate.
Tool 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
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
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
