RETUNR FUNCTION USING "TYPE" [message #689614] |
Thu, 22 February 2024 04:06 |
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 |
|
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 |
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
|
|
|
|