Skip to Main Content
  • Questions
  • Use of Wrap utility for 12K LOC packages

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Edgar.

Asked: January 12, 2017 - 3:55 am UTC

Last updated: January 14, 2017 - 7:45 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

I've create a SP to encrypt the source code of existing packages, my problem is that legacy packages contain more than 12K LoC (lines of code),

with small packages I have no issue, but with the heavy ones I got :

06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.


I'm using dbms_metadata.get_ddl to extract the source code and then I'm passing the variable {CLOB type} to:

DBMS_DDL.CREATE_WRAPPED

but apparently the size is to much for the wrapping function,

any idea???

      SELECT dbms_metadata.get_ddl(type_artifact, package_name,schema_name)
      INTO v_source_code_package
      FROM dual;
    
       DBMS_DDL.CREATE_WRAPPED(v_source_code_package);


and Connor said...

There are different versions of this proc. You can pass an *array* of varchar2

PROCEDURE CREATE_WRAPPED
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DDL                            TABLE OF VARCHAR2(32767) IN
 LB                             BINARY_INTEGER          IN
 UB                             BINARY_INTEGER          IN


So query DBA_SOURCE and add each line into an array entry and you've good to go.

SQL> set serverout on
SQL> declare
  2    r dbms_sql.varchar2s;
  3  begin
  4    select text
  5    bulk collect into r
  6    from dba_Source
  7    where owner = 'SYS'
  8    and name = 'DBMS_PREUP'
  9    and type = 'PACKAGE BODY';
 10    dbms_output.put_line(r.count);
 11  end;
 12  /
13163

PL/SQL procedure successfully completed.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I got the idea...

Edgar Lopez, January 12, 2017 - 7:19 am UTC

Thanks Connor, I got the idea, I tried to "reuse" your example but I got an error :

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

so.. I tried a small hack....

declare

type n_array is table of varchar2(32767) index by binary_integer;
   code n_array;
   r dbms_sql.varchar2s;
begin
select text bulk collect into code
     from all_source
   where owner = 'ELOPEZ'
   and name = 'MOCK_PACKAGE'
   and type = 'PACKAGE BODY';
  
  sys.DBMS_DDL.CREATE_WRAPPED(code);
   end;
   /


then I got the error :

Error report -
ORA-06550: line 15, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_WRAPPED'
ORA-06550: line 15, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


suggestions??

and thanks for the answer, let me know to where can I send a starbucks card

I got the idea...

Edgar Lopez, January 12, 2017 - 7:20 am UTC

Thanks Connor, I got the idea, I tried to "reuse" your example but I got an error :

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

so.. I tried a small hack....

declare

type n_array is table of varchar2(32767) index by binary_integer;
   code n_array;
   r dbms_sql.varchar2s;
begin
select text bulk collect into code
     from all_source
   where owner = 'ELOPEZ'
   and name = 'MOCK_PACKAGE'
   and type = 'PACKAGE BODY';
  
  sys.DBMS_DDL.CREATE_WRAPPED(code);
   end;
   /


then I got the error :

Error report -
ORA-06550: line 15, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_WRAPPED'
ORA-06550: line 15, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


suggestions??

and thanks for the answer, let me know to where can I send a starbucks card

varchar2a

Christian, January 12, 2017 - 7:41 am UTC

Instead the varchar2s which is a table of varchar2(256) use varchar2a which is a table of varchar2(32767) to bulk collect your code lines

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDEGGIG


dbms_ddl.create_wrapped acceppts dbms_sql.varchar2a as well:
https://docs.oracle.com/database/121/ARPLS/d_ddl.htm#ARPLS66086

In any case - is there a reason why you want to create a wrapped procedure from the data dictionary instead using the wrap utility of the oracle client which simply would take any file and wrap it (if possible)?

cheers
Connor McDonald
January 13, 2017 - 1:18 am UTC

My guess is to wrap an existing set of source.

Hopefully they have the source elsewhere :-)

thanks

Edgar Lopez, January 12, 2017 - 8:52 am UTC

it was a terrific help , I was able to solve my problem , thanks!

Error with wrapping result

Edgar Lopez, January 13, 2017 - 6:43 am UTC

Hi, the reason for the wrapping from the dictionary is for a CI/CD implementation where we deploy from different sources, then we encrypt.

now I got an :

Error: PLS-00753: malformed or corrupted wrapped unit


sometimes , during the wrapping execution I got

ORA-24344: success with compilation error
ORA-06512: at "SYS.DBMS_DDL", line 761


I already tried the trick to add a char(10) at the end of each line, but not success.

I'm a bit frustrated with this, are large packages, so Im not sure if it could an especial char or something else...
Connor McDonald
January 14, 2017 - 7:45 am UTC

ORA-24344: success with compilation error

is a procedure that is not valid. Just because a procedure is stored in the dictionary doesn't mean its compiled and working.

SQL> create or replace procedure PP is
  2  asdasdkqwe
  3  qwe
  4  qwe
  5  qwe;
  6  end;
  7  /

Warning: Procedure created with compilation errors.



wrap

Christian, January 13, 2017 - 9:11 am UTC

You'd certainly would know which packages are corrupted after wrapping; I'd take those packages, use the command line wrap utility and try it on those (source) files; also maybe compare them to the wrapped ones in your data dictionary:

https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#LNPLS01602

(be careful - the versions of the wrap utility plus the database version you are using dbms_ddl should match completely or otherwise you certainly will get differences; maybe simply use the wrap utility in the oracle home of your database server installation for testing purposes)

I am guessing the wrapper doesn't like some objects and refuses to wrap them; the command line utility is somewhat mute with errors as well, but at least it won't create garbage files when it can't wrap them, so you'd simply would need to check for source files which don't have the wrapped keyword in their create command.

Hi, the reason for the wrapping from the dictionary is for a CI/CD implementation where we deploy from different sources, then we encrypt.

Why not do it the other way around? Having a script / procedure / whatever which wraps packages in the data dictionary...I simply have a bad feeling about this (and your random corrupted package simply confirms this bad feeling).

Assuming you have version control in place, and assuming your deploy meachanism does (amongst others)
- check out from version control
- call sqlplus for each source file in directory X and run it against schema Y
- call stored procedure / script / whatever to wrap all sources in schema Y

you could do
- check out
- call the wrap utility for each source file in directory X (and let it output the wrapped destination files to directory Z)
- call sqlplus for each wrapped file in drectory Z and run it against schema Y

Then in your testing database there would never ever be a package in plain text if that is your concern, even at deploy time. Plus deployment would be faster as you'd have 50% less DDL statements at hand for creating database packages when deploying - you'd simply create your database packages already wrapped instead of first create them, then wrap them.

The wrap utility comes with a simple oracle client installation; you can wrap a package with a 11.2.0.x wrap utility and use this wrapped package in any 11.2.0.x database or higher if this is your concern.

cheers

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here