Home » Other » Client Tools » Integrating with PL/SQL stored procs from Java/J2EE apps
Integrating with PL/SQL stored procs from Java/J2EE apps [message #320515] Thu, 15 May 2008 05:51 Go to next message
ranganathan_n
Messages: 1
Registered: May 2008
Junior Member
I have requirement to integrate PL/SQL stored procedures on Oracle 10g from Java/J2EE application.

The stored procedure uses associative arrays as In, OUT parameters. The associative array uses data of PL/SQL record type.Because of this I cannot call the stored procedure directly using JDBC.

One of the design options I am considering is using Oracle JPublisher.

My understanding from reading on JPublisher is that , JPublisher would help out in creating the SQL scripts for - SQL types ( mapping to PL/SQL types) + conversion functions for SQL->PL/SQL types and vice versa + wrapper stored procedures.
Also JPublisher creates wrapper java classes.
This would enable passing SQL types via JDBC to the wrapper stored procedure which would then get converted to PL/SQL types and invoke the required underlying procedure.

Is the above understanding correct, specifically in relation to the fact that I have associative arrays and PL/SQL record types in stored procedure. Kindly clarify
Re: Integrating with PL/SQL stored procs from Java/J2EE apps [message #320912 is a reply to message #320515] Fri, 16 May 2008 13:09 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
there is an easier way.

Add a "stub" or overloaded fuction/procedure that converts your plsql array to a refcursor. J2EE apps have no trouble at all taking refcursors.

Here is some sample code in case this is new to you. Please consider that this solution would only really work for smaller datasets because of the memory requirements. For argument sake lets us say anything less than 10,000 rows.

create or replace type o_type1 is object (a number,b number)
/
create or replace type c_type1 is table of o_type1
/

create or replace package pkg_temp1 is

   type r1 is record (a number,b number);
   type t1 is table of r1 index by binary_integer;

   procedure p1 (array_p out t1);

   procedure p1 (array_p in t1,rc_p out sys_refcursor);

end;
/
show errors

create or replace package body pkg_temp1 is

   procedure p1 (array_p out t1) is
   begin
      array_p(1).a := 1;
      array_p(1).b := 1;
      array_p(2).a := 2;
      array_p(2).b := 2;
   end;

   procedure p1 (array_p in t1,rc_p out sys_refcursor) is
      c_type1_v c_type1 := c_type1();
   begin
      for i in 1..nvl(array_p.count,0) loop
         c_type1_v.extend;
         c_type1_v(c_type1_v.last) := o_type1(array_p(i).a,array_p(i).b);
      end loop;
      open rc_p for
         select *
         from table(cast(c_type1_v as c_type1))
      ;
   end;

end;
/
show errors

set serveroutput on

declare
   rc1 sys_refcursor;
   a number;
   b number;
   array_v pkg_temp1.t1;
begin
   pkg_temp1.p1(array_v);
   dbms_output.put_line('array_v.count='||array_v.count);
   pkg_temp1.p1(array_v,rc1);
   loop
      fetch rc1 into a,b;
      if rc1%notfound then exit; end if;
      dbms_output.put_line('a='||a||',b='||b);
   end loop;
   close rc1;
end;
/


Notice the steps here:

1) we defined a data type that matches our plsql table
2) we have a second procedure that uses this new type to convert the plsql table to a collection so that the collection can be returned as a refcursor.

There is clearly some extra work happening here. It would be better to make the original package return a refcursor so we could avoid passing data more than once. But this means a CONTRACT change between pieces of code and thus cold lead to broken clients.

In any event, good luck, this is one easy way around your problem as long as you do not have large datasets to be kept in memory by the plsql array and the database collection.

Kevin
Previous Topic: TOAD - Command Line - Compare Schema's
Next Topic: help to import data from 8i to 10g
Goto Forum:
  


Current Time: Thu Mar 28 13:42:09 CDT 2024