I have department table (dept) and I have return a stored procedure to
return value -1 if the department exist but if user enter other than a
department number thats in the table it should return 1.
As I shown bellow... Stored procedure works fine when execute the
stored procedure with the exiting department number. When execute it
with non existing department number i get the fallowing error
1 create or replace procedure check_dno (deptNum number,
returnValue out number)
2 is
3 v_deptNum number;
4 begin
5 select deptno into v_deptNum from dept where deptno = deptNum;
6 if deptNum = v_deptNum then
7 returnValue := -1;
8 else
9 returnValue := 1;
10 end if;
11* end check_dno;
SQL> /
Procedure created.
SQL> variable gReturn number;
SQL> exec check_dno(80, :gReturn);
BEGIN check_dno(80, :gReturn); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "ANU.CHECK_DNO", line 5
ORA-06512: at line 1
Stored Procedure works for the departments that are in the dept table.
SQL> exec check_dno(20, :gReturn);
PL/SQL procedure successfully completed.
SQL> print :gReturn;
GRETURN
----------
-1
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
