12 Replies Latest reply: Dec 27, 2009 10:24 AM by 743198 RSS

    exp/imp procedures, functions and packages question

    FireFox88
      Hi

      I've a 9i R2 version Oracle database. I would like to export procedures, functions and packages from a schema. How do I do that?

      Is there any script or command lines can provide?

      Thanks
        • 1. Re: exp/imp procedures, functions and packages question
          damorgan
          The Oracle docs are located at: http://tahiti.oracle.com
          • 2. Re: exp/imp procedures, functions and packages question
            732412
            Hello user12259190.
            You can do an export of the user itself, excluding table data as in
            H:\>exp
            
            Export: Release 10.2.0.1.0 - Production on Tue Dec 22 11:22:52 2009
            
            Copyright (c) 1982, 2005, Oracle.  All rights reserved.
            
            
            Username: db_user@db_sid
            Password:
            
            Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
            With the Partitioning, Data Mining and Real Application Testing options
            Enter array fetch buffer size: 4096 >
            
            Export file: EXPDAT.DMP >
            
            (2)U(sers), or (3)T(ables): (2)U > 2
            
            Export grants (yes/no): yes > no
            
            Export table data (yes/no): yes > no
            
            Compress extents (yes/no): yes > no
            
            Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
            server uses UTF8 character set (possible charset conversion)
            Note: table data (rows) will not be exported
            Note: grants on tables/views/sequences/roles will not be exported
            . exporting pre-schema procedural objects and actions
            . exporting foreign function library names for user DB_USER
            . exporting PUBLIC type synonyms
            . exporting private type synonyms
            . exporting object type definitions for user DB_USER
            About to export DB_USER's objects ...
            . exporting database links
            . exporting sequence numbers
            . exporting cluster definitions
            . about to export DB_USER's tables via Conventional Path ...
            . . exporting table  TABLE_NAMEs
            EXP-00091: Exporting questionable statistics.
            . exporting synonyms
            . exporting views
            . exporting stored procedures
            . exporting operators
            . exporting referential integrity constraints
            . exporting triggers
            . exporting indextypes
            . exporting bitmap, functional and extensible indexes
            . exporting posttables actions
            . exporting materialized views
            . exporting snapshot logs
            . exporting job queues
            . exporting refresh groups and children
            . exporting dimensions
            . exporting post-schema procedural objects and actions
            . exporting statistics
            Export terminated successfully with warnings.
            Unfortunately, you can't export just the objects you want to unless they are tables.

            Using import (imp) you can list the content of your packages, procedures, functions, views, etc. and perhaps that will give you what you need.

            Another choice would be to use
            SELECT * FROM user_source ORDER BY 2, 1, 3;
            to list the code.

            Hope this helps,
            Luke
            • 3. Re: exp/imp procedures, functions and packages question
              Dean Gagne-Oracle
              Luke is correct. The best that you can do with exp/imp is export/import the schema, but you will get all of the other objects. If you were on version 10, then datapump would allow you to do exactly what you want. I know that does not help your case, but just letting you know that the support is available if/when you ever get to 10.

              Dean
              • 4. Re: exp/imp procedures, functions and packages question
                Lubiez Jean-Valentin
                Hello,


                Starting with 10g you could use the DataPump utility but, in 9i you can already use the package dbms_metadata.

                For instance, you can "export" (or get) the DDL of Procedures / Functions like that:
                SET LONG 2000000 PAGESIZE 0
                SELECT DBMS_METADATA.GET_DDL(o.object_type, o.object_name, o.owner)
                from dba_objects o
                where owner=’{color:red}SCHEMA_NAME{color}’
                and object_type in (’{color:red}PROCEDURE{color}','{color:red}FUNCTION{color}’);
                /
                Afterwards, by executing this DDL you can create the Procedures wherever you want provided you have the
                CREATE PROCEDURE (and FUNCTION) system privilege.

                This can be used for every kind of Object Type.

                You can find here a link about dbms_metadata:

                [http://www.psoug.org/reference/dbms_metadata.html]


                Hope this help.
                Best regards,
                Jean-Valentin

                Edited by: Lubiez Jean-Valentin on Dec 22, 2009 10:18 PM
                • 5. Re: exp/imp procedures, functions and packages question
                  732412
                  Hello Jean-Valentin,
                  Just started looking at DBMS_METADATA. How does it work with package bodies?
                  SELECT   DBMS_METADATA.GET_DDL(o.object_type, o.object_name, UPPER('schema_name'))
                  FROM     user_objects  o
                  WHERE    object_type NOT IN ('TABLE', 'VIEW');
                  
                  ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in function GET_DDL
                  ORA-06512: at "SYS.DBMS_METADATA", line 2682
                  ORA-06512: at "SYS.DBMS_METADATA", line 2733
                  ORA-06512: at "SYS.DBMS_METADATA", line 4333
                  ORA-06512: at line 1
                  31600. 00000 -  "invalid input value %s for parameter %s in function %s"
                  *Cause:    A NULL or invalid value was supplied for the parameter.
                  *Action:   Correct the input value and try the call again.
                  Yes, I will be looking this up, but wanted to raise the issue as soon as it was found. Also, getting ready to head home from the office, so I won't be checking this out until tomorrow.

                  Thanks,
                  Luke
                  • 6. Re: exp/imp procedures, functions and packages question
                    732412
                    Hello Jean-Valentin.
                    Guess I'm not going home as soon as I thought.

                    DBMS_METADATA.GET_DDL outputs the DDL for a package and package body together when the object type passed is 'PACKAGE'. This is great!!!

                    Now if I could only convince the developers of Oracle SQL Developer to use DBMS_METADATA.GET_DDL for generating the DDL for views.

                    Thanks,
                    Luke
                    • 7. Re: exp/imp procedures, functions and packages question
                      Lubiez Jean-Valentin
                      Hello,


                      The normal input should be PACKAGE_BODY ( with underscore ) and not PACKAGE BODY.

                      Do you still have the ORA-31600 error if you execute the following:
                      SELECT DBMS_METADATA.GET_DDL(o.object_type, o.object_name, o.owner)
                      from dba_objects o
                      where owner=’{color:red}SCHEMA_NAME{color}’
                      and object_type = ’PACKAGE_BODY';
                      Please let me know.
                      Best regards,
                      Jean-Valentin
                      • 8. Re: exp/imp procedures, functions and packages question
                        Lubiez Jean-Valentin
                        Hello Luke,


                        I'm happy that it could be helpfull :-)


                        Best regards,
                        Jean-Valentin
                        • 9. Re: exp/imp procedures, functions and packages question
                          FireFox88
                          Hi Thanks to all for replying this post

                          I did found out some suggestion from internet that using sql query to call and get all the function/procedure/packages codes from the particular schema and put it into a script recreate it.

                          So how do I do that? can someone give me the command line?
                          • 10. Re: exp/imp procedures, functions and packages question
                            732412
                            Hello Jean-Valentin.
                            The problem here is that the GET_DDL procedure wants object type passed as PACKAGE_BODY and dba_objects stores object type as PACKAGE BODY. This works
                            SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, 'SCHEMA_NAME')
                            FROM   user_objects
                            WHERE  object_type = 'PACKAGE BODY';
                            Thanks again,
                            Luke
                            • 11. Re: exp/imp procedures, functions and packages question
                              Lubiez Jean-Valentin
                              Hello,

                              It's me, I thank you for your feedback.

                              Best regards,
                              Jean-Valentin
                              • 12. Re: exp/imp procedures, functions and packages question
                                743198
                                You can also import METADATA by using CONTENT=METADATA with EXPDP. This will give you the metadata for all the objects existing in the database.