Package [message #319331] |
Fri, 09 May 2008 21:19 |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
What did I do wrong?
SQL> CREATE OR REPLACE PACKAGE Hospital1 AS
2 TYPE rec_type IS RECORD
3 (t_pat_Nbr Patient.Pat_Nbr%TYPE,
4 t_Trt_Procedure Treatment.Trt_Procedure%TYPE,
5 t_Phys_ID Physician.Phys_ID%TYPE,
6 t_Phys_Name Physician.Phys_Name%TYPE,
7 t_Phys_Specialty Physician.Phys_Specialty%TYPE);
8
9 TYPE t_patTrt IS TABLE OF rec_type
10 INDEX BY BINARY_INTEGER;
11
12 e_DupPhysFound EXCEPTION;
13
14 FUNCTION FindPatient(nbr IN Patient.pat_nbr%TYPE)
15 RETURN BOOLEAN;
16 END Hospital1;
17 /
Package created.
SQL> Show error;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY Hospital1 AS
2 --FindPatient by patient number
3 FUNCTION FindPatient(nbr IN Patient.pat_nbr%TYPE)
4 Return Boolean IS
5 v_nbr Patient.Pat_nbr%TYPE;
6 Begin
7 Select pat_nbr
8 Into v_nbr
9 From Patient
10 Where v_nbr = nbr;
11 IF SQL%FOUND THEN
12 Return True;
13 Else
14 Return False;
15 END IF;
16 END FindPatient;
17 END Hospital1;
18 /
Package body created.
SQL> Show error
No errors.
SQL> --Driver for FindPatient
SQL> set serveroutput on
SQL> Begin
2 if (Hospital1.FindPatient(3249)) then
3 dbms_output.Put_line('Patient is in database');
4 else
5 dbms_output.Put_line('Patient is not in database');
6 end If;
7 End;
8 /
Begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSTEM.HOSPITAL1", line 7
ORA-06512: at line 2
Best Regards
learnSQL
|
|
|
|
Re: Package [message #319340 is a reply to message #319333] |
Fri, 09 May 2008 22:56 |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
Thanks for your input ebrian. I don't know why No_DATA_FOUND exception before SLQ%FOUND. Do you have any idea.
SQL> select Pat_Nbr
2 from patient
3 where Pat_Nbr = 3249;
PAT_NBR
----------
3249
Best Regards
|
|
|
|
|
|
Re: Package [message #319444 is a reply to message #319331] |
Sun, 11 May 2008 06:17 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Anyway.
As Brian wrote
Quote: | The NO_DATA_FOUND exception is being raised before you can check SQL%FOUND.
|
so use:
FUNCTION FindPatient(nbr IN Patient.pat_nbr%TYPE)
Return Boolean IS
v_nbr Patient.Pat_nbr%TYPE;
Begin
Select pat_nbr Into v_nbr
From Patient
Where pat_nbr = nbr;
Return True;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Return False;
END FindPatient;
HTH/
|
|
|