view resultset of refcursor returning function

[Vish Thalakola]

I have a function that returns a resultset
as a refcursor.

How can I view this data in
pl/sql developer tool.

thanks.

 

[Cisco]

Use a Test window.
Then do the following

select yourfunctionname() into :thedata from dual;

Then sacn variables and set thedata as a cursor

Press F8
Then open the thedata varibale to see the data

 

[Stew Stryker]

I'd like to add some more details to this instruction, for those who might have trouble figured this out, as I did. I read through it about 10 times allowed and looked at the test window each time, but couldn't figure how to Open the cursor. My fault for being blind!

1) In the test window, type in the SELECT above.

2) At the bottom of the window, to the left of where you see the column header 'Variable', click the down arrow to scan for test window variables.

You should now see the variable *thedata* defined as a cursor variable.

3) Execute the test window, so it returns your cursor.

4) When it's done running, look at the line the variable *thedata* is on, then look to the FAR RIGHT of that line, where you should see a tiny set of elipses (...). This is a button. Click it to view your result cursor.

All the best.

 

[Stew Stryker]

I want to save or review the cursor result from a stored procedure I'm testing. I have it set up to run in a Test window. And I can view the cursor by clicking the ellipses (see above).

But I can't seem to walk through the cursor in that test procedure.

Has anyone got a suggestion about how I can get the following code sample to work? The procedure returns a ref cursor, which maps to a table I've created (see declaration section).

DECLARE
    TYPE my_dcf_cur_t IS REF CURSOR RETURN pf_by_sol_std%ROWTYPE;
    my_dcf_cur my_dcf_cur_t;
    my_dcf_rec my_dcf_cur%ROWTYPE;
BEGIN
    :rec_count := 0;
    -- Solicitor_id_in: 0000062482  - has more than 10 records in aeo_dcf_parent table
    aeo_dcf_parent_fund.dcf_parent_fund_by_solicitor(title1_in            => :title1_in,
                                                     solicitor_id_in      => :solicitor_id_in,
                                                     ex_fund_omit_in      => :ex_fund_omit_in,
                                                     ex_act_no_solicit_in => :ex_act_no_solicit_in,
                                                     ex_hold_in           => :ex_hold_in,
                                                     giv_info_in          => :giv_info_in,
                                                     dcf_cur              => :dcf_cur);

    my_dcf_cur := :dcf_cur;
    WHILE my_dcf_cur%ISOPEN
    LOOP
        FETCH my_dcf_cur
            INTO my_dcf_rec;
        IF my_dcf_cur%FOUND
        THEN
            :rec_count := :rec_count + 1;
        END IF;
    END LOOP;
    CLOSE my_dcf_cur;
END;

Thanks!

 

[Marco Kalter]

What exactly goes wrong?

 

[Stew Stryker]

What's wrong?

I AM A BIG DOPE!

I screwed up the WHILE loop, forgetting to do the first FETCH before, and then I should have tested for %FOUND, not %ISOPEN.

Darn, I've made that same error of not incrementing a loop since grade 10. You'd think I'd learn!

:-(

Sorry to have wasted your time. Though I guess you got entertainment value out of it. :-/

 

[Marco Kalter]

What's wrong?

I AM A BIG DOPE!

 

[Stew Stryker]

Marco,

Glad you enjoyed it.

A pox on you all for not noticing and pointing out the bug!