?
Solved

Filling a Varray

Posted on 2003-12-03
2
Medium Priority
?
962 Views
Last Modified: 2007-12-19
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
Comment
Question by:Tereza
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 1000 total points
ID: 9869475
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
 

Author Comment

by:Tereza
ID: 9869494
thanks....;-)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question