Passing an array from Java to PL/SQL

A6l 06,200 4 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 Array from Java to PL/SQL and also returnarray 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_pkgas -- varchar2's are most easily mapped to the Java string type procedure pass (p_in varchar2, p_out out varchar2) as language Java name' demo _ passing_pkg.p Ass (Java. lang. string, Java. lang. string []) '; Procedure pass (p_in strarray, p_out strarray) as language Java name 'demo _ passing_pkg.pass_str_array (Oracle. SQL. array, Oracle. SQL. array []) '; function return_string return varchar2 as language Java name' demo _ passing_pkg.return_string () return Java. lang. string '; end demo_passing_pkg;/set define offcreate or replace and compilejava source named "Demo_passing_pkg" asimport Java. io. *; import Java. SQL. *; import Java. math. *; import oracle. SQL. *; import oracle. JDBC. driver. *; public class demo_passing_pkg extends object {public static void pass (Java. lang. string p_in, Java. 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 -- t His 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_array_info (Oracle. SQL. array p_in) throws sqlexception {system. out. println ("array is of type" + p_in.getsqltypename (); system. out. println ("array is of type code" + p_in.getbasetype (); system. out. println ("array is of length" + p_in.length ();} public static voidpass_str_array (Oracle. SQL. array p_in, Oracle. SQL. array [] p_out) throws Java. SQL. sqlexception, ioexception {show_array_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 array (descriptor, Conn, values);} public static string return_string () {return "Hello World ";}} set serveroutput on size 1000000 exec dbms_java.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 .. rochelle out.count loop dbms_output.put_line ('L _ out ('| I |') = '| Rochelle 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 Array (Collection) We want to pass. O demo_passing_pkg is our "binding" to the java. maps the SQL types to the Java types. O when you run the PLSQL block at the bottom of your question, we are calling the code: public static voidpass_str_array (Oracle. SQL. array p_in, Oracle. SQL. array [] p_out) throws Java. SQL. sqlexception, ioexception {show_array_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 array (descriptor, Conn, values);} That code just 1) dumps the array meta data -- type name, length and so on.2) gets the array of Java strings from the parameter (p_in.getarray () 3) prints out each string in turn (system. out. println) 4) creates a new array 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 array we want to return into the out parameter.

Review & followup

Rating: 4

Passing an array from Java to PL/SQL May 07,200 2

Reviewer: Venkatesh From Hyderabad

Thanks again Tom for your help. Venkat

Rating: 4

A reader July 31,200 2

Reviewer: Rahul From India

Hi Tomi 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 shocould use the same approach or use the array 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 array 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,200 2

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,200 3

Reviewer: Carol From Canada

I understand your example, but I am unable to understand how to extend it to allow an array containing something like (last_name, first_name, address) to be returned from the Java to the PL/SQL (I am not querying the database to retrieve the information, I am building the array 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 wocould tackle this.

Rating: 3

It is still not clear to me June 10,200 3

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 wocould declare in my java? I want to populate this structure manually with my java 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 Java classes. me, I wowould jusecreate global temporary table GTT (fname varchar2 (20), lname varchar2 (20) on commit Delete rows; and have the Java 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,200 3

Reviewer: Bob From Texas

Tom, no need to go there (jpub that is) .. (that is what OTN is !)

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 Java classes to represent the Oracle object type. this sample uses strates access of an object type using a Java class generated by jpublisher, while Retrieval Using weakly typed objects (Oracle. SQL. struct) is already strated in object Oracle sample. updated on 20-may-2003.

Rating: 5

Using this example August 21,200 3

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/Java 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,200 3

Reviewer: Olerag From Virginia

First example of code I 've seen that clearly demospassing a Java string array to a PL/SQL stored functionthat is implemented with a PL/SQL block. other examples only showed a Java class that passed anarray to a PL/SQL block only to return it to the samejava class. this did not provide the "key" to the issue, specifically the return type as depicted in the PL/sqlpackaged function, "oracle. SQL. array [] ". The reference, however, to jdeveloper was, I suppose, anattempt to demonstate the capaiblities of passingmulti-dimen1_arrays. I wonder how one might returnthis type of object (string [] []) to a PL/SQL packagedfunction 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,200 3

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 Java Collection type from Java to PL-SQL using Java stored procedure. for example in Java code there's a declaration like: List Results = new arraylist (); I was thinking of copying this list type to an array of string, but may be there's a 1-to-1 mapped type from list to Oracle JDBC types. so, what mapped type shoshould I use in the Java stored procedure. I wocould 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,200 4

Reviewer: Dazza From UK

Tom, when I use the example with 10 elements in the array it works. if I try 11 I get: ORA-00932: inconsistent PES ypes on the call to the java. am I doing something wrong here?

Rating: 4

Dazza January 23,200 4

Reviewer: A reader From UK

Tom, please ignore that previous comment. in my java file if one of the elements of the array turns out to be "" (ie a zero length Java 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,200 4

Reviewer: Venkat From India

Tom, our elastes feel that Java API can be used to handle varrays in Java 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 wocould remove "What dependency". Not sure at all what they mean.

Rating: 5

Brilliant A6l 06,200 4

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 doing strating with the 'demo _ passing_pkg.pass 'example. the Java side is declared to be string [], but the PL/SQL side is merely "varchar2", and the result is the Java stored procedure since es a single-element string []. is this just to demonstrate a convenient feature of the PL/SQL-Java interface (that single-element arrays can be manipulated without the need for Oracle. SQL. array )... or did I miss the real point of that particle example. cheers, Matthew

Followup:

I was demonstrating in out parameters. to do "out" you need to use an array so you can change what the array points.

A6l 06,200 4A reader -- thanks for the question regarding "9i and SQL Server 2000", version 9i