Passing an ARRAY from Java to PL/SQL

April 06, 2004 Venkat -- Thanks for the question regarding "Passing an ARRAY from Java to PL/SQL", version 8.1.7 You Asked

Hi Tom,
 
   I need to Pass String  from  to PL/SQL and also return
 from PL/SQL. I refered your book and arrived at the below code.

CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
/

CREATE OR REPLACE PACKAGE DEMO_PASSING_PKG
AS
   -- Varchar2's are most easily mapped to the  String type
   PROCEDURE PASS (
      P_IN IN VARCHAR2,
      P_OUT OUT VARCHAR2)
   AS
      LANGUAGE 
         NAME 'demo_passing_pkg.pass( .lang.String,
                                      .lang.String[] )';
  
     PROCEDURE PASS (
        P_IN IN STRARRAY,
        P_OUT OUT STRARRAY)
     AS
        LANGUAGE 
           NAME 'demo_passing_pkg.pass_str_( oracle.sql.,
                                               oracle.sql.[] )';

   FUNCTION RETURN_STRING
      RETURN VARCHAR2
   AS
      LANGUAGE 
         NAME 'demo_passing_pkg.return_string() return  
           .lang.String';
END DEMO_PASSING_PKG;
/



SET define off

CREATE OR REPLACE AND COMPILE
 SOURCE NAMED "demo_passing_pkg"
AS
import .io.*;
import .sql.*;
import .math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class demo_passing_pkg extends Object{

public static void pass( .lang.String p_in,
                         .lang.String[] p_out ){

    /*
     * the simplest of datatypes -- the String.  If you remember
     * the C version with 6 formal parameters, null indicators,
     * strlen's, strcpy's and so on -- this is trivial in
     * comparision
     */

    if ( p_in != null ){
        
    System.out.println
        ( "The first parameter is " + p_in.toString() );

        p_out[0] = p_in.toUpperCase();

        System.out.println
        ( "Set out parameter to " + p_out[0].toString() );
    }
}

private static void show__info( oracle.sql. p_in )
throws SQLException{

    System.out.println( " is of type      " +
                         p_in.getSQLTypeName() );
    System.out.println( " is of type code " +
                         p_in.getBaseType() );
    System.out.println( " is of length    " +
                         p_in.length() );
}

public static void
pass_str_( oracle.sql. p_in, oracle.sql.[] p_out )
throws .sql.SQLException,IOException{

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

    for( int i = 0; i < p_in.length(); i++ )
        System.out.println( "p_in["+i+"] = " + values[i] );

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

    p_out[0] = new ( descriptor, conn, values );

}

public static String return_string(){
    return "Hello World";
}

}


SET serveroutput on size 1000000
EXEC dbms_.set_output( 1000000 )

DECLARE
   L_IN                          STRARRAY := STRARRAY ();
   L_OUT                         STRARRAY := STRARRAY ();
BEGIN
   FOR I IN 1 .. 5
   LOOP
      L_IN.EXTEND;
      L_IN (I) := 'Element ' || I;
   END LOOP;

   DEMO_PASSING_PKG.PASS (L_IN, L_OUT);

   FOR I IN 1 .. L_OUT.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE ('l_out('|| I || ') = ' || L_OUT (I) );
   END LOOP;
END;
/


It worked, But I did not understand the flow of the code, Can you please help me 
here.

Thanks in Advance,
Venkat
  

---

and we said...

Umm, at a loss here.  It is pretty straightforward stuff and I did document the 
flow in the book (very heavily I thought).

I'll try again.

o STRARRAY is simply our Oracle type that represents the  (collection) we 
want to pass.

o DEMO_PASSING_PKG is our "binding" to the .  Maps the SQL types to the  
types.

o when you run the PLSQL block at the bottom of your question, we are calling 
the code:

public static void
pass_str_( oracle.sql. p_in, oracle.sql.[] p_out )
throws .sql.SQLException,IOException{

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

    for( int i = 0; i < p_in.length(); i++ )
        System.out.println( "p_in["+i+"] = " + values[i] );

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

    p_out[0] = new ( descriptor, conn, values );

}

That code just 

1) dumps the  meta data -- type name, length and so on.
2) gets the  of  strings from the parameter (p_in.getArray())
3) prints out each string in turn (system.out.println)
4) creates a new  to be returned (first half of example shows how to PASS 
IN, second half shows how to pass OUT).  
5) Then, it copies the values in the  we want to return into the OUT 
parameter.



 

Review & Followup

---

Rating: 4
Passing an ARRAY from Java to PL/SQL  May 07, 2002
Reviewer:  Venkatesh  from Hyderabad

Thanks again Tom for your help.

Venkat 

---

Rating: 4
A Reader  July 31, 2002
Reviewer:  Rahul  from India

Hi Tom

I have a small doubt, please clear it.
I have a proecdure which takes the parameter values from a form filled by a user 
and insert it into a table.
That form has 15 to 20 parameters and right now I am using one IN parameter for 
each value.
Please tell me, I should use the same approach or use the  for that..which 
one is better and optimize approach??

Thanks.
 

Followup:

I cannot say -- it depends on the code in the client doesn't it? Is dealing with the data in an more natural? Is dealing with each parameter as a formal named (and typed) parameter more natural? Performance wise, it'll be 6 one way, 1/2 dozen the other way.

---

Rating: 3
  August 07, 2002
Reviewer:  peter

I think the confusion perhaps lies/lay in why we have two overloaded pass 
routines and whether that really necessary.

Useful never the less. 

---

Rating: 3
What about a more comple array?  June 09, 2003
Reviewer:  Carol  from Canada

