'WRAP' function with 'EXECUTE IMMEDIATE'

1) Suppose My procedure name is vnk_proc. Anybody can paste this in toad and press f4    and  can view the code(No security for the code).

2) In Sqlplus,the code will be stored in user_source table. IF I write this sql statement it will  display my code

         

        SELECT text FROM USER_SOURCE WHERE TYPE='PROCEDURE' AND NAME LIKE 'VNK_PROC'

3) In order to hide our code both in toad and sqlplus, we use the overloaded ‘WRAP’ function with ‘EXECUTE IMMEDIATE’.

DECLARE

vnk_procedure VARCHAR2(32767);

BEGIN

vnk_procedure := 'CREATE OR REPLACE PROCEDURE wrap_test54 '

||'IS '

||' v_airport_codes AIRPORT_LIST.AIRPORT_CODE%TYPE; '

||' '

||' CURSOR emp_cur IS '

||' SELECT empno''

||' FROM emp'

||' ORDER BY empno;'

||' '

||'BEGIN '

||' FOR y IN emp_cur LOOP '

||' DBMS_OUTPUT.PUT_LINE('Employee Number: ''||y.empno);'

||' END LOOP;'

|| 'END;';

EXECUTE IMMEDIATE DBMS_DDL.WRAP(vnk_procedure);

END;

/

4) TO see the wrapped procedure, select the text from the user_source view

SELECT TEXT FROM USER_SOURCE WHERE NAME = 'WRAP_TEST54';

5) DBMS_DDL.CREATE_WRAPPED works in a similar way. The following example

shows how it differs from the WRAP function.

DECLARE

VNK_PROCEDURE VARCHAR2(32767);

BEGIN

VVK_PROCEDURE := 'CREATE OR REPLACE PROCEDURE create_wrapped_test54 '

||'IS '

||' '

||' CURSOR emp_cur IS '

||' SELECT empno'

||' FROM emp'

||' ORDER BY empno;'

||' '

||'BEGIN '

||' FOR y IN emp_cur LOOP '

||' DBMS_OUTPUT.PUT_LINE(''Employee Number: ''||y.empno);'

||' END LOOP;'

|| 'END;';

SYS.DBMS_DDL.CREATE_WRAPPED(VNK_PROCEDURE);

END;