CODEWITHSUNDEEP
sqloracle-databaseexceptionplsql
Katherine
Katherine

Reputation: 573

PL/SQL - Why ORA-06550 can't be trapped in an exception?

I created a plsql program to get an employee_id from substitution variable. whenever i try to input a letter, i get an error message with ORA-06550 as the error number. I put it in the exception section but it seems like it wasn't being raised.

here's the error message when i input 'kk' in the substitution variable...

Error report:
ORA-06550: line 13, column 9:
PLS-00201: identifier 'KK' must be declared
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

and here's my pl/sql block

set serveroutput on
set verify off
declare
  cursor cur(mid employees.employee_id%type) is
  select e.last_name employee, 
         m.last_name manager 
    from employees e 
    join employees m 
      on m.employee_id = e.manager_id
   where m.employee_id=mid;

   rec cur%rowtype;
   m_id employees.employee_id%type;
   ex exception;
   pragma exception_init(ex, -06550);

begin
  m_id := &id;
  open cur(m_id);
  fetch cur into rec;
    dbms_output.put_line('here '||rec.employee || ' ' || rec.manager);
  close cur;

exception
  when ex then dbms_output.put_line('employee_id was not a valid number');
end;
/

do anyone knows why i cant trap that exception?

Upvotes: 1

Views: 6276

Answers (3)

Isha
Isha

Reputation: 31

Your code is absolutely correct. kindly use the employee ID instead of KK, which is in the table. It must be a number. For example 101 or 102

In where clause of the cursor you have use "m.employee_id=mid" and the datatype of employee_id is number.

Thanks:)

Upvotes: 1

Gumowy Kaczak
Gumowy Kaczak

Reputation: 1499

I wish I could comment. Can't you change your code to:

m_id := '&id';

so you won't need to trap that exception.

Update for catching number exception:

declare
    (...)
begin
    m_id := to_number('&id');
    (...)
exception
    when VALUE_ERROR then
         dbms_output.put_line('You provided invalid number'); 
end;

Upvotes: 1

Thilo
Thilo

Reputation: 262794

You cannot catch that exception at runtime, because this is a compile-time error.

m_id := &id;

Try typing 'KK' instead of KK, otherwise it will be interpreted as an identifier.

Upvotes: 4

Related Questions