problems passing table of object to Java from PL/SQL

From: obakesan <cjundieseastwd_at_powerup.com.au>
Date: Fri, 20 Aug 2004 22:23:24 GMT
Message-ID: <41267848$0$27241$61ce578d_at_news.syd.swiftdsl.com.au>

HiYa

I've been trying to pass a structure rather than simply an array of string to some Java, however I've been unsucessful. Can anyone suggest what I might be doing wrong?

I've made the following types

• oracle types CREATE TYPE person AS object (firsName VARCHAR2(30), lastName VARCHAR2(30));

create or replace type array_of_persons as table of person;

• package wrapper (in a package)

   PROCEDURE j_test_people (p_dummy IN strArray
                          , p_in IN array_of_persons
                          , p_out OUT strArray) AS
      LANGUAGE JAVA NAME 

'paramPassingTest.pass_person_array(oracle.sql.ARRAY, oracle.sql.ARRAY,
oracle.sql.ARRAY[])';

• test script DECLARE cell strArray := strArray(); returnData strArray := strArray(); people array_of_persons := array_of_persons();

BEGIN

     people.EXTEND;
     people(1) := person('Chris', 'Eastwood');
     people.EXTEND;
     people(2) := person('James', 'Cook');
     people.EXTEND;
     people(3) := person('Billy', 'Bragg');
     people.EXTEND;
     people(4) := person('Tommy', 'Emanuel');
     cell.EXTEND;
     cell(1) := 'yo there';
     java_test.j_test_people( cell , people , returnData);

   dbms_output.put_line('return data has '|| returnData.COUNT );    FOR i IN 1 .. returnData.COUNT LOOP
     dbms_output.put_line( 'returnData(' || i || ') = ' || returnData(i) );

   END LOOP; END;

• and finally the java

public class paramPassingTest
{

   public static void pass_person_array( oracle.sql.ARRAY p_dummy,
                                         oracle.sql.ARRAY p_in,
                                         oracle.sql.ARRAY[] p_out
                                      ) throws java.sql.SQLException
   {
       String[] stringArray = new String[6];
       String[] dataRow;

       Connection conn = new OracleDriver().defaultConnection();
       ArrayDescriptor descriptor =
         ArrayDescriptor.createDescriptor( p_dummy.getSQLTypeName(),

conn );

       stringArray[0] = p_dummy.getSQLTypeName(); //EASTWOOC_DEV.STRARRAY
       stringArray[1] = p_dummy.getBaseTypeName(); //VARCHAR

       stringArray[2] = p_in.getSQLTypeName(); //EASTWOOC_DEV.ARRAY_OF_PERSONS

       stringArray[3] = p_in.getBaseTypeName();  //EASTWOOC_DEV.PERSON
       String[] values = (String[])p_in.getArray();
       p_out[0] = new ARRAY( descriptor, conn, stringArray );

   }

}

its here at the end where I'm falling down. I know that the parameter is getting in, and I'm able to get in my OUT parameter the SQLTypeName and BaseType, but I don't know how to access my data.

I know that if I was passing in a table of varchar2 I could use something like

String[] values = (String[])p_in.getArray();

but, I'm beginning to think that it won't support passing anything else.

help!

See Ya
(when bandwidth gets better ;-)

Chris Eastwood
Photographer, Programmer
Motorcyclist and dingbat

please remove undies for reply Received on Sat Aug 21 2004 - 00:23:24 CEST