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!

Mook wrote:
Some of the best explanations can be found at asktom.oracle.com by
looking up "Collections."
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Mook wrote:
Some of the best explanations can be found at asktom.oracle.com by
looking up "Collections."
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Mook wrote:
Some of the best explanations can be found at asktom.oracle.com by
looking up "Collections."
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Mook wrote:
Some of the best explanations can be found at asktom.oracle.com by
looking up "Collections."
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"Mook" <marcel.antawyn (AT) gmail (DOT) com> wrote

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

"Mook" <marcel.antawyn (AT) gmail (DOT) com> wrote

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

"Mook" <marcel.antawyn (AT) gmail (DOT) com> wrote

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

"Mook" <marcel.antawyn (AT) gmail (DOT) com> wrote

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