Displaying Cursor Output

[Terry Rose]

I have functions that return values of a cursor that seems to run okay. However, I cannot see the output anywhere in PL/SQL Developer. Do I need to turn on some option to see the output?

 

[Claus Pedersen]

How do you output?

If you use dbms_output.put_line you can see your output in the Output tab-page in Test/SQL/Command-windows

If the functions are 'public' you can also show the values via SQL:

SELECT my_function(73, 'a')
FROM   dual;

Please send code examples.

 

[Terry Rose]

Originally posted by Claus Pedersen:
How do you output?

If you use dbms_output.put_line you can see your output in the Output tab-page in Test/SQL/Command-windows

If the functions are 'public' you can also show the values via SQL:

SELECT my_function(73, 'a')
FROM   dual;

Please send code examples.

 

[Terry Rose]

Thanks for your input. However, I am still having difficulties. The select statement doesn't work for some reason. I was trying to do it within a test script. The test script executes but the results do not show in the Output tab. The script content is:

begin
-- Call the function
:result := pkg_user_admin.existing_users(p_company => _company);
end;

result is a returned cursor with multiple entries where p_company is a single letter which is then used to form a string in a LIKE clause. I was hoping that I could I run the function as a test script and see the returned cursor values in a window within PL/SQL Developer. Is this possible?

 

[CTzen]

Terry,

to see results you have to click on '...' for value

Here is the source code to try

create or replace package pkg_data_types
is
    type ref_cursor_type is ref cursor;
end pkg_data_types;
/

create or replace function get_ref_cursor(p_query varchar2)
return pkg_data_types.ref_cursor_type
is
    l_result pkg_data_types.ref_cursor_type;
begin
    open l_result for p_query;
    return(l_result);
end get_ref_cursor;
/

-- Test SQL*Plus Version
variable v_ref_cursor refcursor
exec :v_ref_cursor := get_ref_cursor('select sysdate from dual');
print v_ref_cursor

 

[Terry Rose]

Slava,

Thank you very much. I see now how to do this.

Terry