bulking with PLSQL/Java params passing & collections puzzle.. tough one!

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Mon, 24 Mar 2003 13:15:17 +0100
Message-ID: <b5mt27$2atsr2$1@ID-114658.news.dfncis.de>

Hello all,

test platforms: Oracle Enterprise Edition 9.2.0.2 W2K & Solaris SPARC 64.

I need to execute a Java Procedure method that generate a collection of double coefficients out of two input collection parameters (collections are of table type):

create type dateArray as table of DATE;
create type doubleArray as table of NUMBER; create or replace function kdpae_computecoeffs(times in dateArray

                                                                        ,

tvalues in doubleArray) return doubleArray AUTHID CURRENT_USE
PARALLEL_ENABLE
DETERMINISTIC
AS LANGUAGE JAVA NAME
'com.kdlabs.kdprevent.analytics.computeCoeffs(oracle.sql.ARRAY, oracle.sql.ARRAY) return oracle.sql.ARRAY'; /
SHOW ERRORS create or replace function kdpae_computecoeffs(times in dateArray

                                                                        ,
tvalues in doubleArray
                                                                        ,

tcoeffs in out nocopy doubleArray) return String AUTHID CURRENT_USE
PARALLEL_ENABLE
DETERMINISTIC
AS LANGUAGE JAVA NAME
'com.kdlabs.kdprevent.analytics.computeCoeffs(oracle.sql.ARRAY, oracle.sql.ARRAY, oracle.sql.ARRAY[]) return String'; /
SHOW ERRORS The problem is that I want to execute this function as a single call over *ALL* the possible inputs in a way that allows Oracle achieve parallelism, and I can't seem to find the way to achieve that, bellow all the alternatives I have tried:

--//
--// Anonymous PL/SQL example
--//

declare
type table_doubleArray is table of doubleArray; type table_dateArray is table of dateArray; type nums_list is table of number(9);

times dateArray := dateArray();
tvalues doubleArray := doubleArray();

ntimes   table_dateArray     := table_dateArray();
ntvalues table_doubleArray := table_doubleArray();
ntcoeffs table_doubleArray := table_doubleArray();

j                number(9);
dummyStr varchar2(100);
indxs         nums_list := nums_list();

begin
--// compute aggregations...

--// collect input data into ntimes and ntvalues...

execute immediate 'ALTER SESSION FORCE PARALLEL QUERY'; execute immediate 'ALTER SESSION FORCE PARALLEL DML';

• 1.- NOT POSSIBLE... returning clause can not use forall index forall i in ntimes.first .. ntimes.last select kdpae_computecoeffs(ntimes(i), tvalues(i)) into ntcoeffs(i) from dual;
• 2.- NOT POSSIBLE... select inside forall can not include bulk collect forall i in ntimes.first .. ntimes.last select kdpae_computecoeffs(ntimes(i), tvalues(i)) bulk collect into ntcoeffs from dual;
• 3.- NOT POSSIBLE... Internal server error!!!!! j := 1; forall i in ntimes.first .. ntimes.last select kdpae_computecoeffs(ntimes(i), tvalues(i)) , j + 1 into ntcoeffs(j), j from dual;
• 4.- NOT POSSIBLE... ORA-06572: function name has out arguments
  --//
  --// In this case I tried changing the kdpae_computecoeffs function to
  --// return actually a dummy String (not useful I know but just want
  --// to fake the FORALL-SELECT statement to work) and then have
  --// the function return the coeffs as an OUT param.
  --//
  forall i in ntimes.first .. ntimes.last select kdpae_computecoeffs(ntimes(i), tvalues(i), ntcoeffs(i)) into dummyStr from dual;
• 5.- NOT POSSIBLE... collection table-cast column_value
  --// can not be used as index parameter, the error is:
  --// "invalid column name: column_value" ;-(...
  --//
  indxs.extend(ntimes.count); for i in ntimes.first .. ntimes.last loop indxs(i) := i; end loop; select kdpae_computecoeffs(ntimes(column_value), tvalues(column_value)) bulk collect into ntcoeffs from table(cast(indxs as nums_list));

end;
/

I can only end up with a total failing solution that doesn't allow Oracle work in parallel and hence this does not scale on multi-processor enviroments:

for i in ntimes.first .. ntimes.last
loop

     ntcoeffs(i) := doubleArray(); // initialize... is it required?
     select  kdpae_computecoeffs(ntimes(i), tvalues(i))
     into ntcoeffs(i)
     from dual;

end loop;

I also tried this piece:

assume all type definitions are moved into varray something like this:

create or replace type doubleArray190 as varray(190) of NUMBER;

create table coefficients_table (

    coefficients doubleArray190
) PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT ) /

I didn't find how to create a column of type table but only a column of fixed size varray.

forall i in ntimes.first .. ntimes.last

     insert into coefficients_table
     select  kdpae_computecoeffs(ntimes(i), tvalues(i))
     from dual;

but I checked this statement did not work in parallel either in a Solaris SPARC
8 processors, perhaps because of varray columns???

Any help greatly appreciated,
Best regards,
Giovanni Received on Mon Mar 24 2003 - 06:15:17 CST