Protect the Code at Source

PL/SQL program units often contain very sensitive and confidential information about company procedures and trade secrets, which makes them a protected entity group, similar to tables. To prevent unauthorized viewing of the source code, the programs are often obfuscated using the wrap command line utility. You can invoke wrap only after the PL/SQL script is created; the utility creates a wrapped file from the input clear text. Thankfully, Oracle Database 10g Release 2 provides a supplied package that you can use to create the code in a wrapped format.

For instance, imagine that you want to create the simple procedure  P in wrapped format.

scott@10GR2> create or replace procedure P
  2  as
  3  begin
  4    dbms_output.put_line('who called me');
  5  end;
  6  /

Procedure created.

Inside the PL/SQL unit, you can create it dynamically but in wrapped format with:


scott@10GR2> begin
  2     dbms_ddl.create_wrapped (' create or replace procedure p as begin
  3      dbms_output.put_line ('' who called me '' );
  4  end; ');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09


 The first line, procedure P wrapped, is confirmation that the procedure was created in wrapped manner.
 
scott@10GR2> SELECT text
  2  FROM USER_SOURCE
  3  where name ='P'
  4  /

TEXT
-------------------------------------------------------------------------------------
procedure p wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4a 85
zfdZ1Dj9NsdN+BFxsAr247WXAjUwg5nnm7+fMr2ywFwWabh0K7jAMv7Shgmm4R9Jmo8wtVDI
qVAvAMpK/gjSx1xQaeoWcoXqWtxH+qEuzIUuXGnqUKCLwIHHLStwpqbBOLbP


Elapsed: 00:00:00.04

scott@10GR2> exec p;
 who called me

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Advanced Query Rewrite in 10g

Learnt something new about Oracle Database pretty much every day. Recently I learned something its about advanced query rewrite in 10g EE

scott@ORCL> create table customer
  2  nologging
  3  as
  4  select rownum as cust_id,
  5         object_name as cust_name,
  6         object_type as cust_type,
  7         created  as  created_dt
  8  from all_objects
  9  /

Table created.

Elapsed: 00:00:10.34
scott@ORCL> create table sales
  2  nologging
  3  as
  4  select decode(mod(level,366),0,1) as cust_id,
  5         abs(dbms_random.random)/100 as sales_amt,
  6         trunc(sysdate,'yyyy')+level as trans_date
  7  from dual
  8  connect by level <=100000
  9  /

Table created.

Elapsed: 00:00:01.46
scott@ORCL> alter table customer add constraint cust_pk primary key(cust_id);

Table altered.

Elapsed: 00:00:02.14
scott@ORCL> alter table sales add constraint sales_fk foreign key(cust_id) references customer;

Table altered.

Elapsed: 00:00:00.89
scott@ORCL> begin
  2    dbms_stats.gather_table_stats(ownname=>USER,tabname=>'CUSTOMER',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns size 254');
  3    dbms_stats.gather_table_stats(ownname=>USER,tabname=>'SALES',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns size 254');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.59
scott@ORCL> create table cust_sales_mv
  2  nologging
  3  as
  4  select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt
  5  from customer c,sales s
  6  where c.cust_id = s.cust_id (+)
  7  and   c.created_dt between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy')
  8  group by c.cust_id
  9  /

Table created.

Elapsed: 00:00:00.06


scott@ORCL> begin
  2     sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
  3             name =>'demo_rewrite',
  4             source_stmt => ' select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt '||
  5                                             ' from customer c,sales s '||
  6                                             ' where c.cust_id = s.cust_id (+) '||
  7                                             ' and   c.created_dt between to_date(''01/01/2005'',''mm/dd/yyyy'') and to_date(''12/31/2005'',''mm/dd/yyyy'') ' ||
  8                                             ' group by c.cust_id ' ,
  9             destination_stmt =>' select * from cust_sales_mv ',
 10             validate => false,
 11             rewrite_mode =>'TEXT_MATCH' );
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76

Now for this Query rewrite to take place ensure that query_rewrite_integrity should be either trusted or stale_tolerated

scott@ORCL> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.00
scott@ORCL> set autotrace traceonly explain;
scott@ORCL> select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt
  2  from customer c,sales s
  3  where c.cust_id = s.cust_id (+)
  4  and   c.created_dt between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy')
  5  group by c.cust_id;
Elapsed: 00:00:00.85

Execution Plan
----------------------------------------------------------
Plan hash value: 2300330077

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CUST_SALES_MV |     1 |    39 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

scott@ORCL> set autotrace off;