Output results of Oracle stored proc from SQL Developer

I'm trying to call an Oracle stored proc using SQL Developer. The proc outputs results using a sys_refcursor. I right click in the proc window which brings up the Run PL/SQL window. When I choose the proc I want it creates all the input params etc for me. Below is the code I'm using to try and loop through the sys_refcursor and开发者_开发百科 output the results, but I'm getting an error on the 'v_rec v_Return%rowtype;' line :

ORA-06550: line 6 column 9: PLS-00320: the declaration of the type of this expression is incomplete or malformed. ORA-06550: line 6 column 9: PL/SQL: Item ignored

vendor code 6550

I found the looping code on a couple of other websites and it seems to be the way to do it but it's not working for me no matter what I try. Another question - on the DBMS_OUTPUT.PUT_LINE('name = ' || v_rec.ADM) am I referencing the v_rec correctly i.e. is v_rec."column_name" the correct way??

I'm not that used to Oracle and have never used SQL plus. Any suggestions appreciated.

DECLARE
  P_CAE_SEC_ID_N NUMBER;
  P_PAGE_INDEX NUMBER;
  P_PAGE_SIZE NUMBER;
  v_Return sys_refcursor;
  v_rec v_Return%rowtype;
BEGIN
  P_CAE_SEC_ID_N := NULL;
  P_PAGE_INDEX := 0;
  P_PAGE_SIZE := 25;

  CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
    P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
    P_PAGE_INDEX => P_PAGE_INDEX,
    P_PAGE_SIZE => P_PAGE_SIZE,
    P_FOF_SEC_REFCUR => v_Return
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = ');
  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('name = ' || v_rec.ADM);
  end loop;

END;

Your problem is here:

v_Return sys_refcursor;
v_rec v_Return%rowtype;

v_Return is a cursor variable and has no specific structure (list of columns), so v_Return%rowtype is not a valid record structure to declare v_rec. It is even possible for different calls to the procedure to return cursors with different structures.

You know what you are expecting the structure of the returned cursor to be (but Oracle doesn't) so you need to explicitly define the appropriate record structure e.g.

type t_row is record (empno number, ename varchar2(30));
v_rec t_row;

You need a strongly typed ref cursor to be able to define it as a %ROWTYPE.

Example here

@Tony Andrews thanks for this it gave me a better idea where I was going wrong. Still having problems though - here's a shortened version of my proc. It's a bit complex in that it's selecting all fields from a subquery and 2 other values:

open p_fof_sec_refcur for    

SELECT *
FROM( 
    SELECT securities.*, rownum rnum, v_total_count 
    FROM
        (
        SELECT
          CFS.CAE_SEC_ID,
          CFS.FM_SEC_CODE,
          ...
        FROM
        CAEDBO.CAE_FOF_SECURITY CFS
        INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
            ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
        ...
        WHERE APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
        ...
          )securities
      )   

  WHERE rnum between v_pgStart and v_pgEnd;

I explicitly defined the output structure as below to match the return fields from the proc but I'm still getting an error:

v_Return sys_refcursor;
type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),...rnum number, v_total_count number);
v_rec t_row;

The error I get is

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at line 45

I'm just wondering is the "rownum rnum, v_total_count" part tripping me up. I'm pretty sure I have all the other fields in the output structure correct as I copied them directly from the proc.