Select query causing exception

Question

When does the below sql function can cause an exception other than NO DATA FOUND? v_ExchangeRate is of type float.The datatype of rate column is NUMBER(14, 10) and this column can not contain NULL values.

BEGIN
SELECT rate
INTO v_ExchangeRate
FROM exchange_rate
WHERE currency_code = CurrencyCode
AND status        = 'A';

The data type of exchange_rate in database is NUMBER(14,10)

Edit Multiple rows cannot be returned by the where clause as the currency_code is the primary key.

Answer 1

Maybe I'm a little lost, but what is CurrencyCode? I'm guessing exchange_rate and ExchangeRate are tables in your database? It's confusing to have a column and a table named exchange_rate, unless that's part of the problem.

Answer 2

Paste your Full stored procedure, its really confusing exchange_rate table or column and currency code also. I feel same variable used for column id and table name gives exception

Answer 3

What problem are you trying to solve here, or is this just a theoretical question? If an exception is being raised and you are suppressing it with a WHEN OTHERS clause then remove that clause -- it's presence is a coding error (ie. a bug).

Answer 4

Possible exceptions (not an exhaustive list by any means):

1) ORA-00942: table or view does not exist

declare
  var integer;
begin
  select 1 into var from nosuchtable;
end;

2) ORA-06502: PL/SQL: numeric or value error: character to number conversion error

declare
  var integer;
begin
  select 'x' into var from dual;
end;

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

declare
  var varchar2(1);
begin
  select 'xx' into var from dual;
end;

4) ORA-01722: invalid number

SQL> create table t1 (n1 number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> declare
  2    var varchar2(1);
  3  begin
  4    select 'x' into var
  5    from t1 where n1 = 'y';
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-0512: at line 4

Can you not create a copy of the function, remove the WHEN OTHERS part, and test it in SQL Plus or an IDE to see what exception you get?

Answer 5

Doing an SQL trace MAY show the exception (if it raised by SQL rather than the PL/SQL).

Alternatively DBMS_TRACE has the ability to record exceptions even when they are caught by an exception handler. But you'd have to install the supporting tables.

D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 14 09:24:45 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> @tracetab.sql
SQL> CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
SQL> CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
SQL> CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
SQL> GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;

Now for a demo :

create or replace
procedure test_trace is
  v_test varchar2(3);
begin
  select '12' into v_test from dual;
  select '123' into v_test from dual;
  select '1234' into v_test from dual;
  select '12345' into v_test from dual;
exception
  when value_error then
    null;
end;
/

Run the test by clearing out the table of any old junk, set the flag and execute the procedure

delete from plsql_trace_events;
commit;
exec DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
exec test_trace;

Then query the results.

select event_kind, event_unit, event_line, stack_depth, excp, event_comment, callstack, errorstack
from plsql_trace_events
where event_kind not in (38,40,43,44)
order by event_seq;

 EVENT_KIND EVENT_UNIT                       EVENT_LINE STACK_DEPTH        EXCP
----------- ------------------------------- ----------- ----------- -----------
EVENT_COMMENT
----------------------------------------------------------------------------------
CALLSTACK
----------------------------------------------------------------------------------
ERRORSTACK
----------------------------------------------------------------------------------
      52.00 TEST_TRACE                             6.00        2.00    6,502.00
Exception raised
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3BF0F6D4         6  procedure GARY.TEST_TRACE
3BDF1764         1  anonymous block
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

      53.00 TEST_TRACE                            11.00        2.00    6,502.00
Exception handled

We can see an exception was raised at line 6 and the fact that it was caught at line 11. The latter is pretty important too. If you have some complex code, it isn't impossible that an exception handler a couple of levels up the call hierachy may 'handle' an exception for which it was never intended. You can even see the error number which is handy if it got caught by a WHEN OTHERS.