• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 971
  • Last Modified:

Filling a Varray

I need help with the TYPE Varray....

I have a parser that will send me data ....I need to grab those names from the parser and use them as parameters into a stored procedure or the parser code can call the stored procedure and pass this in as parameters...I thought something like this...

Create type target_list_t is Varrary(100) OF Varchar2(20);
Create OR replace procedure Get_Targets ( list_in IN target_list_t)
IS
Begin
FORALL listnum IN list_in..First list_in.LAST
   Select * from tABle where thing = list_in ( listnum);
END;

I have a PL/SQL book but I am unclear on HOW the arrary is filled....do I have to initialize EACH entry from 0-99?

Do I need an OUT statement?  To output the select statement results?
Would it be better to just have a list of parameters in the create procedure and then check to see if they are null ..if not create a select statment? Again do I need an OUT for the results?

I dont have access right now to any data so Im writing kinda blindly without being able to test on a system....
0
Tereza
Asked:
Tereza
1 Solution
 
seazodiacCommented:
The best way to do this is pass the comma-delimited string from your application to your stored procedure, inside which you have to code to parse the string, and initializ your array one by one in a loop.

don't expect to pass in a parameter AS ARRAY type from your application because they don't comply with ARRAY type in PL/SQL.

if you want your select results as output , of course you need a OUT parameter , to be more specific, a CURSOR TYPE parameter as OUT .

the signature of the stored procedure will look like this:

Create or replace package my_types AS
TYPE g_cursor_type IS REF CURSOR;
end;

Create OR replace procedure Get_Targets ( p_in_string IN VARCHAR2, p_cursor_out OUT my_types.g_cursor_type)
IS

---Processing and parsing the comma-delimited string p_in_string
---LOOP initialized v_array
--open the cursor with the select statement
end;
/
0
 
TerezaAuthor Commented:
thanks....;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now