SYS_REFCURSOR and dynamic SQL

Question

I am trying to create a form that returns fields and a table name to a procedure in my database where the procedure returns a sys_refcursor back to the form where it fetches the data and extract them into a csv or excel file.

Since the data is dynamic I am using a dynamic query stored into a variable and everywhere I look in the internet it shows the same way where this is possible, for example:

procedure xxx ( --- IN --- , result out sys_refcursor)
V_str = 'select * from customer';
open result for V_str;

But in the form it gives me the error, invalid cursor.

Here is the procedure:

PROCEDURE Original_Report (
    pQUERY                     VARCHAR2,
    pQUERY2                    VARCHAR2,
    pTABLE                     VARCHAR2,
    pReportCode                NUMBER,
    P_OUT_HEADER               OUT VARCHAR2,
    lRESULT                    OUT SYS_REFCURSOR)
IS
    str     VARCHAR2(3000);
BEGIN
    --  update  REPORT_AUTOMATION set RAU_REP_HEADER = pQUERY
    -- where RAU_REP_CODE = 10;
    -- commit;
                
    BEGIN
        SELECT  RAU_REP_HEADER
        INTO    P_OUT_HEADER
        FROM    REPORT_AUTOMATION
        WHERE   RAU_REP_CODE = PREPORTCODE;
    EXEPTION
        WHEN OTHERS THEN
            P_OUT_HEADER := NULL;
    END;

    str :=  'SELECT '||pQUERY2||' FROM ' ||pTABLE;
    open lRESULT for str;

END Original_Report;

And here is the form that calls it:

FUNCTION EXPORT_TO_CSV2 RETURN NUMBER IS

    L_CURSOR        SYS_REFCURSOR;
    V_RECORD        VARCHAR2(32000);
    APPLICATION     CLIENT_OLE2.OBJ_TYPE;
    WORKBOOKS       CLIENT_OLE2.OBJ_TYPE;
    WORKBOOK        CLIENT_OLE2.OBJ_TYPE;
    WORKSHEETS      CLIENT_OLE2.OBJ_TYPE;
    WORKSHEET       CLIENT_OLE2.OBJ_TYPE;
    ARGS            CLIENT_OLE2.LIST_TYPE;
    CELL            CLIENT_OLE2.OBJ_TYPE;
    SELECTION       CLIENT_OLE2.OBJ_TYPE;
    COLUM           CLIENT_OLE2.OBJ_TYPE;
    INTERIOR        CLIENT_OLE2.OBJ_TYPE;
    OUT_FILE        CLIENT_TEXT_IO.FILE_TYPE;
    FILENAME        VARCHAR2(200);
    RET             NUMBER;
    V_LINE          VARCHAR2(32000);
    I               NUMBER(10):=0;

BEGIN
    -- Get File path and name
    filename := webutil_file.file_save_dialog(
        directory_name => null,
        file_name      => 'Original_Report'||to_char(sysdate,'yyyymmdd')||'.csv',
        file_filter    => 'CSV Files (*.csv)|*.csv|',
        title          => 'Select File Name'
        );

    if filename is not null then
        out_file := client_text_io.fopen(filename,'w');
    
        MXP.MEPS_FORMS_REPORTS_SCRIPTS.Original_Report(  
            pQUERY       => :QUERY_ITEMS,
            pQUERY2      => :QUERY_ITEMS2,
            pTABLE       => :RAT_TABLE_NAME,
            pReportCode  => 10,
            P_OUT_HEADER => v_line,
            lRESULT      => l_cursor
            );

        client_text_io.put_line(out_file,v_line);
        v_line := null;
        LOOP
            FETCH l_cursor INTO V_RECORD;
            EXIT WHEN l_cursor%NOTFOUND;
            
            v_line := V_RECORD;
            client_text_io.put_line(out_file,v_line);        
            i:=i+1;
            if i > 300 then
                synchronize;
                i := 0;
            end if;        
        END LOOP;
        client_text_io.fclose(out_file);
        RETURN 1;
    ELSE
        RETURN 2;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        CLIENT_TEXT_IO.FCLOSE(OUT_FILE);
        RET := MSGBOX(SQLERRM);
        RETURN 0;
END;

Answer 1

Turns out that the best way I saw fit was to create a new table and to insert the result of the query (executed by execute immediate) into 1 field in that table and adding another field and giving it a sequence number that adds one every time data is entered, then using a cursor to call that table with the maximum sequence number!