SQL & PL/SQL

All,

I have written a pl/sql block to update a table. Assume that when am trying to update for a table which does exists in the DB.

DECLARE
pRetVal NUMBER:=-1;
c_module varchar2(20):='fnUpdateDMD';
v_sqlerrm varchar2(400);
v_message varchar2(400);
v_sqlerrc number(20);
recs_updated number(20):=0;
BEGIN
    UPDATE DMDUN A
    SET A.U_LLA = (SELECT B.U_LLA FROM DMDUNIT B
                   WHERE A.U_CASEUPC = B.U_CASEUPC
                   AND B.U_BASE_ITEM = 'Y' GROUP BY B.U_LLA)
       WHERE A.U_CASEUPC IN (SELECT C.U_CASEUPC FROM DMDUNIT C
                             WHERE  C.U_BASE_ITEM = 'N'
                             GROUP BY C.U_CASEUPC);
  pRetVal:=0;
  recs_updated:=SQL%ROWCOUNT;
  dbms_output.put_line('No of Rows Updated in DMDUNIT->'|| ' '||recs_updated);
  COMMIT;
  EXCEPTION WHEN OTHERS THEN
   pRetVal:=-1;
   v_sqlerrc:=SQLCODE;
   v_sqlerrm:=SQLERRM;
   dbms_output.put_line(pRetVal);
  END;

when i run the above procedure am getting error like this

     UPDATE DMDUN A
               *
ERROR at line 9:
ORA-06550: line 9, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored

But i want to capture in the sqlerrornumber and sqlerror message in the variables(vsqlerrc and v_sqlerrm) and return -1(pretval) to calling environment. could you assist me how to proceed..

Regards,
Achyut

Edited by: Achyut K on Dec 27, 2011 5:15 AM