Oracle Wrap Procedure

/* syntax */
$ wrap iname=p1.sql oname=p1.plb

/* For example: */
[oracle@localhost sql]$ vi p1.sql

CREATE OR REPLACE PROCEDURE "SYS"."P1"
is
begin
       dbms_output.put_line('okay');
end;
/

SQL> @./sql/p1.sql

Procedure created.
/* method_1 */
SQL> set serveroutput on

SQL> set long 999999
SQL> select dbms_metadata.get_ddl(object_type => 'PROCEDURE',name => 'P1') from dual;

DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'PROCEDURE',NAME=>'P1')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "SYS"."P1"
is
begin
       dbms_output.put_line('okay');
end;

/* method_2 */
SQL> col text for a50
SQL> col name for a5
SQL> select * from user_source a where a.name='P1';

NAME  TYPE               LINE TEXT
----- ------------ ---------- --------------------------------------------------
P1    PROCEDURE             1 PROCEDURE       "P1"
P1    PROCEDURE             2 is
P1    PROCEDURE             3 begin
P1    PROCEDURE             4        dbms_output.put_line('okay');
P1    PROCEDURE             5 end;

/* wrap */

$ wrap iname=p1.sql oname=p1.plb

SQL> @./sql/p1.plb

Procedure created.

/* query again */
SQL> select * from user_source a where a.name='P1';

NAME  TYPE               LINE TEXT
----- ------------ ---------- --------------------------------------------------
P1    PROCEDURE             1 PROCEDURE       "P1" wrapped
                              a000000
                              369
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              7
                              43 79
                              f0Ol4oGWwTH7hPDl4A3DnBHngC8wg5nnm7+fMr2ywFxpnnzDCY
                              +bdIvAwDL+0oYJqaYfSZqP
                              MLVQyKlQLwDKSv4I0sc9aXJy3NjDsaCLwIHHLcmmphStL88=

 

/* or dbms_metadata.get_dll

FUNCTION GET_DDL RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_TYPE                    VARCHAR2                IN
 NAME                           VARCHAR2                IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 VERSION                        VARCHAR2                IN     DEFAULT
 MODEL                          VARCHAR2                IN     DEFAULT
 TRANSFORM                      VARCHAR2                IN     DEFAULT
*/

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28859270/viewspace-777087/，如需轉載，請註明出處，否則將追究法律責任。