Re: PL/SQL Collections

"Mook" <marcel.antawyn_at_gmail.com> wrote in message news:f0262fe8-804c-40a4-bdd5-14dde2c23851_at_21g2000hsj.googlegroups.com...
> 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.

Like:
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