Monday, 25 February 2013

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;
/