oracle dbms_ddl.wrap,Oracle代码封装工具和DBMS_DDL包的使用

最新推荐文章于 2022-08-24 18:29:33 发布

weixin_33930436

最新推荐文章于 2022-08-24 18:29:33 发布

阅读量142

点赞数

文章标签： oracle dbms_ddl.wrap

The WRAP Utility and the DBMS_DDL Package

On occasion it is necessary to hide (obfuscate) your PL/SQL source code. Traditionally this has been done using the WRAP utility, but Oracle 10g Release 2 also allows this to be done dynamically using the DBMS_DDLpackage. This article presents examples of both methods of PL/SQL source obfuscation.

The WRAP Utility

The wrap utility is a command line utility that obfuscates the contents of a PL/SQL source file. The syntax for the wrap utility is shown below.

wrap iname=input_file [oname=output_file]

The iname parameter specifies the source file, while the oname parameter specifies the destination file. If the destination file is not specified it defaults to the source file name with a ".pld" extension.

To see how the wrap utility works we must create a dummy source file called get_date_string.sql with the following contents.

CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS

BEGIN

RETURN TO_CHAR(SYSDATE, 'DD-MON-YYYY');

END get_date_string;

We can then run the two variants of the wrap utility, specifying our source file.

wrap iname=get_date_string.sql

PL/SQL Wrapper: Release 10.2.0.1.0- Production on Wed Oct 05 10:54:14 2005

Processing get_date_string.sql to get_date_string.plb

wrap iname=get_date_string.sql oname=get_date_string_wrap.sql

PL/SQL Wrapper: Release 10.2.0.1.0- Production on Wed Oct 05 10:55:01 2005

Processing get_date_string.sql to /tmp/get_date_string_wrap.sql

Notice that the output file name is defaulted as expected when it is not specified on the command line. The resulting output files contain the wrapped code which is listed below.

CREATE OR REPLACE FUNCTION get_date_string wrapped

a000000

abcd

71 ae

P29RDhRZX0orO0ED/mMF8i12Glkwg8eZgcfLCNL+XlquYvSuoVah8JbRPpdHDOrnwLK9spte

58d0wDO4dGUJuHSLwMAy/tKGCamhAs7G1hohrO/WTHaEcTKOd0xx9RBzc/XvN2dM6+zZPXLp

r1UqFBwU/Sx2010pwUjXpqZCvywG

The DBMS_DDL package for Dynamic Wrapping

The DBMS_DDL package contains three overloaded functions called WRAP, the simplest of which accepts a VARCHAR2 input parameter containing a PL/SQL CREATE OR REPLACE statement and returns the obfuscated PL/SQL, which can be written to a file or stored in a table. The following example shows how the basic WRAP function is used from PL/SQL.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

l_source VARCHAR2(32767);

l_wrap VARCHAR2(32767);

BEGIN

l_source := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS' ||

'BEGIN ' ||

'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ' ||

'END get_date_string;';

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source);

DBMS_OUTPUT.put_line(l_wrap);

END;

CREATE OR REPLACE FUNCTION get_date_string wrapped

a000000

abcd

mV4eMSJ8EqqgErJT91l6UZ0pdDUwgyr6LZ5GfHSmUPiJfkEObQpeDb6D7glajI+ONulxdqC1

0HvOPP4eJpQs5zxsKXpj6XL1

fvieXyWCr3BTzXTqcGYhfXrtqDVPztR/o+9UZ8l5OijDSsRW

ZPv6rISzFyqeEsCBweFUFyxd

PL/SQL procedure successfully completed.

SQL>

This works fine for PL/SQL source that is less than or equal to 32K in size, but the VARCHAR2 input parameter means it can't cope with source larger than this. Fortunately the other overloads of this function allow us to work with source greater than 32K in size.

DBMS_DDL.WRAP(

ddl DBMS_SQL.VARCHAR2S,

lb PLS_INTEGER,

ub PLS_INTEGER)

RETURN DBMS_SQL.VARCHAR2S;

DBMS_DDL.WRAP(

ddl DBMS_SQL.VARCHAR2A,

lb PLS_INTEGER,

ub PLS_INTEGER)

RETURN DBMS_SQL.VARCHAR2A;

In both overloads the source is passed in and out using an associative array (index-by table). The difference between the two is that the DBMS_SQL.VARCHAR2S type is limited to 256 bytes per line, while theDBMS_SQL.VARCHAR2A type can hold a maximum of 32K per line. The following example uses the DBMS_SQL.VARCHAR2A version to shows how these overloads work.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

l_source DBMS_SQL.VARCHAR2A;

l_wrap DBMS_SQL.VARCHAR2A;

BEGIN

l_source(1) := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS ';

l_source(2) := 'BEGIN ';

l_source(3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';

l_source(4) := 'END get_date_string;';

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source,

lb => 1,

ub => l_source.count);

FOR i IN 1 .. l_wrap.count LOOP

DBMS_OUTPUT.put_line(l_wrap(i));

END LOOP;

END;

CREATE OR REPLACE FUNCTION get_date_string wrapped

a000000

abcd

mV4eMSJ8EqqgErJT91l6UZ0pdDUwgyr6LZ5GfHSmUPiJfkEObQpeDb6D7glajI+ONulxdqC1

0HvOPP4eJpQs5zxsKXpj6XL1

fvieXyWCr3BTzXTqcGYhfXrtqDVPztR/o+9UZ8l5OijDSsRW

ZPv6rISzFyqeEsCBweFUFyxd

PL/SQL procedure successfully completed.

SQL>

In addition to the WRAP functions, the DBMS_DDL package also contains three overloaded procedures called CREATE_WRAPPED. These procedure have the same parameter lists as the three WRAP function overloads, and are used to wrap and compile the specified source. The following example shows how the DBMS_SQL.VARCHAR2A version is used.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

l_source DBMS_SQL.VARCHAR2A;

l_wrap DBMS_SQL.VARCHAR2A;

BEGIN

l_source(1) := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS ';

l_source(2) := 'BEGIN ';

l_source(3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';

l_source(4) := 'END get_date_string;';

SYS.DBMS_DDL.CREATE_WRAPPED(ddl => l_source,

lb => 1,

ub => l_source.count);

END;

PL/SQL procedure successfully completed.

SET PAGESIZE 100

SELECT text

FROM user_source

WHERE name = 'GET_DATE_STRING'

AND type = 'FUNCTION';

TEXT

------------------------------------------------------------------------

FUNCTION get_date_string wrapped

a000000

abcd

6f aa

mV4eMSJ8EqqgErJT91l6UZ0pdDUwgyr6LZ5GfHSmUPiJfkEObQpeDb6D7glajI+ONulxdqC1

0HvOPP4eJpQs5zxsKXpj6XL1fvieXyWCr3BTzXTqcGYhfXrtqDVPztR/o+9UZ8l5OijDSsRW

ZPv6rISzFyqeEsCBweFUFyxd

1 row selected.

SQL>

The CREATE_WRAPPED procedures are the equivalent of passing the wrapped source returned from the WRAP function into an EXECUTE IMMEDIATE or DBMS_SQL.PARSE call, but they are optimized to give better performance.

-------------------------------------------------------------------------

present by dylan.

weixin_33930436

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle dbms_ddl.wrap,Oracle代码封装工具和DBMS_DDL包的使用

The WRAP Utility and the DBMS_DDL PackageOn occasion it is necessary to hide (obfuscate) your PL/SQL source code. Traditionally this has been done using theWRAPutility, but Oracle 10g Release 2 also...
复制链接

扫一扫