Exception block in pl/sql 'Function' not catching error condition

Question

I have the following function which i call from another procedure within the same pl/sql package.

Function lf_get_query(p_table in varchar2) return varchar2
Is
    v_where_clause  varchar2(500);
Begin
    Case upper(p_table)
        When 'TABLEA'   then                    
            v_where_clause := ' Where trunc(x.purchase_dtm) < ' || '''' || trunc(v_check_Date) || ''''; 
        When 'TABLEB'   then                    
            v_where_clause := ' Where trunc(x.purchase_dtm) < ' || '''' || trunc(v_check_Date) || ''''
                                || ' And product_type='ABC'
                                || ' And customer_type='XXX'
                                || ' And contract_type='YYY';   
        Else
            raise ex_unknown_table_type;                    
    End case;           
    return v_where_clause;
Exception
    When ex_unknown_table_type then
        [[Log to file that table is urecognised. ]]
        raise;
    When others then
        [[Log to file that table is urecognised and include sqlerrm ]]
        raise;
End;

When i call the function, it generates the following error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The error is generated because the variable v_where_clause is not large enough for some of the strings i am trying to store in the variable. What i don't understand is when the error occurs, it is not caught by any of the two exception clauses shown above. Instead the error is being caught on the exception block for the procedure that is calling this function.

The reason i know it is not being caught by the exception clauses in the function is that both exception clauses are supposed to log the error condition to a file but they are not.

Is there any reason for this? Shouldn't the exception be caught by the 'WHEN OTHERS' exception block?

Also, is there any way i can declare the v_where_clause variable without specifying a size?

Thanks

Specify your VARCHAR2 variable to a very large size i.e. 32767 and Oracle will automatically give it just enough storage to cover what you put into it. it won't set aside 32767 bytes arbitrarily. — Ollie
– Ollie, Commented Nov 8, 2011 at 14:44

Allan · Accepted Answer · 2011-11-08 14:45:51Z

4

Is the exception block of the calling procedure issuing a rollback? If so, it will rollback your logging along with everything else. The way you get around this is to use a procedure that is defined with PRAGMA AUTONOMOUS_TRANSACTION, which will allow your logging to occur outside of the current transaction.

answered Nov 8, 2011 at 14:45

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ziggy Over a year ago

Oh im so stupid. The log entries that were logged in the table were being rolled back. Thanks!!!!!!!!

DCookie Over a year ago

Happens to all of us at least once ;-)

APC Over a year ago

+1 Writing log records is the only valid use for AUTONOMOUS TRANSACTION.

Collectives™ on Stack Overflow

Exception block in pl/sql 'Function' not catching error condition

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related