Using Ref Cursor in Oracle SQL Developer

Question

I am using Oracle SQL Developer, but I am having an issue seeing results from a package that returns a ref cursor. Below is the package definition:

CREATE OR REPLACE package instance.lswkt_chgoff_recov
as
      type rec_type is record
            (
            source_cd                       lswk_tpr.gltrans.tpr_source_cd%TYPE,
            as_of_dt                        lswk_tpr.gltrans.tpr_as_of_dt%TYPE,
            chrg_off_recov                  varchar2(5),
            process_dt                      lswk_tpr.gltrans.dtgltran%TYPE,
            effect_dt                       lswk_tpr.gltrans.dtgltran%TYPE,
            account_nbr                     lswk_tpr.contract.lcontid%TYPE,
            naics_cd                        lswk_tpr.udfdata.sdata%TYPE,
            prod_type                       varchar2(20),
            off_nbr                         lswk_tpr.schedule.sctrcdty%TYPE,
            borrower_nm                     lswk_tpr.customer.scustnm%TYPE,
            tran_type_cd                    lswk_tpr.gltrans.sglcd%TYPE,
            tran_type_desc                  lswk_tpr.gltrans.sglcd%TYPE,
            tran_amt                        lswk_tpr.gltrans.ctranamt%TYPE,
            note_dt                         lswk_tpr.schedule.dtbk%TYPE,
            accru_cd                        number,
            non_accr_cd                     lswk_tpr.schedule.dtlstincsus%TYPE,
            comm_sb_ind                     varchar2(4)
            );

      type cur_type is ref cursor return rec_type;

      procedure sp
            (
            p_as_of_dt              in      date,
            ref_cur                 in out  cur_type
            );
end;
/

I guess the question is this possible and if so, what do I need to do. I am using Oracle SQL Developer 1.5.5. Thanks.

Wade

Here is the code I used to call my package (generated by TOAD):

DECLARE 
  P_AS_OF_DT DATE;
  REF_CUR instance.LSWKT_CHGOFF_RECOV.CUR_TYPE;
  REF_CUR_row REF_CUR%ROWTYPE;

BEGIN 
  P_AS_OF_DT := '31-AUG-2009';

  instance.LSWKT_CHGOFF_RECOV.SP ( P_AS_OF_DT, REF_CUR );

  DBMS_OUTPUT.Put_Line('REF_CUR =');
  IF REF_CUR%ISOPEN THEN
  DBMS_OUTPUT.Put_Line('  SOURCE_CD  AS_OF_DT  CHRG_OFF_RECOV  PROCESS_DT  EFFECT_DT  ACCOUNT_NBR  NAICS_CD  PROD_TYPE  OFF_NBR  BORROWER_NM  TRAN_TYPE_CD  TRAN_TYPE_DESC  TRAN_AMT  NOTE_DT  ACCRU_CD  NON_ACCR_CD  COMM_SB_IND');
    LOOP
      FETCH REF_CUR INTO REF_CUR_row;
      EXIT WHEN REF_CUR%NOTFOUND;
      DBMS_OUTPUT.Put_Line(
           '  ' || '[TPR_SOURCE_CD%type]'
        || '  ' || '[TPR_AS_OF_DT%type]'
        || '  ' || '''' || REF_CUR_row.CHRG_OFF_RECOV || ''''
        || '  ' || '[DTGLTRAN%type]'
        || '  ' || '[DTGLTRAN%type]'
        || '  ' || '[LCONTID%type]'
        || '  ' || '[SDATA%type]'
        || '  ' || '''' || REF_CUR_row.PROD_TYPE || ''''
        || '  ' || '[SCTRCDTY%type]'
        || '  ' || '[SCUSTNM%type]'
        || '  ' || '[SGLCD%type]'
        || '  ' || '[SGLCD%type]'
        || '  ' || '[CTRANAMT%type]'
        || '  ' || '[DTBK%type]'
        || '  ' || NVL(TO_CHAR(REF_CUR_row.ACCRU_CD), 'NULL')
        || '  ' || '[DTLSTINCSUS%type]'
        || '  ' || '''' || REF_CUR_row.COMM_SB_IND || '''');
    END LOOP;
  ELSE
    DBMS_OUTPUT.Put_line('  (Ref Cursor is closed)');
  END IF;


  COMMIT; 
END;

I get the error:

ORA-06502: PL/SQL: numeric or value error

Hope this clears it up a bit more.

Answer 1

You can easily print the output results of a ref_cursor in SQL Developer to see the return value..

Here's an example of a Refcursor Function:

create or replace function get_employees() return sys_refcursor as
  ret_cursor sys_refcursor;
begin
  open ret_cursor for
    select * from employees;
  return ret_cursor; 
end get_employees;

Quick and dirty method:

select get_employees() from dual; 

Neat and tidy method:

variable v_ref_cursor refcursor;
exec :v_ref_cursor := get_employees(); 
print :v_ref_cursor

Answer 2

The only explicit value that I see in the generated program is

 P_AS_OF_DT := '31-AUG-2009';

Try a an explicit conversion (to_date ('31-AUG-2009', 'DD-MON-YYYY') instead, maybe that gets rid of the problem.

If that doesn't help, can you see if your error is generated in the sp or in yor code? If you can't figure this out directly, define an sp from the code you have, set a breakpoint and step through the code to see where the error comes from.

Answer 3

Just make a loop which iterates through the ref cursor returned. You can output to the console using DBMS_OUTPUT.PUT_LINE() and choosing specific fields to show.

Answer 4

There's no way to tell without seeing what the cursor's query is. Take a look at the SELECT statement you're running in the procedure SP. One of the column's you are selecting into a numeric field in rec_type is returning character data, which cannot be converted to a number.

Instead of trying to figure out how to output the cursor, take the SELECT statement from sp and run it standalone. Look down the results you get. You're going to be looking for some non-digit values coming back in one of the fields where you expect a number.

Answer 5

In your comment you sayHere is the error:

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 16

Whatever it may appear like sometimes, PL/SQL errors are not randomly generated. This error points to a flawed data type conversion which occurs at line 16 of your procedure. Without seeing your whole procedure it is not possible for us to pinpoint line 16. Fortunately the Code Editor in SQL Developer will put line numbers in the gutter; if you are not seeing line numbers you will need to toggle a preference.

What you need to look for is a string being cast to a number or date variable, or a number being cast to a date field. This may be signalled by an explicit TO_NUMBER() or TO_DATE, in which case you need to check the format mask and/or the data content. Alternatively you may have an implicit cast. In that case you may need to make it explicit, with the appropriate format mask. Of course it could be an accidental and unwanted conversion because the projection of the SELECT statement doesn't match the signature of the REF CURSOR record. That is easy to fix.

Answer 6

If you have a procedure which requires a refcursor in the signature of the proc, you can do this:

var rc refcursor;
execute <package>.my_proc(:rc);
print rc;

Highlight and hit F5.