Home » SQL & PL/SQL » SQL & PL/SQL » RETUNR FUNCTION USING "TYPE" (10G)
RETUNR FUNCTION USING "TYPE" [message #689614] Thu, 22 February 2024 04:06 Go to next message
emadnabil
Messages: 179
Registered: August 2007
Senior Member
Good Morning

I am using type in a function and want to return the value of the function into a form
but it fails

here is the type creation
create or replace type limitS as object
     ( lIMIT1 NUMBER,
       LIMIT2 NUMBER,
      LIMIT3 NUMBER
     )
here is the function you could ignore the details of the body
create or replace function CALCULATE_EMP_AMOUNT_MEDICINE(vEMP_ID in varchar, vCATEOGRY_ID NUMBER )

return LIMITS

is
vMEDICAL_PROGRAM NUMBER;
vLIMIT1 NUMBER;
vLIMIT2 NUMBER;
vLIMIT3 NUMBER;
vPROGRAM_ID NUMBER;
begin


-- GET MEDICAL PROGRAM OF THE EMPLOYEE
SELECT MEDICAL_PROGRAM INTO vMEDICAL_PROGRAM FROM RA_CUSTOMER_DETAILS_ALL RD
WHERE RD.CUSTOMER_ID = vEMP_ID;


-- GET PROGRAM ID FROM THE HEADER
SELECT ID INTO vPROGRAM_ID FROM MED_PROGRAMS_HEADER
WHERE PROGRAM_CODE = vMEDICAL_PROGRAM;

-- GET THE LIMIT OF THE PROGRAM
SELECT NVL(LIMIT1,0),NVL(LIMIT2,0),NVL(LIMIT3,0) INTO vLIMIT1,vLIMIT2,vLIMIT3 FROM MED_PROGRAMS_DETAILS
WHERE MED_PROGRAM_ID = vPROGRAM_ID
AND CATEOGRY_ID = vCATEOGRY_ID;


return LIMITS(vLIMIT1,vLIMIT2,vLIMIT3);
end CALCULATE_EMP_AMOUNT_MEDICINE;


here is the select from sql while test the function

select CALCULATE_EMP_AMOUNT_MEDICINE('00035310','7') from dual;

the result
CALCULATE_EMP_AMOUNT_MEDICINE(.LIMIT1	CALCULATE_EMP_AMOUNT_MEDICINE(.LIMIT2	CALCULATE_EMP_AMOUNT_MEDICINE(.LIMIT3
300	                                              650	                                  0
but when add it inside the form like the below
DECLARE 
vLIMIT1 NUMBER;
vLIMIT2 NUMBER;
vLIMIT3 NUMBER;

BEGIN
	
select CALCULATE_EMP_AMOUNT_MEDICINE('00000704','7') INTO  vLIMIT1,vLIMIT2,vLIMIT3 from dual;

END;
I got an error of type mismatch of vLimit
as the function returns 3 values but I cannot put them in the "INTO" in the form

how to handle this
Re: RETUNR FUNCTION USING "TYPE" [message #689615 is a reply to message #689614] Thu, 22 February 2024 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

DECLARE 
vLIMITS LIMITS;
vLIMIT1 NUMBER;
vLIMIT2 NUMBER;
vLIMIT3 NUMBER;

BEGIN
	
-- select CALCULATE_EMP_AMOUNT_MEDICINE('00000704','7') INTO  vLIMITS from dual;
-- but no need of SELECT
vLIMITS := CALCULATE_EMP_AMOUNT_MEDICINE('00000704','7');
vLIMIT1 := vLIMITS.LIMIT1;
vLIMIT2 := vLIMITS.LIMIT2;
vLIMIT3 := vLIMITS.LIMIT3;

END;

[Updated on: Thu, 22 February 2024 04:55]

Report message to a moderator

Re: RETUNR FUNCTION USING "TYPE" [message #689616 is a reply to message #689615] Thu, 22 February 2024 05:19 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can simplify that function a bit:
CREATE OR REPLACE FUNCTION calculate_emp_amount_medicine(vemp_id      IN ra_customer_details_all,customer_id%TYPE, 
                                                         vcateogry_id IN med_programs_details.cateogry_id%TYPE)
RETURN limits

IS

  vlimit1 med_programs_details.limit2%TYPE;
  vlimit2 med_programs_details.limit2%TYPE;
  vlimit3 med_programs_details.limit2%TYPE;

BEGIN

  -- Get the limits of the employees medical program
  SELECT nvl(mpd.limit1,0), nvl(mpd.limit2,0), nvl(mpd.limit3,0) 
  INTO vlimit1, vlimit2, vlimit3 
  FROM med_programs_details mpd
  JOIN med_programs_header mph ON mpd.med_program_id = mph.id
  JOIN ra_customer_details_all rd ON mph.program_code = rd.medical_program
  WHERE mpd.cateogry_id = vcateogry_id
  AND rd.customer_id = vemp_id;

  RETURN limits(vlimit1,vlimit2,vlimit3);
  
END calculate_emp_amount_medicine;
Also you should get into the habit of using %TYPE notation for parameter and variable declarations. I've put it in my modified example
Re: RETUNR FUNCTION USING "TYPE" [message #689710 is a reply to message #689614] Fri, 29 March 2024 07:08 Go to previous message
emadnabil
Messages: 179
Registered: August 2007
Senior Member
Many thanks for your support
Previous Topic: Getting same sequence number in select query
Next Topic: Implement Profile, PW Policy & Roles
Goto Forum:
  


Current Time: Tue Nov 12 19:06:57 CST 2024