Oracle batch export Stored Procedure

Oracle batch export Stored Procedure Method 1: PL/SQL tool export Method 2: SQL code SET echo off; SET heading off; SET feedback off; spool C: \ Documents and Settings \ Administrator \ Desktop \ proc. SQL; -- 1. If the sys user is used, use the SQL code select text from dba_source where owner = 'lingfeng' and type = 'Procedure '; -- 2. General users (users who want to export their stored procedures): SQL code select text from user_source; spool off; Method 3: (write your own Stored Procedure for export) 1. create DIRECTORYSql Code create or replace directory PROCE_DIR as 'C:/Documents and Settings/Administrator/desktop '. If an error is reported during creation, Log On as A system user, grant permissions to the current user -- grant statement SQL code grant create any directory to bijian; -- revoke statement SQL code revoke create any directory from bijian; after creation, you can use the following statement to check whether the SQL code is successfully created: select * from dba_directories; 2. create or replace procedure loadProce (owner varchar2) is type partition is table of user_source.text % type index by BINARY_INTEGER; user_source_table partition; file_handle partition; stor_text VARCHAR2 (4000 ); SQL _stat varchar2 (1000); SQL _stat2 varchar2 (1000); SQL _stat varchar2 (1000); nCount NUMBER; I NUMBER; begin SQL _stat: = 'select distinct (name) from all_source where owner = ''' | upper (owner) | '''; execute immediate SQL _stat bulk collect into user_source_table; file_handle: = utl_file.fopen ('proce _ dir ', 'test. SQL ', 'w'); for j in 1 .. user_source_table.count loop I: = 1; SQL _stat2: = 'select MAX (LINE) FROM ALL_SOURCE WHERE OWNER = ''' | upper (owner) | ''' and name = ''' | user_source_table (j) | '''; -- dbms_output.put_line (SQL _stat2); execute immediate SQL _stat2 into nCount; WHILE I <= nCount LOOP SQL _stat: = 'select TEXT FROM ALL_SOURCE WHERE OWNER = ''' | upper (owner) | ''' and name = ''' | user_source_table (j) | ''' and line = '| I; -- dbms_output.put_line (SQL _stat3 ); execute immediate SQL _stat3 into stor_text; I: = I + 1; utl_file.put (file_handle, stor_text); END LOOP; end loop; utl_file.fclose (file_handle); commit; end loadProce; 3. call to save the type body, PROCEDURE, TYPE, FUNCTION, TRIGGER, package body, and PACKAGE to test on the desktop. SQL code begin loadproce ('bijian '); end;