Re: PL/SQL Tables and JDBC/SQLJ

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/01
Message-ID: <8jkt86$32l$1@nnrp1.deja.com>#1/1

In article <395c25da.10486782_at_news.pacbell.net>, landrew_11_at_yahoo.com (Andrew Adams) wrote:
> Hi all,
> I have a packaged procedure that returns an OUT parameter of type
> PLSQL Table. My Java program needs to bind this to either SQLJ or
> JDBC. A lot of research on OTN/ Metalink has led me to believe that
> this "might" be possible.
>
> My understanding is that the ability to bind to a PLSQL Table type was
> introduced in 8.1.6, which I am running on HP/UX 11. I am unable to
> find an example of how this can be implemented though. My preference
> would be to use SQLJ.
>
> Has anyone had any luck in implementing this on any version of 8i?
>
> Thanks!
>
> Andrew
>
>

The short answer is -- you cannot bind to plsql TABLES, RECORDS or BOOLEANs from JDBC. See

http://technet.oracle.com/doc/oracle8i_816/java.816/a81354/ref4.htm#1005 006

for the details. The longer answer is there are 2 ways to work around this.

the first way would have you bind a simple delimited string from java and parse it into the table (and if need be put it back into the string) in plsql. That is, instead of executing a block of plsql like:

begin

testme.retrievearray( :java_bind_variable ); end;

you would have a block of code like:

begin
testme.retrievearray( STR2ARRAY(:java_string) ); end;

The following exmaple, done in sqlplus, shows how to code a block you would embed in your java client that can call a plsql routine that takes a PLSQL table type as an IN/OUT parameter. You could make the functions I have in the block (str2array, array2st) be standalone functions in the database (to avoid having to put them in the block) or not -- up to you:

ops$tkyte_at_dev8i> create or replace package my_pkg   2 as
  3 type Array is table of number index by binary_integer;   4
  5 procedure array_rev( p_array in out array );   6 end;
  7 /
Package created.

ops$tkyte_at_dev8i> create or replace package body my_pkg   2 as
  3
  4 procedure array_rev( p_array in out array )   5 is
  6 l_tmp array;
  7 begin

  8      for i in 1 .. p_array.count loop
  9          l_tmp( p_array.count-i+1 ) := p_array(i);
 10      end loop;
 11      p_array := l_tmp;

12 end;
13
14 end;
15 /
Package body created.

Thats the package we want to invoke from java. java cannot bind to the plsql table type. we'll let it bind a string instead

ops$tkyte_at_dev8i> set autoprint on
ops$tkyte_at_dev8i> variable a varchar2(255)
ops$tkyte_at_dev8i> exec :a := '1,2,3,4,5,6,7'

PL/SQL procedure successfully completed.

That is our demo list of values we want to send in and get back out...

1,2,3,4,5,6,7

ops$tkyte_at_dev8i> declare

  2      l_tmp    my_pkg.array;
  3      function str2array( p_str in varchar2 )
  4      return my_pkg.array
  5      as
  6          l_str    long default p_str || ',';
  7          l_tmp    my_pkg.array;
  8      begin
  9          loop
 10              exit when l_str is null;
 11              l_tmp(l_tmp.count+1) :=
 12                   substr( l_str, 1, instr(l_str,',')-1 );
 13              l_str := substr( l_str, instr(l_str,',')+1);
 14          end loop;
 15          return l_tmp;
 16      end;
 17      function array2str( p_tbl in my_pkg.array )
 18      return varchar2
 19      is
 20          l_str     long;
 21          l_sep     varchar2(1);
 22      begin
 23          for i in 1 .. p_tbl.count loop
 24              l_str := l_str || l_sep || p_tbl(i);
 25              l_sep := ',';
 26          end loop;
 27          return l_str;
 28      end;
 29  begin
 30      l_tmp := str2array( :a );
 31      my_pkg.array_rev( l_tmp );
 32      :a := array2str( l_tmp );

33 end;
34 /

PL/SQL procedure successfully completed.

7,6,5,4,3,2,1

That is the block of code we need to put in our java application. We could make it a stored procedure of course, or break out str2array and array2str to make it smaller.

The second option is much like the first but instead of using strings, we would use OBJECT types. Java can bind to nested table and varray types. You would still have to create a small interface layer to convert the nested table into the corresponding plsql table but you would not have to string up the array elements.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jul 01 2000 - 00:00:00 CDT