Re: PL/SQL Collections

From: <>
Date: Sun, 06 Jan 2008 17:54:49 GMT
Message-ID: <JB8gj.6731$9e1.5353@trnddc02>

"Mook" <> wrote in message
> Could someone please help me to better understand the application of
> collections in PL/SQL? Specifically, I wish to better understand when
> to use a record, table of records, and various arrays. Plus what are
> the trade-offs for applying one over another in various situations.
> Thanks!

The manual has a nice section on when to use which one and what the differences are.

Often collections are used as complex data types in a procedure call. A lot of times the Java developers like to use complex types on their end. If you have them call a stored procedure with the complex type(s) then you control how the database will handle the input and output. Also you can use a complex type to avoid having to have a huge number of parameters for your procedure. (eg you want to operate on a record, so pass in a record not 200 columns each a parameter) Also you can do some fancy processing of sets. For example, if you want to get a set of records in a search based upon some list of values and that list of values is dynamic a collection can help.

create or replace type t_mycollectiontype as table of number;

select * from mytable mt where mt.primarykey in (select COLUMN_VALUE from table(cast( input_collection as t_mycollectiontype)));

Your question is too broad; you are asking for a book. Either you have posted it in the hopes of someone doing a homework assignment or an interview question. It would help if you put more into your question. (eg. I was reading the thus and so manual in the section on collections and it says.... I think that section is rather vague. Can someone explain that section to me?

Jim Received on Sun Jan 06 2008 - 11:54:49 CST

Original text of this message