Oracle exception information

Question

I'm wondering if there is a way to get a little more information about what caused the exception:

Error starting at line 5 in command:
exec ....
Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at ..., line 558
ORA-06512: at ..., line 752
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

I'm assuming this means I have a variable that is too small for what I tried to put in it... But why can't Oracle tell me what variable this is? It would be great for both debugging and production support people to be able to say to our client I think you have this field too big and that's what caused the error, rather than just "some value was too big and we really have no idea which one"... Using the line number would required someone who understood sql and looking at the code which is not ideal.

Why is there nothing under the Cause and Action fields?

When you try to insert into a col with data that is too large it tells you what col... I would like similar information here.

Is that possible without having to put an exception handler after every plsql line of code?

How would telling you the local PL/SQL variable name give you more information than telling you the line of code? Someone is still going to have to look at the code to determine what is being inserted into that variable in order to determine the root of the problem and the cause. Assuming your code uses anchored data types (i.e. declaring the variable `l_first_name person.first_name%type` rather than `l_first_name varchar2(100)`), buffer errors generally occur when you are combining data from multiple different columns and/or rows which further complicates things. — Justin Cave, Oct 21 '11 at 17:17
I have to parse some values. during the parsing I have to store the values in a variable. They are defined like `varcahr2(100)`. Lets say I am parsing Codes (just to make it easy) I might have a string like `'codeA,codeB,codeC'` and I have an array of `varchar(5)` objects called `CodeArray`. So I load the codes into the array. But If I get a Code that is like `'codeAA'` i will get an exception like above. If I know that the `CodeArray` variable caused the exception that will provide value. — kralco626, Oct 21 '11 at 17:23
@kralco626, so specifically catch an exception when in that block of code and raise your own error that has the information you want returned. — BQ., Oct 21 '11 at 17:37
you assuming im only parsing one string. If I have a loop that parses a number of strings, or a string that is like `code1#msg1%code2#msg2%code3#msg3` where i'm trying to parse the code and message out of the same string I won't know which value caused the error, only that it was caused in that block of code... — kralco626, Oct 21 '11 at 17:39

BQ. · Answer 1 · 2011-10-21T17:50:02.860

Oracle can't know what your intent is with the error information. Knowing the variable name you're storing it as or where exactly it came from isn't necessarily in the best interests of the end user or security.

For example, I can generate your error easily:

SQL> declare
  2  v_tooshort varchar2(3);
  3  begin
  4    select 'too long' into v_tooshort from dual;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

The error already gives the line number.

Would you prefer the error give the variable name (v_tooshort)? That's not helpful to a user.

Is the right information the value, "too long"? Or the fact that it's a dummy column from the dual table? (or an actual column and table)?

Because the error arises from a select into instead of an insert statement, it's not like there's a specific database constraint the exception can identify by name.

EDIT (to address issue raised in a comment): That's not true. You'll get the column name and lengths returned when doing an insert (as an ORA-12899), but not when doing a select into even if it's using data from a table:

SQL> create table test_length (tooshort varchar2(3));

Table created.

SQL> begin
  2    insert into test_length(tooshort) values ('too long');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12899: value too large for column "MYUSER"."TEST_LENGTH"."TOOSHORT"
(actual: 8, maximum: 3)
ORA-06512: at line 2

SQL> insert into test_length(tooshort) values ('abc');

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2    v_onechar varchar2(1);
  3  begin
  4    select tooshort into v_onechar from test_length;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

EDIT 2:

You can nest your select into into its own begin-exception-end block and raise whatever error you like (providing a unique error number and descriptive error text):

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    v_onechar varchar2(1);
  3  begin
  4    select tooshort into v_onechar from test_length;
  5  exception
  6    when value_error then
  7      RAISE_APPLICATION_ERROR(-20011, 'my variable named v_onechar is too short for the data returned from test_lengt
h.tooshort');
  8* end;
SQL> /
declare
*
ERROR at line 1:
ORA-20011: my variable named v_onechar is too short for the data returned from
test_length.tooshort
ORA-06512: at line 7

I see your point, but if it was a column I would at least get the information `expected length 3 got length 8`. Even that information would useful here... If it were a column I would also get the name of the col. Seems to make sense I would get the same of the variable here... — kralco626, Oct 21 '11 at 17:26
You can handle the error with an `exception` clause in your PL/SQL. My first edit attempted to show how the error your getting (ORA-06502) is reporting different information because it's a different error than the one providing the information you are expecting (ORA-12899). — BQ., Oct 21 '11 at 17:50

Oracle exception information

1 Answers1