12 Replies Latest reply: Feb 8, 2011 3:53 AM by Moazzam RSS

    export stored procedures in .sql file

    Moazzam
      I am using Oracle 10g R2. I want to write a script to generate a .sql file containing all stored procedures and functions present in my database. The purpose is to take backup of these objects.

      i am trying to use the following code:
      BEGIN
        for c in (select OBJECT_NAME,OBJECT_TYPE from user_objects o where o.object_type IN ( 'PROCEDURE','FUNCTION'))
        loop
          execute immediate ( 'SELECT dbms_metadata.get_ddl(''' || c.object_type || ''',''' || OBJECT_NAME || ''') FROM dual;');
        end loop;
        
      END;
      But i want to know how can i export the definition of objects in .sql file using this script.
        • 1. Re: export stored procedures in .sql file
          RPuttagunta
          I guess you can add 'TABLE' in your in-list?

          Edit: Never mind. That wasn't the question.

          Edited by: RPuttagunta on Feb 7, 2011 12:37 PM
          • 2. Re: export stored procedures in .sql file
            SomeoneElse
            You don't need PL/SQL or dynamic sql to do this.

            It can be as simple as this:
            SQL> create procedure p as
              2  begin
              3     dbms_output.put_line('Hello world');
              4  end;
              5  /
            
            Procedure created.
            
            SQL> create function f return varchar as
              2  begin
              3     return 'Hello world';
              4  end;
              5  /
            
            Function created.
            
            SQL> SELECT dbms_metadata.get_ddl(object_type,object_name) FROM user_objects where object_type IN ( 'PROCEDURE','FUNCTION');
            
            DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME)
            --------------------------------------------------------------------------------
            
              CREATE OR REPLACE FUNCTION "SELSE"."F" return varchar as
            begin
               return 'Hello world';
            end;
            
            
            
              CREATE OR REPLACE PROCEDURE "SELSE"."P" as
            begin
               dbms_output.put_line('Hello world');
            end;
            Then spool the results as needed. Don't forget that the output from the get_ddl function is a CLOB, so you may need to adjust your sql/plus settings accordingly to avoid wrapping and such.
            • 3. Re: export stored procedures in .sql file
              odie_63
              Hi,

              You don't need dynamic SQL here.

              You can do something like :
              DECLARE
              
                v_ddl_clob  CLOB;
                
              BEGIN
               
                FOR c IN ( SELECT object_name, object_type 
                           FROM user_objects 
                           WHERE object_type IN ('PROCEDURE','FUNCTION') )
                LOOP
                 
                  v_ddl_clob := dbms_metadata.get_ddl(c.object_type, c.object_name);
                  
                  -- write to TEST_DIR directory :
                  dbms_xslprocessor.clob2file(v_ddl_clob, 'TEST_DIR', c.object_name||'.sql');
                  
                END LOOP;
                
              END;
              /
              • 4. Re: export stored procedures in .sql file
                aerielover
                Using sqlplus

                SET pages 0
                spool proclist.sql
                SELECT
                CASE line
                WHEN 1 THEN
                'CREATE OR REPLACE ' || TYPE || ' ' || NAME || CHR(10) || text
                ELSE
                text
                END
                FROM user_source
                WHERE TYPE IN ( 'PROCEDURE','FUNCTION')
                ORDER BY name, line;
                spool OFF
                exit
                • 5. Re: export stored procedures in .sql file
                  Moazzam
                  odie_63,

                  I want a single .sql file which should contain all functions and procedures and function or procedure should be seaprated by forward salsh "/" so that this single file can be executed directly to create all objects.
                  • 6. Re: export stored procedures in .sql file
                    837236
                    With reference to the answer posted by "SomeoneElse", the provided query can be further modified as mentioned below for what you've asked

                    SELECT dbms_metadata.get_ddl(object_type,object_name) *|| '*
                    */'* FROM user_objects where object_type IN ( 'PROCEDURE','FUNCTION');

                    I hope this helps ...
                    • 7. Re: export stored procedures in .sql file
                      Efficientoracle
                      Hi,
                      The best way is

                      Spool <<SOME PATH>>
                      set pages 0
                      set lines 1000
                      set trimspool on
                      Select text From user_Source Where Type In ('FUNCTION','PROCEDURE');
                      Spool Off


                      Regards,
                      Simma...
                      • 8. Re: export stored procedures in .sql file
                        Suri
                        Use below program and save the output file as .SQL

                        declare
                        -- Local variables here
                        v_clob CLOB;

                        CURSOR cur_ddl IS SELECT object_type,object_name,owner
                        FROM dba_objects
                        WHERE owner = 'SCOTT'
                        AND object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

                        begin
                        -- Test statements here


                        FOR rec_metadata IN cur_ddl
                        LOOP

                        v_clob := DBMS_METADATA.get_ddl('PROCEDURE','TEST_P1','SCOTT' );

                        dbms_output.put_line (:v_clob);
                        dbms_output.put_line('/');

                        END LOOP;

                        end;
                        • 9. Re: export stored procedures in .sql file
                          Moazzam
                          Hi 834233

                          When i try to spool the output, then it does not completely print the function e.g.

                          >
                          CREATE OR REPLACE FUNCTION "REL_65"."VERIFY_LIST" (p_list in varchar2)
                          return
                          CREATE OR REPLACE PROCEDURE "REL_65"."VERIFYDB" is
                          v_flag number := -1;
                          beg
                          CREATE OR REPLACE PROCEDURE "REL_65"."UPDATETABLESTATUS"
                          (
                          v_TableName IN
                          CREATE OR REPLACE PROCEDURE "REL_65"."CHECK_UPDATE" (v_tablename IN VARCHAR2
                          CREATE OR REPLACE FUNCTION "REL_65"."GET_OBJECT_STATUS" (v_name varchar2) ret
                          • 10. Re: export stored procedures in .sql file
                            Moazzam
                            Thanks to all of you for the help:

                            I used the following code to create backup files for each procedure:
                            DECLARE
                              v_ddl_clob  CLOB;
                            BEGIN
                             
                              FOR c IN ( SELECT object_name, object_type 
                                         FROM user_objects 
                                         WHERE object_type IN ('PROCEDURE','FUNCTION') )
                              LOOP
                               
                                v_ddl_clob := dbms_metadata.get_ddl(c.object_type, c.object_name) || '
                            /';
                                
                                -- write to TEST_DIR directory :
                                dbms_xslprocessor.clob2file(v_ddl_clob, 'DATA_FILE_DIR', c.object_name||'.sql');
                                
                              END LOOP;
                              
                            END;
                            Then using the following shell script, i was able to create single file containing required output:
                            for file in `ls *.sql` ; do
                             cat "$file">>db.sql
                            done
                            • 11. Re: export stored procedures in .sql file
                              Saubhik
                              This can be done using a single PL/SQL block like:
                              /*************************************************************** 
                               Generate a SQL file with ddls for any Procedures and Functions.
                               This can be extended to get ddls of any objects.
                              ****************************************************************/
                              DECLARE
                              --These varriables are to manipulate the LOBs
                                myddls     CLOB := EMPTY_CLOB();
                                metadata   CLOB := EMPTY_CLOB();
                                ddls       CLOB := EMPTY_CLOB();
                                v_amount   INTEGER;
                                v_offset   INTEGER; -- Where to write.
                                l_pos      INTEGER := 1;
                                my_var     VARCHAR2(32767) := '/'; --Adding the / at the end.
                                l_clob_len INTEGER;
                                l_buffer   VARCHAR2(32767);
                                l_amount   BINARY_INTEGER := 32767;
                              
                                --The cursor to fetch the object to get the DDLs.
                                CURSOR cur_objcts IS
                                  SELECT object_name, object_type, owner
                                    FROM dba_objects
                                   WHERE object_type IN ('PROCEDURE', 'FUNCTION')
                                     AND owner IN ('SCOTT', 'HR'); --I am taking two schemas here. You can remove this restriction.
                              
                                --This function usages dbms_metadata to fetch the DDLs.
                                FUNCTION get_metadata(pi_obj_type  IN VARCHAR2,
                                                      pi_obj_name  IN VARCHAR2,
                                                      pi_obj_owner IN VARCHAR2) RETURN CLOB IS
                                  h   NUMBER;
                                  th  NUMBER;
                                  doc CLOB;
                                BEGIN
                                  h := DBMS_METADATA.open(pi_obj_type);
                                  DBMS_METADATA.set_filter(h, 'SCHEMA', pi_obj_owner);
                                  DBMS_METADATA.set_filter(h, 'NAME', pi_obj_name);
                                  th  := DBMS_METADATA.add_transform(h, 'MODIFY');
                                  th  := DBMS_METADATA.add_transform(h, 'DDL');
                                  doc := DBMS_METADATA.fetch_clob(h);
                                  DBMS_METADATA.CLOSE(h);
                                  return doc;
                                END get_metadata;
                                
                              ------Begin main executable section.
                              BEGIN
                                DBMS_LOB.createtemporary(myddls, TRUE);
                                DBMS_LOB.createtemporary(metadata, TRUE);
                                DBMS_LOB.open(myddls, DBMS_LOB.lob_readwrite);
                                DBMS_LOB.open(metadata, DBMS_LOB.lob_readwrite);
                                FOR i IN cur_objcts LOOP
                                  ddls := get_metadata(i.object_type, i.object_name, i.owner);
                                  --Get the total length of the CLOB
                                  l_clob_len := DBMS_LOB.getlength(ddls);
                                  --Read and Write in chunks.
                                  WHILE l_pos < l_clob_len LOOP
                                    DBMS_LOB.READ(ddls, l_amount, l_pos, l_buffer);
                                    DBMS_LOB.WRITE(metadata, l_amount, l_pos, l_buffer);
                                    l_pos := l_pos + l_amount;
                                  END LOOP;
                                  l_pos := 1;
                                  --append to another LOB
                                  DBMS_LOB.append(myddls,metadata);
                                  v_amount := DBMS_LOB.GETLENGTH(my_var);
                                  --Write the / character.
                                  DBMS_LOB.writeappend(myddls, v_amount, my_var);
                                
                                END LOOP;
                                --Write the whle LOB to disk.
                                DBMS_XSLPROCESSOR.clob2file(cl => myddls,flocation =>'SAUBHIK' ,fname =>'myddls.sql' );
                               --Cleanups.
                                DBMS_LOB.close(myddls);
                                DBMS_LOB.close(metadata);
                                DBMS_LOB.freetemporary(myddls);
                                DBMS_LOB.freetemporary(metadata);
                              
                              END;
                              • 12. Re: export stored procedures in .sql file
                                Moazzam
                                There is a problem with this Pl/Sql script. The final output file does not contain forward slash "/" at the start of new line. Instead first there is a space then after that space there is "/".

                                However in some procedures it is correctly placed. I could not understand the reason for its variable behavior.