oracle plsql stored procedure error handling ora-6502

Question

create procedure about_emp(p_empno in number,p_ename out varchar2)
is
begin
select ename into p_ename from emp
where empno=p_empno;
exception
when no_data_found then
dbms_output.put_line('your id not available');
when value_error then
dbms_output.put_line('enter exact data');
end;

execution

variable x varchar2(10); exec about_emp(4520,:x);

X

michel

exec about_emp(1111,:x); you id not available

exec abot_emp('a',:x); ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

but normally in store procedure are display like ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.about_emp", line 4 ORA-06512: at line 1

please find the error and give me the solution

Answer 1

Well, either you have declared X variable too small to hold the value of empno.ename, so make it larger. If you also want to see the output from dbms_output in sqlplus, you need to enable: "set serveroutput on"

Answer 2

Procedure about_emp expects number data type as first formal parameter and varchar as second formal parameter.

create procedure about_emp(p_empno in number,p_ename out varchar2)

But while executing you are supplying varchar data type for the first formal parameter (where number is expected)

 exec abot_emp('a',:x); 

Oracle can't do implicit conversion of a to a number, so that is why you are getting error ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1 You should call this procedure supplying first actual parameter as number and second parameter as varchar (or compatible (or convertable) data types )

As per exec about_emp(1111,:x); you id not available This is just output from trapped exception

exception
when no_data_found then
dbms_output.put_line('your id not available');

It mean's that SELECT returned empty result set, there is no such data in the table.

UPDATE OP asked how to handle errors in PL/SQL

1st option - You need to associate error with the definition

PRAGMA EXCEPTION_INIT(invalid_implicit_conversion, -6789);

and then trap error in EXCEPTION section.

 EXCEPTION
      WHEN invalid_implicit_conversion 
      THEN ...

2nd option -

  EXCEPTION
          WHEN OTHERS // this will trap any exception
          IF SQLCODE = -6789 THEN ...