I understand your example, but I am unable to understand how to extend it to 
allow an  containing something like (last_name, first_name, address) to be 
returned from the  to the pl/sql (I am not querying the database to retrieve 
the information, I am building the  manually).  I cannot find an example on 
this.  Does one exist?

Thank you 

Followup:

see http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10271519764319 for how I would tackle this.

---

Rating: 3
It is still not clear to me  June 10, 2003
Reviewer:  Carol  from Canada

I'm sorry, but the example does not make it clear to me.  If I declare a 
structure like:

create or replace type testemp as object (
    fname            varchar2(20),
    lname            varchar2(20))
/

create or replace type testtableemp as table of testemp
/

in PL/SQL.  What is the related structure I would declare in my ?  I want to 
populate this structure manually with my  code and pass it back to PL/SQL.

Thanks again. 

Followup:

you can use jpublisher (way beyond where I'm going...) to map these object types to classes. Me, I would juse create global temporary table gtt ( fname varchar2(20), lname varchar2(20) ) on commit delete rows; and have the app BATCH insert into this and then call the procedure, the procedure just uses that tables data as its inputs. that, in my experience, is the least amount of code between "me" and "being finished"

---

Rating: 5
JPublisher  June 10, 2003
Reviewer:  bob  from texas

Tom,

No need to go there (JPub that is)..  (that is what OTN is for!)

http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/advanced/advanced.htm

Objects can be accessed either using oracle.sql.STRUCT or by defining custom 
 Classes to represent the Oracle Object Type. This sample illustrates access 
of an Object Type using a  class generated by JPublisher, while retrieval 
using weakly typed objects (oracle.sql.STRUCT) is illustrated in Object Oracle 
Sample. Updated on 20-May-2003.


 

---

Rating: 5
Using this example  August 21, 2003
Reviewer:  Carol  from BC

Hi Tom,

I used the 'passing the array' portion of this example and it worked great.  
When I try to run the example as a user that is not the owner of the 
packages/ and type, I cannot get it to work.  Is it possible or am I doing 
something wrong?

I changed the code to just pass strings (and not arrays so I didn't need the 
type strarray) and when I run it as a different user it works fine.

Any ideas?

Thanks.

 

Followup:

not working is 'too vague' was it "type not known" type of error? did you grant execute on it? what was the error?

---

Rating: 5
Passing a Java String array from Java to PL/SQL  August 28, 2003
Reviewer:  olerag  from Virginia

First example of code I've seen that clearly demos
passing a  String  to a PL/SQL stored function
that is implemented with a PL/SQL block.  

Other examples only showed a  class that passed an
 to a PL/SQL block only to return it to the same
 class.  This did not provide the "key" to the issue,
specifically the return type as depicted in the PL/SQL
packaged function, "oracle.sql.[]".

The reference, however, to JDeveloper was, I suppose, an
attempt to demonstate the capaiblities of passing
multi-dimensional arrays.  I wonder how one might return
this type of object (String[][]) to a PL/SQL packaged
function in a pure PL/SQL environment?? 

Followup:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:8908169959941

---

Rating: 5
Passing a List Java Collection Type from Java to PL-SQL Java Stored Procedure  October 07, 2003
Reviewer:  pasko  from HH,Germany

Hi Tom,

I have just finished reading the above Great example..
but still couldn't figure out how to pass a List  Collection Type from  
to PL-SQL using  stored Procedure.

For example in  code there's  a declaration like :

List  results = new  ArrayList() ;


I was thinking of copying this List type to an  of String , but may be 
there's a  1-to-1 mapped Type from List  to  Oracle jdbc Types .


So , what mapped type should i use in the  stored Procedure.

I would really appreciate your Response on this .

Thanks in advance.


 

Followup:

you have to use the collection type, as above. you need to map to a SQL type.

---

Rating: 4
Limitation on array size?  January 23, 2004
Reviewer:  Dazza  from uk

Tom,

when i use the example with 10 elements in the  it works. if i try 11 i 
get:

ORA-00932: inconsistent datatypes 

on the call to the .

am i doing something wrong here? 

---

Rating: 4
Dazza   January 23, 2004
Reviewer:  A reader  from uk

Tom,

please ignore that previous comment. 

In my  file if one of the elements of the  turns out to be "" (ie a 
zero length  string) it crashes Oracle. if i put a bit of code that said:
if (arrayInput[i] == ""){arrayInput[i] = null}

the problem goes away. 
wierd?

 

---

Rating: 4
varray  February 10, 2004
Reviewer:  Venkat  from India

Tom,

Our developeres feel that  API can be used to handle varrays in  instead 
of oracle or weblogic API to eliminate dependency of oracle/weblogic on this 
part. 

Is this the best way keeping in view the future implementation of oracle?

Regards,
Venkat

 

Followup:

don't understand what the goal is or how they would remove "what dependency". not sure at all what they mean.

---

Rating: 5
Brilliant  April 06, 2004
Reviewer:  Matthew  from Canberra, ACT, Australia

Tom,

Once again you prove to be an absolute godsend.   Worked perfectly in both our 
8i & 9i databases :)

Only one comment: I can't see what you're illustrating with the 
'demo_passing_pkg.pass' example.

The  side is declared to be String[], but the PL/SQL side is merely 
"Varchar2", and the result is the  stored procedure receives a 
single-element String[].

Is this just to demonstrate a convenient feature of the PL/SQL- interface 
(that single-element arrays can be manipulated without the need for 
oracle.sql.)... or did I miss the real point of that particular example.

cheers,

Matthew
 

Followup:

I was demonstrating IN OUT parameters. To do "out" you need to use an so you can change what the points to.

April 06, 2004 A Reader -- Thanks for the question regarding "9i and SQL Server 2000", version 9i