Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Return Recordset with Function

2742137Oct 12 2016 — edited Oct 12 2016

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 9 2016
Added on Oct 12 2016
6 comments
1,143 views