Dear Sir, 

Thanks for the excellent advice.

This gives me the correct error details of the actual line of error (v_environment_in := 'XX'; --here).

/*
--start sample code


--Database version is Oracle 11g R1
--PROCEDURE t2 with raise_application_error

create or replace PROCEDURE t2(
    p_environment_in   IN  VARCHAR2
)
IS
v_environment_in varchar2(1);
v_err_code       number;
v_err_message    varchar2(4000);
BEGIN
  
  --Process Logic
  --process_logic;

  --Test scenario for fail
  v_environment_in := 'XX';

EXCEPTION
WHEN OTHERS THEN

  v_err_code    := SQLCODE;
  v_err_message := DBMS_UTILITY.FORMAT_ERROR_STACK()||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();  
  dbms_output.put_line(chr(10) || '-' || v_err_code || '-' || v_err_message );
  raise_application_error(-20000, 'Error! '||v_err_message);

END t2;
/
show errors


--run details of the Pro*C program

$ cd $BE
$ ./catch_err

Oracle error
Error Statement obtained using sqlgls is--
begin ops$xxxxx . t2 ( 'D' ) ; end ;                                                                                            À
Full Error Message obtained using sqlglm is ========
ORA-20000: Error! ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPS$xxxxx.T2", line 14
ORA-06512: at "OPS$xxxxx.T2", line 22
ORA-06512: at line 1

$

--Verifying the error details
SQL> l 14
 14*   v_environment_in := 'XX';
SQL> l 22
 22*   raise_application_error(-20000, 'Error! '||v_err_message);
SQL> 


--end sample code
*/


On similar line in addition to the original question, we need to run the procedure from the UNIX shell script.


Can you please provide your opinion on the following approach to do error handling?




/*
--start sample code
--Database version is Oracle 11g R1
--PROCEDURE t2 with raise_application_error

create or replace PROCEDURE t2(
    p_environment_in   IN  VARCHAR2
)
IS
v_environment_in varchar2(1);
v_err_code       number;
v_err_message    varchar2(4000);
BEGIN
  
  --Process Logic
  --process_logic;

  --Test scenario for fail
  v_environment_in := 'XX';

EXCEPTION
WHEN OTHERS THEN

  v_err_code    := SQLCODE;
  v_err_message := DBMS_UTILITY.FORMAT_ERROR_STACK()||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();  

  dbms_output.put_line( 'STRT');
  dbms_output.put_line(SQLCODE);
  dbms_output.put_line( 'STRTSTCK');
  uxpsubm.p_Log_Errors ( 'Error_Stack...' || Chr(10) || DBMS_UTILITY.FORMAT_ERROR_STACK() );
  dbms_output.put_line( 'STRTBTRC');
  uxpsubm.p_Log_Errors ( 'Error_Backtrace...' || Chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
  dbms_output.put_line( 'ENDD');

  raise_application_error(-20000, 'Error! '||v_err_message);

END t2;
/
show errors


--UNIX shell script calls a sql program catch_error.sql for running a procedure
$ cat catch_error.sh
#!/bin/ksh
#

if [ ${UIPW:-0} = 0 ];
then
    UIPW="/"
    fi
    a='D'
    echo $a
    export a
    #
    #       run sqlplus to print the report
    #
    echo "Starting sql script...."
    MSG=`sqlplus -s $UIPW @catch_error.sql $a | col -b |rmnl | sed -n '/STRT/,/ENDD/p'`
    echo $?
    echo "Completed sql script...."
    R_CD=`echo $MSG| awk '{print $2}'`
    echo $R_CD
    echo $MSG
    if [[ $R_CD -ne 0 ]]
    then
        echo "`date` Failure"
        exit 1
    else
        echo "`date` Success"
    fi

    exit $?

--SQL program catch_error.sql for running a procedure
$ cat catch_error.sql
--set timing on verify on termout on feedback on timing on
set heading off
whenever sqlerror exit 1
whenever oserror exit 2

set verify off

column a format a10


variable a varchar2(10);

begin

  select '&&1' into :a from dual;

end;
/

set serveroutput on size 100000

EXECUTE ops$xxxxx.t2(:a);


pause
exit

$


--run details of the UNIX Shell script program


$ sh catch_error.sh
D
Starting sql script....
0
Completed sql script....
-6502
STRT -6502 STRTSTCK Error_Stack... ORA-06502: PL/SQL: numeric or value error: character string buffer too small STRTBTRC Error_Backtrace... ORA-06512: at "OPS$xxxxx.T2", line 14 ENDD
Mon Jan 10 22:49:33 EST 2011 Failure


--end sample code
*/

Thanks
Rakesh