Using Ref Cursor in Oracle SQL Developer
Solution 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
Solution 2
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.
Solution 3
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.
Solution 4
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.
Wade73
Updated on July 09, 2022Comments
-
Wade73 almost 2 years
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.
-
Wade73 over 14 yearsI made a modification above, since that doesn't seem to be working.