|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
This
articles discusses the method for
returning TYPE return
values from functions in SELECT list.
This is particularly useful when you have some PLSQL code populate a
collection and you want to use that collection values through the use
of a simple select statement.
(Step 1) Create a SQL type
similar to the return type of the function :
create or replace type test_type as object (col3 number, col4 number);
/
(Step 2) Create a table over
the type :
create or replace type testarray_type as table of test_type;
/
(Step 3) In the example
below, XYZ is a normal relational heap organized table with columns
col3 and col4.
Create the function as
follows :
create or replace function
create_func(var_col in number) return testarray_type
is
localarray_type testarray_type;
begin
select test_type(col3,col4) bulk collect
into localarray_type from xyz where col3 = var_col;
return localarray_type;
end;
/
(Step 4) The select statement
will need to be like this :
select
col1,col2,col3,col4 from abc,table(cast(create_func(col1) as testarray_type));
NOTE : In the above
select statement, columns col1 and col2 belong to the relational table
abc while columns col3 and col4 are returned by the function as types.
Col1 from the table "abc" is passed to the function as an input
parameter. The joins between "abc" and the object returning function
are handled implicitly by oracle.
|
