Skip to Main Content
  • Questions

  • Sqltype Table of Records - Operations and Java

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 08, 2003 - 11:15 pm UTC

Last updated: May 23, 2009 - 12:55 pm UTC

Version: 9.2

Viewed 10K+ times! This question is

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 Tom 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.

Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

New PL/SQL syntax

A reader, June 09, 2003 - 9:03 am UTC

I haven't been keeping up to speed with the last PL/SQL stuff since 8.1.5 (early 1999). I am amazed by all these new syntax, such as BULK COLLECT and PIPELINED you used in your sample code. I am still using the good old myTableType(index).field := xxxx in a loop. I hope I am not paying any performance penalty.

BTW, here is my 2 cents on using collection of Java objects: java.sql.ResultSet IS fairly easy to use, but since it does not implement java.io.Serializable, it is difficult to pass a ResultSet between tiers (web and business). Plus ResultSet is connection-based so you need to have a connection open - not a good idea. You may want to use the RowSet interface, but a more typical pattern is to use a collection of DTO - data transfer objects.

perfect : Q about selective select

A reader, June 10, 2003 - 12:52 am UTC 

How about selecting only x and y sclar values from the  table of records for populating the functions both piped and non piped.Just as we select few columns from a table.
How to do that for table of records.
 How should we do that.
Wrote file afiedt.buf

  1  create or replace function selective_non_pipelined( p_like in varchar2 )
  2  return myTableType
  3  as
  4      l_data myTableType;
  5  begin
  6      select myScalarType( user_id, username  )
  7        BULK COLLECT into l_data.user_id ,l_data.username
  8        from all_users
  9       where username like p_like;
 10      return l_data;
 11* end;
 12  /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION SELECTIVE_NON_PIPELINED:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
6/12     PL/SQL: ORA-02315: incorrect number of arguments for default
         constructor

SQL> ed
Wrote file afiedt.buf

  1  create or replace function selective_is_pipelined( p_like in varchar2 )
  2  return myTableType
  3  pipelined
  4  as
  5  begin
  6      for x in ( select myScalarType( user_id, username  ) y
  7                   from all_users
  8                  where username like p_like )
  9      loop
 10          pipe row(x.y);
 11      end loop;
 12      return;
 13* end;
SQL> /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION SELECTIVE_IS_PIPELINED:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/14     PL/SQL: SQL Statement ignored
6/21     PL/SQL: ORA-02315: incorrect number of arguments for default
         constructor

10/9     PL/SQL: Statement ignored
10/18    PLS-00364: loop index variable 'X' use is invalid

Tom Kyte
June 10, 2003 - 8:19 am UTC

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.

Thanx much

A reader, June 10, 2003 - 11:01 am UTC

You have made Oracle so simple to understand.

Can we declare an object type in a package

jo sat, May 10, 2004 - 5:00 am UTC

Tom

In your example, can we declare an object type in package instead of a DDL of "create type"?

Thanks
Jo

Tom Kyte
May 10, 2004 - 8:08 am UTC

no, they must be visible to SQL (the types)

Some additional queries

sudip, September 13, 2004 - 3:00 am UTC

If I wanted to pass an n dimensional array from Java and use it to populate into a table of an obeject type with n columns ( SQLTYPE) . Can I do the same. I searched the site, everywhere the example is for a one dimensional array .
My other question is can I pass two such arrays as parameters to the function when it gets called from a java pgm.
I don't know much of Java , but wanted to convince people that best thing to do is to pass thier n dimensional array to data base , compute the data and store it back - instead of making n calls to database and saving n records of the array.

A code snippet where java makes a call to a function which accepts a n dimensional array would be of great help. We are working with 9i R2.
Thanks much for your time.

Tom Kyte
September 13, 2004 - 8:03 am UTC

you would pass an object type from java to plsql.

see the JPUB guides available on otn.oracle.com -- it maps sql object types to java classes.

you can pass as many arrays as you like -- if you can pass one, you can pass 50.



Mapping PL/SQL table with java ARRAY

Thierry Nestelhut, December 28, 2005 - 7:25 pm UTC 

Thank you for your very useful document.

I have tried it and it works almost perfectly.

Nevertheless, I have a problem when the return array is too big (and in fact no soo big).
I have got the following message :

SQL> @test.sql
ERROR:
ORA-03114: not connected to ORACLE


declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


ORA-24323: value not allowed
Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-03114: not connected to ORACLE

