Home » SQL & PL/SQL » SQL & PL/SQL » Package (SQL*Plus, 10, XP)
Package [message #319331] Fri, 09 May 2008 21:19 Go to next message
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 #319333 is a reply to message #319331] Fri, 09 May 2008 21:50 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The NO_DATA_FOUND exception is being raised before you can check SQL%FOUND.
Re: Package [message #319340 is a reply to message #319333] Fri, 09 May 2008 22:56 Go to previous messageGo to next message
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 #319344 is a reply to message #319331] Fri, 09 May 2008 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-06512: at "SYSTEM.HOSPITAL1", line 7
-----------------------^^^^^^
Are you really running this as user "SYSTEM"?

[Updated on: Fri, 09 May 2008 23:17] by Moderator

Report message to a moderator

Re: Package [message #319346 is a reply to message #319344] Fri, 09 May 2008 23:25 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Yes anacedent.
Re: Package [message #319348 is a reply to message #319331] Sat, 10 May 2008 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
  7        Select pat_nbr
  8        Into v_nbr
  9        From Patient
 10        Where v_nbr = nbr;

v_nbr is null at this moment so this will never return a row.

I think this might be "Pat_Nbr" instead of "v_nbr".

In addition, NEVER use SYSTEM (or worst SYS) for your objects, create your own user.

Regards
Michel
Re: Package [message #319444 is a reply to message #319331] Sun, 11 May 2008 06:17 Go to previous message
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/

Previous Topic: monitor files
Next Topic: buffers cleaning
Goto Forum:
  


Current Time: Sun Nov 03 08:08:42 CST 2024