How can I execute a function from TOAD for Oracle and bind the result to a data grid

Question

I have a function that takes pl / sql objects as one of the VARRAY arguments. How to execute this stored procedure and link the result set that it returns to the data grid in TOAD for Oracle?

Answer 1

After some searching, I found the answer to my problem. Say your varray type was called varchar_pair_array, and the objects stored in this array were called varchar_pair_object. varchar_pair_object is a simple object that has two varchars as members.

Here is the code for executing the proc that takes in varray_pair_object (s):

DECLARE RetVal SYS_REFCURSOR; a_simplevalue VARCHAR2(200); another_simplevalue VARCHAR2(200); my_array_of_varchar_pairs VARCHAR_PAIR_ARRAY; -- assume varchar_pair_array is defined somewhere else my_obj VARCHAR_PAIR_OBJECT; -- assume varchar_pair_object is defined somewhere else my_other_obj VARCHAR_PAIR_OBJECT; BEGIN a_simplevalue := 'hello'; another_simplevalue := 'there'; my_obj := VARCHAR_PAIR_OBJECT('nice to meet you', 'greetings'); my_other_obj := VARCHAR_PAIR_OBJECT('goodbye', 'ciao'); my_array_of_varchar_pairs := VARCHAR_PAIR_ARRAY(); my_array_of_varchar_pairs.EXTEND(2); -- this should be the number of objects you plan to put into the array my_array_of_varchar_pairs(1) := my_obj; my_array_of_varchar_pairs(2) := my_other_obj; RetVal := my_function ( a_simplevalue, another_simplevalue, my_array_of_varchar_pairs); -- assuming your array takes two varchars and one array of VARCHAR_PAIR_OBJECT (s) :to_grid := RetVal; END; 

Copy this code into the TOAD text editor and modify it to adapt to your function and types and press F9. TOAD will ask you about the type of variable: to_grid. Select the cursor (if your function returns the cursor) and press enter. TOAD will bind the result set to the data grid.

Links that helped me:

http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-11.htm (good collection tutorial) http://download.oracle.com/docs/cd/B10501_01/appdev .920 / a96624 / 10_objs.htm # 1972 (the section on declaring and initializing objects is especially useful in this case)

With very little change, the same can be done with the procedure.