Do you know how to solve it.
Thank you.

Tom Kyte
December 28, 2005 - 8:16 pm UTC

ora-
3113
7445
600

all imply "contact support"

Logs

Wolfgang, December 29, 2005 - 10:04 am UTC

Hi,

have a look at the alert log and the dumps. We also had some ORA-03113: end-of-file on communication channel errors and we dind't know why they came up.
Then I accidentally noticed that there where lage dumps and we saw that there where Unique constraint violations, rollbacks and the application has savely ignored everything.
You know something like that:
try
{
...
}
catch Exception e
{
//Do nothing and nobody will make any trouble...
}

:-(

Regards
Wolfgang

Mapping PL/SQL table with java ARRAY

Thierry Nestelhut, December 29, 2005 - 2:37 pm UTC

I have this problem only with Oracle 8.1.7.
In fact, if the Array is greater than 1Kb, I have the message "end-of-communication channel".

I tried on another AIX server with Oracle 9.2, and I was able to transfer 2 Mb of data from the JAVA String Array to the PL/SQL table of varchar2.

Do you think that this problem comes from Oracle 8.1.7 or a bad configuration of this server/database ?

Thank you.

Tom Kyte
December 29, 2005 - 4:13 pm UTC

could be

a) 817 (doubtful in general, feature was introduced in that release and 1Kbyte is sort of "small")

b) bad configuration

c) bug

You might look for a trace file on the server, and utilize that with support.

But what if I want to pass more than one table back?

Shawn Brockway, August 11, 2006 - 8:38 am UTC

What if I wanted to pass back multiple pl/sql table types from the same stored procedure as out parameters?

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

create or replace type ObjectType as object
( x int,
a varchar2(20),
b date
);
/
create or replace type ObjectTableType as table of ObjectType;
/

procedure get_x_data(p_x int, p_users out UserTableType, p_objects ObjectTableType) as
begin
-- select my data here to populate both pl/sql tables
end get_x_data;
/

Tom Kyte
August 11, 2006 - 11:14 am UTC

go ahead? you can pass as many arguments in/out as you like.

Sorry should have been clearer...

A reader, August 11, 2006 - 11:27 am UTC

The problem we are having is on the java side with registering out parameters for stored procedures when the data type of the out parameters are pl/sql object types.

But, after looking at your book Effective Oracle by Design, I'm not sure that we want to do that anymore. In your book you have a complete example of using ref cursors to return resultsets. We have tried to run your example exactly as you have it in your book, but we keep getting "Bigger type length than Maximum" when we execute the callable statement in the procedure ref_cursor. Any thoughts on what we are doing wrong?

Tom Kyte
August 11, 2006 - 12:18 pm UTC

please give example.

I don't even know what ora error code you might be refering to here.


You don't really tell me which example you are running.

so, reproduce it all here for us to look at.

Sorry...

Shawn Brockway, August 11, 2006 - 2:16 pm UTC

I need to take a moment to put a clear concise thought together. It's been a long and stressful day. Okay, In your book Effective Oracle by Design, in chapter 9,Effective PL/SQL Programming, you discuss using cursor to return Result Sets to a java application. Specifically I was looking at page 572 where you have an example that looks fairly similar to

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class demo_ref_cursor
{
public static void main (String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@DB_SID","scott","tiger");

OracleCollableStatement cstmt = (OracleCallableStatement)conn.prepareCall("begin sbrockw.demo_pkg.ref_cursor(?,?); end;");
cstmt.setString(1, "scott");
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(2);
String data;
int i;
for(i=1; rset.next(); i++)
data = rset.getString(1);
}
}

the pl/sql procedure is defined as:

create package demo_pkg as
type rc is ref cursor;
procedure ref_cursor(p_owner in varchar2,
p_cursor in out rc);
end;

create package body demo_pkg as
procedure ref_cursor(p_owner in varchar2,
p_cursor in out rc) is
begin
open p_cursor for select object_name,
object_type, timestamp
from all_objects
where owner = upper(p_owner);
end;
end;

When we run the java code, we are getting an exception that simply states "Bigger type length than Maximum java.sql.SQLException: Bigger type length than Maximum". We've been working on doing something similar for a few days and I wanted to see if we could atleast get your example working and move on from there. Any thoughts on what we are doing wrong? I know we are connecting with the jdbc thin driver instead of OCI. Could that be an issue?

