--STEP1 --CREATE PROCEDURE OR PACKAGE IN DATABASE (FOR EXAMPLE : XX_FA_IMPORT_PKG)
STEP2—RUN BELOW QUERY BY GIVING PACKAGE NAME AS A PARAMETER TO WRAP THE
CODE.
DECLARE
sql_text_t DBMS_SQL.varchar2a;
v_object_type VARCHAR2 (40);
v_object_name VARCHAR2 (60) := 'XX_FA_IMPORT_PKG';--<---give the package
name as a parameter.
-- Pick up the code only from
APPS schema
CURSOR c_package_body (
v_package_name VARCHAR2)
IS
SELECT text
FROM all_source
WHERE NAME = v_package_name
AND TYPE = 'PACKAGE BODY'
AND owner = 'APPS';
-- Pick up the code only from
APPS schema
CURSOR c_procedure (v_procedure VARCHAR2)
IS
SELECT text
FROM all_source
WHERE NAME = v_procedure AND TYPE = 'PROCEDURE' AND owner = 'APPS';
BEGIN
BEGIN
-- Check the
type of code
SELECT object_type
INTO v_object_type
FROM all_objects
WHERE object_name = v_object_name AND status = 'VALID';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
-- The
select statement will fail
-- for a
package as there will be
-- 2 rows. 1
for package spec and
-- 1 for
package body
v_object_type := 'PACKAGE BODY';
END;
IF (v_object_type = 'PROCEDURE' OR v_object_type = 'FUNCTION')
THEN
-- Open the
cursor by passing the name of the package
OPEN c_procedure (v_object_name);
-- Get each
line of code into each array row, i.e. PL/SQL table
FETCH c_procedure
BULK COLLECT INTO sql_text_t;
CLOSE c_procedure;
ELSIF v_object_type = 'PACKAGE BODY'
THEN
-- Open the
cursor by passing the name of the package
OPEN c_package_body (v_object_name);
-- Get each
line of code into each array row, i.e. PL/SQL table
FETCH c_package_body
BULK COLLECT INTO sql_text_t;
CLOSE c_package_body;
END IF;
-- Since the code stored in
the database does not
-- contain the DDL text,
CREATE OR REPLACE, we are
-- adding this part in the
beginning of the first
-- line as the package body
will be recompiled
sql_text_t (1) := 'CREATE OR REPLACE ' || sql_text_t (1);
-- Call the Oracle package to
reompile the code
-- and encrypt it into the
database
DBMS_DDL.create_wrapped
(DDL => sql_text_t, lb => 1, ub => sql_text_t.COUNT);
END;
--STEP3 : RUN BELOW SELECT
QUERYNOW CHECK THE CODE EITHER WRAPED OR NOT
SELECT *
FROM all_source
WHERE NAME = 'XX_FA_IMPORT_PKG'
--STEP4 : CLICK ON TEXT TO SEE THE WRAPPED CODE
