A Practical backup Pl/sql program tool

Backup | program


/*ligang1000@hotmail.com*/





 





feature: Used to back up all Pl/sql objects owned by the current user, including





Type,type body, PROCEDURE, FUNCTION, PACKAGE, PACKAGE body or JAVA SOURCE)





 





principle: The call to the User_source data dictionary gets all the





pl/sql Code.





 




Examples of
use:





1. Set up directory C:\EXPORT
in C disk




2. Will Export_source. SQL and Extract_source. SQL Copy to C packing directory.





3. Landing Sqlplus, CONNECT Scott/tiger





4. Run @c:\export_source. SQL





5. At the end of execution, all Scott-owned Pl/sql object code files are built in the C:\EXPORT directory.





suffix name. PKS says package





suffix name. PKB says package body





Suffix name. SQL represents other Objects





 





 





Note:





If you want the Pl/sql objects of each schema in the database, simply change the user_source in the tool code to Dba_source by the system





can be run.





 





 





 





 





Tool Code:





Export_source.sql





 





SET serveroutput on SIZE 1000000





SET ECHO off VERIFY off FEEDBACK out Trimspool on PAGES 0 LINES 512





SET termout off





SET termout on





 





PROMPT





PROMPT pl/sql Export Utility





PROMPT





PROMPT This utilty exports all to 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 out 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 in 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)) text





from User_source US





WHERE us.name = ' &1 '





and Us.type = ' &2 '





ORDER by Us.line;





 





PROMPT/





PROMPT