Toad World® Forums

Is there a way to display my stored procedure's output to data grid?

Hi all,

I have a simple oracle SQL as below

+++++

select distinct table_name from user_tab_columns where table_name like ‘%’||table_like||’%’ order by table_name;

+++++

I created a procedure with a VARCHAR2 parameter as an input value for table_like above.

I can use DBMS_OUTPUT.PUT_LINE to print the result but it only shows on DBMS output panel.

Is there a way I can make some changes to directly display the results to Toad’s data grid panel?

Any sugggestion is appreciated.

thanks!!

-Tian

If you have flexibility to modify your procedure to include a cursor out parameter you can do something like the code below. This may or may not be the best method, but it’s the only way I can think of to get PL/SQL data into a Toad data grid. Similarly you can also execute the procedure using the Execute PL/SQL button on toolbar and Toad will automatically create the output grid for you. This eliminates the need for the anonymous block wrapper and bind variable.

    -- Simple procedure to return cursor as output parameter
CREATE OR REPLACE PROCEDURE proc_out_cur (OUT_RES OUT SYS_REFCURSOR)
AS
BEGIN
OPEN OUT_RES FOR SELECT * FROM user_tables;
END;
-- Call procedure from anonymous block using bind var for the
-- cursor output. When prompted by Toad choose CURSOR as the
-- variable's datatype.
BEGIN
proc_out_cur (:res);
END;