Exception raised while wrapping up a procedure Code Using Oracle9i Wrap Utility

CREATE TABLE TEST_TAB(COL1  NUMBER,COL2  NUMBER,COL3  VARCHAR2(100))

/

 Save below procedure as a .prc file

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

   INSERT INTO test_tab

      SELECT 1, (SELECT empno

                   FROM EMP

                  WHERE ename = 'ALLEN'), 'RAJU'

        FROM DUAL;

   COMMIT;

END;

/

Then try to wrap the procedure code using wrap command

wrap iname=TEST_PROC.prc oname=TEST_PROC_w.prc;

Processing TEST_PROC.prc to TEST_PROC_w.prc

PSU(103,1,5,18):Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null others <an identifier>

   <a double-quoted delimited-identifier> <a bind variable> avg

   count current exists max min prior sql stddev sum variance

   execute forall merge time timestamp interval date

   <a string literal with character set specification>

   <a number> <a single-quoted SQL string> pipe

PSU(103,1,7,40):Encountered the symbol ")" when expecting one of the following:

   . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or

   group having intersect minus order start union where connect

   ||

PL/SQL Wrapper error: Compilation error(s) for:

CREATE OR REPLACE PROCEDURE test_proc

Outputting source and continuing.

Cause:-

--------

Above exception is why because in one select statement another select is used and after that trying to insert some constant value.

Solution:-

---------

Way1:- Convert normal sql statement as a Dynamic Sql statement.

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

  execute immediate 'INSERT INTO test_tab

      SELECT 1, (SELECT empno

                   FROM EMP

                  WHERE ename = ''ALLEN''), ''RAJU''

        FROM DUAL';

   COMMIT;

END;

/

Way2:- Put it in a single select statement

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

   INSERT INTO test_tab

       SELECT 1,empno,'RAJU'

                   FROM EMP

                  WHERE ename = 'ALLEN';   

   COMMIT;

END;

/