This is exactly as it should behave. It is an exception for a select statement to not return a value at all (remember that this is different than returning null).
You should trap the NO_DATA_FOUND exception similarly to this example from the searchable Oracle Documentation that you can find at http://tahiti.oracle.com DECLARE Emp_number INTEGER := 9999; Emp_name VARCHAR2(10); BEGIN SELECT Ename INTO Emp_name FROM Emp_tab WHERE Empno = Emp_number; -- no such number DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number); END; JNewMember wrote: > 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 -~----------~----~----~----~------~----~------~--~---
