Getting multiple values from a function

Question

I have my CLIENTS table with the fields: (PK:ID NUMBER), PRICE NUMBER, PAYMENT_TYPE_ID NUMBER, SESSION_AREA NUMBER I created a dynamic action from my page, in order to take the above values from CLIENTS table according to my :P2007_CLIENTS_ID.

First i created this type:

CREATE OR REPLACE EDITIONABLE TYPE  "PATIENT_DETAILS" as object
( payment_type number,
session_area number,
price number
)
/

then i created this function:

create or replace FUNCTION "F_PATIENT_DETAILS"
(patient_id in NUMBER, session_date date)
RETURN patient_details
IS

    v_payment_type number;
    v_session_area number;
    v_price number;

BEGIN

    SELECT CLIENTS.PAYMENT_TYPE_ID into v_payment_type
    FROM CLIENTS
    WHERE CLIENTS.ID = patient_id;

    SELECT CLIENTS.SESSION_AREA into v_session_area
    FROM CLIENTS
    WHERE CLIENTS.ID = patient_id;

    SELECT CLIENTS.PRICE into v_price
    FROM CLIENTS
    WHERE CLIENTS.ID = patient_id;

    if v_price is null then
        SELECT POLICIES.PRICE into v_price
        FROM POLICIES
        WHERE POLICIES.ACTIVE = 1 
        AND to_char(session_date, 'MM-DD-YYYY') BETWEEN POLICIES.START_DATE AND POLICIES.END_DATE;
    end if;

    return patient_details(v_payment_type, v_session_area, v_price);


END;

How do i get the values from this function in my page, with Dynamic Action? I tried this: Identification-> Set Value, Set Type -> PL/SQL Function Body:

declare
  My_Result PATIENT_DETAILS;
begin
  My_Result := F_PATIENT_DETAILS(:P2007_CLIENTS_ID, :P2007_SESSION_DATE);
  
end;

Items to Submit-> P2007_CLIENTS_ID, :P2007_SESSION_DATE Affected Elements -> P2007_PAYMENT_TYPE_ID, :P2007_SESSION_AREA, :P2007_PRICE

but nothing happens..!

Answer 1

Those three fields are never assigned the new values after your function is returned, eg:

:P2007_PAYMENT_TYPE_ID := my_result.payment_type;

Also, there is no reason for 3 separate queries on CLIENTS. You could do this in one motion.

SELECT c.PAYMENT_TYPE_ID, c.SESSION_AREA, c.PRICE 
into v_payment_type, v_session_area, v_price
FROM CLIENTS c
WHERE c.ID = patient_id;

Taking that a step further, you could coalesce c.price with a subquery on policies. Which has a questionable filter on a date being represented as a character. I doubt this would return accurate results.