Raise_application_error

[steeve123]

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Using Forms:4.5.10.22.0

Problem:
I have a package that raise an error, here's the code in package:
Exception
When others then
raise_application_error(-20001,sqlerrm);
end;


Calling from sqlplus, here's the error:
*
ERROR at line 1:
ORA-20001: ORA-01438: value larger than specified precision allows for this column
ORA-06512: at "NCI.GCO_GENERAL", line 1287
ORA-06512: at "NCI.GCO_GENERAL", line 416
ORA-06512: at line 4

So the Error is clear, good, BUT, when that package is called from forms, the error is not clear, here's the output of major error variables of forms:
ERROR_TEXT = null
ERROR_TYPE = null
ERROR_CODE = 0
SQLERRM = ORA-20001:
SQLCODE = -20001
DBMS_ERROR_CODE = 0
DBMS_ERROR_TEXT = null


Conclusion: It's impossible to know the exact error that was raise from my package when the procedure is called from forms. To know the error, i have to call it from sqlplus to know the exact error.

Variable SQLERRM is different in forms than in sqlplus:
SQLPLUS: DBMS_OUTPUT.PUT_LINE(SQLCODE); = -20001
DBMS_OUTPUT.PUT_LINE(SQLERRM); = ORA-20001: ORA-01438: value larger
than specified precision allows for
this column

Forms:
SQLCODE = -20001
SQLERRM = ORA-20001:

Question:
1) How can i manage to show the real error that should raise in forms. The error that i want to be able to see is the following:
ORA-20001: ORA-01438: value larger than specified precision allows for this column. Exactly how i see it when i call the package from sqlplus.


2) Why the variable SQLERRM is different in sqlplus and forms.

[slimdave]

You are masking the error yourself with "WHEN OTHERS" in the exception clause. if you want to reveal the true error to the front end, then don't hide it with your own code.

[steeve123]

Indeed.

But in my exception, i have a series of error handling that i trap like when_no_data_find or dup_val_on_index, etc.. and post a user friendly message
But errors that happen once in a life time like that
ORA-01438: value larger than specified precision allows for this column

It was a number(6) and number was at 1000000

I would like that the error pops in the screen of the program.

[smsutcliffe]

You can use the EXCEPTION clause to raise friendly errors for the errors you handle. If you omit the WHEN OTHERS clause, any unhandled errors will be raised at the front-end.