I need to build Views in SQL Developer based on queries with unbound parameters.
Can I use SYS_REFCURSOR in a Function to return a Recordset that can be referenced by a View?
Example:
create or replace
FUNCTION aaaRelationships
(
UpdateMonth IN aaProjectList.WDMonth%type)
RETURN SYS_REFCURSOR
AS
Relationships_rc SYS_REFCURSOR;
BEGIN
OPEN Relationships_rc FOR SELECT TASKPRED.proj_id, aaProjectList.WDMonth FROM admuser.TASKPRED LEFT JOIN
(SELECT aaProjectList.proj_id, aaProjectList.WDMonth FROM aaProjectList
) aaProjectList ON TASKPRED.Proj_ID=aaProjectList.proj_id WHERE aaProjectList.WDMonth = UpdateMonth;
RETURN Relationships_rc;
END;
Result:
DECLARE
UPDATEMONTH VARCHAR2(32);
v_Return SYS_REFCURSOR;
BEGIN
UPDATEMONTH := NULL;
v_Return := AAARELATIONSHIPS(
UPDATEMONTH => UPDATEMONTH
);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
Thank you.