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;