Tom Kyte
August 11, 2006 - 2:51 pm UTC

what line of your code is blowing up please - and you are running my example "as is" right.

lets see the java call stack.

It works now, but...

Shawn Brockway, August 11, 2006 - 3:17 pm UTC

Tom,
I was running your code primarily as is. You also had a second procedure as you were demonstrating the differences between returning index-by tables and ref-cursors. I simply omitted the code for the index-by table portion of the example. Strangely enough, we took exactly what I posted to the lead developer's desk to work on it with him, and the code work without any issues. I'm perplexed, but there is obviously nothing that you can tell me that would help us solve explain the difference. There is obviously anamoly with the setup of the developers environment. So you know, we were getting the message I posted earlier on the line "cstmt.execute();". I appreciate the time you took to monitor this thread today even though it seems to have turned out that I was "wasting" your time. As always I appreciate the time you take to monitor and answer questions in the threads.

Thanks,
Shawn

DML coming from Java

A reader, December 06, 2006 - 9:40 pm UTC

Hi Tom,

I have two options for DML coming from java, one is create a package to resolve the insert,update,delete and select statement and the other is create a view with instead of trigger, both option needs some business rule before the insert and update statement. Do you have another approach.


Tom Kyte
December 07, 2006 - 9:44 am UTC

option 3:

a package full of well formed, complete business transactions (not just simple single inserts/updates/deletes) that the java application (which provides a nice pretty user interface) calls.

Types

Bob, May 19, 2009 - 12:26 pm UTC

Hi Tom,

I have a table with 242 columns. I have described it, spooled it to a file and then edited it in vi so that I can create a type.

e.g. desc x

a number(10)
b number(3)
c date
.
.
etc

CREATE OR REPLACE TYPE trn_obj AS OBJECT
(a NUMBER(10),
b NUMBER(3),
c DATE
.
.
.
z);

CREATE OR REPLACE TYPE trn_tab AS TABLE OF trn_obj;
/

If I do

select trn_obj(a,b,c..z) from x

I get: ORA-02315: incorrect number of arguments for default constructor

I know that the number of attributes in my type trn_obj match exactly with my table x.

Is there a limit on the number of attributes in a type?

Tom Kyte
May 23, 2009 - 12:23 pm UTC

ran the following in 9.2.0.8, 10.2.0.4, 11.1.0.7

ops$tkyte%ORA11GR1> declare
  2      l_sql long := 'create table t ( x int';
  3  begin
  4      for i in 1 .. 241
  5      loop
  6          l_sql := l_sql || ', c' || i || ' int ';
  7      end loop;
  8      l_sql := l_sql || ')';
  9      execute immediate l_sql;
 10      l_sql := replace( l_sql, 'create table t', 'create type myType as object ' );
 11      execute immediate l_sql;
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> insert into t(x,c241) values ( 1, 2 );

1 row created.

ops$tkyte%ORA11GR1> variable x refcursor
ops$tkyte%ORA11GR1> declare
  2      l_sql long := 'select myType( x';
  3  begin
  4      for i in 1 .. 241
  5      loop
  6          l_sql := l_sql || ', c' || i;
  7      end loop;
  8      l_sql := l_sql || ') from t';
  9      open :x for l_sql;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> print x

MYTYPE(X,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20
-------------------------------------------------------------------------------
MYTYPE(1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
LL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
LL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
LL, NULL, NULL, NULL, NULL, 2)

Types

Bob, May 20, 2009 - 6:14 am UTC

Interestingly, if I run this select statement in PL/SQL (dynamic SQL) it will NOT raise an exception and it works!

Tom Kyte
May 23, 2009 - 12:55 pm UTC 

spool x.sql
begin
    dbms_output.put_line( 'select myType( x' );
    for i in 1 .. 241
    loop
        dbms_output.put_line( ', c' || i );
    end loop;
    dbms_output.put_line( ') from t;' );
end;
/
spool off


I cleaned up x.sql and ran it, clean.

ops$tkyte%ORA9IR2> select myType( x
  2  , c1
  3  , c2
  4  , c3
....
238  , c237
239  , c238
240  , c239
241  , c240
242  , c241
243  ) from t;

MYTYPE(X,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20
-------------------------------------------------------------------------------
MYTYPE(1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
LL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
LL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
LL, NULL, NULL, NULL, NULL, 2)


A reader, October 24, 2012 - 7:50 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library