SYS_REFCURSOR as output parameter in a function

Question

I have a function like below. How do I print the output for this function. I tried something below. But its throwing too many values error

CREATE OR REPLACE FUNCTION PMDB_POSTGRES.Fn_test 
(
    pi_profile_name         IN      varchar2,
    po_ref_cursor           OUT     SYS_REFCURSOR,
    po_mesg_id              OUT     NUMBER,
    po_mesg_text            OUT     VARCHAR2
) RETURN NUMBER IS
    lv_stmt             VARCHAR2(4096);
BEGIN
    
    lv_stmt := 'select 1 as col1,2 as col2,3 as col3 from dual ';


        DBMS_OUTPUT.PUT_LINE('Prepared Stmt Is ');
        DBMS_OUTPUT.PUT_LINE(lv_stmt);

    OPEN po_ref_cursor FOR lv_stmt;
    RETURN 0;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
    RETURN 2;
END fn_test;

trying to print the cursor and the other two output variables

DECLARE
  l_cursor  SYS_REFCURSOR;
  l_cursor1  SYS_REFCURSOR;
  l_col1   varchar2(2000);
  l_col2   varchar2(2000);
  l_col3  NUMBER;
  l_po_mesg_id NUMBER;
  l_po_mesg_text varchar2(2000);
  l_po_mesg_id_op NUMBER;
  l_po_mesg_text_op varchar2(2000);
BEGIN
 DBMS_OUTPUT.pUT_LINE(2);  
    SELECT fn_test(
        'w', 
         l_CURSOR, l_po_mesg_id, l_po_mesg_text
        ) INTO l_cursor1, l_po_mesg_id_op, l_po_mesg_text_op
    FROM dual;
      DBMS_OUTPUT.pUT_LINE(1);      
  LOOP 
    FETCH l_cursor1
    INTO  l_col1, l_col2, l_col3;
    EXIT WHEN l_cursor1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_col1 || ' | ' || l_col2 || ' | ' || l_col3);
  END LOOP;
  CLOSE l_cursor1;
END;

Im getting the below error

SQL Error [6550] [65000]: ORA-06550: line 17, column 3:
PL/SQL: ORA-00913: too many values
ORA-06550: line 13, column 2:
PL/SQL: SQL Statement ignored

Answer 1

DECLARE
  l_cursor  SYS_REFCURSOR;
  l_cursor1  SYS_REFCURSOR;
  l_col1   varchar2(2000);
  l_col2   varchar2(2000);
  l_col3  NUMBER;
  l_po_mesg_id NUMBER;
  l_po_mesg_text varchar2(2000);
  l_po_mesg_id_op NUMBER;
  l_po_mesg_text_op varchar2(2000);
BEGIN
 DBMS_OUTPUT.pUT_LINE(2);  
      l_po_mesg_id_op := fn_test('w',l_CURSOR, l_po_mesg_id, l_po_mesg_text );
      DBMS_OUTPUT.pUT_LINE(1);      
  LOOP 
    FETCH l_cursor
    INTO  l_col1, l_col2, l_col3;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_col1 || ' | ' || l_col2 || ' | ' || l_col3);
  END LOOP;
  CLOSE l_cursor;
END;