Sqltype Table of Records - Operations and Java

You Asked

Hello Sir,
          I want to pass a Sqltype table of records to Java.
1)How to do populate values for such a record in plsql.
say from a select statement.
2) How to insert values in a table from such a table of records
3) How will java interpret that.

I see some examples on your site about passing a scalar sqltype like table of numbers or 
table of varchar But no example on passing table of records to Java.
Can u pls give one 

and we said...

I've never done a collection of objects in java -- and I won't be :)

I would use result sets -- period.  Least amount of code on all parts.  Consider:

create or replace type myScalarType as object
( x int,
  y varchar2(20),
  z date
)
/
create or replace type myTableType as table of myScalarType
/


create or replace function non_pipelined( p_like in varchar2 )
return myTableType
as
    l_data myTableType;
begin

    select myScalarType( user_id, username, created  )
      BULK COLLECT into l_data
      from all_users
     where username like p_like;

    return l_data;
end;
/

create or replace function is_pipelined( p_like in varchar2 )
return myTableType
pipelined
as
begin
    for x in ( select myScalarType( user_id, username, created  ) y
                 from all_users
                where username like p_like )
    loop
        pipe row(x.y);
    end loop;
    return;
end;
/

select * from TABLE( non_pipelined( '%A%' ) );
select * from TABLE( is_pipelined( '%A%' ) );


Just will just run the QUERY to run the procedure and a ResultSet to manipulate the 
output. 

 

you have a record.

it has three components.

it'll have three components today, tomorrow, forever (unless you drop and recreate it).

Just like a table -- if you have a table with three columns -- you have, well, three columns.


So, you could:

  6      select myScalarType( user_id, username, NULL  )
  7        BULK COLLECT into l_data

and then the person querying that would query

select x, y from TABLE.....


or you could:

  6      for x in ( select myScalarType( user_id, username, NULL  ) y
  7                   from all_users
  8                  where username like p_like )
  9      loop
 10          pipe row(x.y);
 11      end loop;



or you could create yet another type that has your two attributes. 

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10271519764319