Skip to Main Content
  • Questions
  • Passing an ARRAY from Java to PL/SQL

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Venkat.

Asked: May 06, 2002 - 9:36 pm UTC

Answered by: Tom Kyte - Last updated: October 09, 2013 - 5:57 pm UTC

Category: Developer - Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I need to Pass String array from Java to PL/SQL and also return
array 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 java String type
PROCEDURE PASS (
P_IN IN VARCHAR2,
P_OUT OUT VARCHAR2)
AS
LANGUAGE JAVA
NAME 'demo_passing_pkg.pass( java.lang.String,
java.lang.String[] )';

PROCEDURE PASS (
P_IN IN STRARRAY,
P_OUT 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 off

CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "demo_passing_pkg"
AS
import 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 -- 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_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 void
pass_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 .. 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 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 void
pass_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.





and you rated our response

  (59 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Passing an ARRAY from Java to PL/SQL

May 07, 2002 - 12:45 pm UTC

Reviewer: Venkatesh from Hyderabad

Thanks again Tom for your help.

Venkat

A Reader

July 31, 2002 - 7:52 am UTC

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 array for that..which one is better and optimize approach??

Thanks.


Tom Kyte

Followup  

August 05, 2002 - 9:35 am UTC

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.

August 07, 2002 - 7:36 am UTC

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.

What about a more comple array?

June 09, 2003 - 5:45 pm UTC

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

Tom Kyte

Followup  

June 09, 2003 - 7:08 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10271519764319 <code>
for how I would tackle this.

It is still not clear to me

June 10, 2003 - 10:30 am UTC

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 java? I want to populate this structure manually with my java code and pass it back to PL/SQL.

Thanks again.

Tom Kyte

Followup  

June 10, 2003 - 10:48 am UTC

you can use jpublisher (way beyond where I'm going...) to map these object types to java classes.

Me, I would juse

create 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"

JPublisher

June 10, 2003 - 12:43 pm UTC

Reviewer: bob from texas

Tom,

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

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

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




Using this example

August 21, 2003 - 12:13 pm UTC

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.



Tom Kyte

Followup  

August 21, 2003 - 7:09 pm UTC

not working is 'too vague'

was it "type not known" type of error? did you grant execute on it?

what was the error?

Passing a Java String array from Java to PL/SQL

August 28, 2003 - 4:52 pm UTC

Reviewer: olerag from Virginia

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

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

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??

Tom Kyte

Followup  

August 29, 2003 - 8:41 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8908169959941 <code>



Passing a List Java Collection Type from Java to PL-SQL Java Stored Procedure

October 07, 2003 - 2:14 pm UTC

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 should i use in the java stored Procedure.

I would really appreciate your Response on this .

Thanks in advance.




Tom Kyte

Followup  

October 07, 2003 - 2:17 pm UTC

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

Limitation on array size?

January 23, 2004 - 4:32 pm UTC

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 datatypes

on the call to the Java.

am i doing something wrong here?

Dazza

January 23, 2004 - 5:03 pm UTC

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?



varray

February 10, 2004 - 4:32 am UTC

Reviewer: Venkat from India

Tom,

Our developeres 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



Tom Kyte

Followup  

February 10, 2004 - 6:40 am UTC

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

Brilliant

April 06, 2004 - 12:08 am UTC

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 Java side is declared to be String[], but the PL/SQL side is merely "Varchar2", and the result is the Java stored procedure receives 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 particular example.

cheers,

Matthew


Tom Kyte

Followup  

April 06, 2004 - 9:00 am UTC

I was demonstrating IN OUT parameters.

To do "out" you need to use an array so you can change what the array points to.

A Reader

April 07, 2004 - 2:20 am UTC

Reviewer: A Reader from Hongkong

" Performance wise, it'll be 6 one way, 1/2 dozen the other way."

I am not getting it. What does it mean?


Tom Kyte

Followup  

April 07, 2004 - 9:08 am UTC

a dozen is 12

1/2 dozen is 6, 6 and 1/2 dozen are the same.


it is a way to say "the same" -- sorry, guess I used an analogy that is not world wide

what if i only want to pass P_OUT OUT STRARRAY from Java to PL/SQL ..

April 20, 2004 - 3:55 am UTC

Reviewer: pasko from HH,Germany

Hi Tom,

from the original Post from this thread and examples from your Book ,

you seem to be getting the STRARRAY Type by using the code :
"p_in.getSQLTypeName()"

1.
so, lets assume that i only want to pass an OUT array of Type STRARRAY from Java to PL/SQL, would i still be forced to declare the p_in Variable ?

2.From the code snippet :

PROCEDURE PASS(
P_IN IN STRARRAY , P_OUT OUT STRARRAY
)
AS LANGUAGE JAVA NAME 'demo_passing_pkg.pass_str_array(
oracle.sql.ARRAY , oracle.sql.ARRAY[] )';

that seems to map:

P_IN IN STRARRAY => oracle.sql.ARRAY
P_OUT OUT STRARRAY => oracle.sql.ARRAY[]

although it works fine , i was expecting a Type Mis-match here; can you please elaborate a little bit what's the magic used here , because i thought both P_IN and P_OUT pl/sql variables would have been mapped to : oracle.sql.ARRAY[] and not oracle.sql.ARRAY

Thanks in advance.







Tom Kyte

Followup  

April 20, 2004 - 8:41 am UTC

1) no, i was showing an IN and an OUT parameter. p_in and p_out.

if all you have is an OUT, by all means, just use an OUT

2) P_IN is passed by value -- goes right into a string variable. You cannot modify the string value.

P_OUT is passed by reference -- they use an array of strings (the array is imutable, what the array POINTS TO is changeable). We really just modify what the first array element points to and the data is given back to the caller.

Access Isssues/errors when attempting to as another account

May 27, 2004 - 8:19 pm UTC

Reviewer: olerag from Virignia

Carol's previous statement (08/2003) about grant/access
issues seem relevant. The PL/SQL block that calls the
package and, consequently, the Java class that prepares/
returns the string array, works great for the account that
creates the objects. However, if the block is executed by
another user (even if grants have been issued to that user
for both the type(s) and package), the Oracle exception
originates from the Java class stating that the type,
as specified in the "Descriptor", attempts to alter the
type name to that user's schema (which, of course, the
user doesn't have).

Also, the "getSQLTypeName()" function appears to work with
the oracle.sql.ARRAY object but not the oracle.sql.ARRAY[].
Consequently, if you did not pass this object in your
example (lets say, you used java.lang.string instead), I
believe you would of had to use the type's literal name in
your descriptor in the Java class. Is this correct??

Pass Array from Java to PL/SQL

August 27, 2004 - 8:50 pm UTC

Reviewer: Alex from Irvine, CA

I am looking for a way to pass string array from Java app via thin JDBC to PL/SQL function that will return status whether DML was successful back to Java.
This app is a batch job it currently executes DML similar to this
UPDATE loan_status SET state = 'Processed'
WHERE tx_id IN ( ?, ?, ?, etc )
IN clause can be very lengthy. I would like to optimize passing java array and bulk binding FORALL etc.
Could you give some simple example how to achieve this? We are using oracle 9.2 on Solaris 8. I was searching in your book and on this web site but could not find the answer.
Your reference </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:712625135727 <code>
Shows example of Java stored procedure how much difference there would be over thin JDBC.
Sorry, I am DBA trying to help java programmers.
You help greatly appreciated.

Tom Kyte

Followup  

August 28, 2004 - 9:40 am UTC

if you have "expert one on one oracle" -- the java external routine chapter demonstrates how to easily pass scalars and arrays of scalars between java and plsql. The same method works for java outside of the database as does "inside"

that referenced example above is "perfect" for java in or out of the database.

Another method fro passing Index-By Tables Java-PL/Sql back-and-forth

August 28, 2004 - 1:17 pm UTC

Reviewer: pasko from HH,Germany

Hi Tom,
thanks for your comments above,
i have your Book and had ealier successfully tested the method described in your Book,

It seems that there is a new Method , i think in 10g jdbc Drivers only , not sure if it's present in 9i jdbc Drivers.

Here's an example from OTN.
</code> http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/oracle10g/plsqltables/Readme.html <code>

Tom Kyte

Followup  

August 28, 2004 - 3:57 pm UTC

that is plsql index by tables.

it was added in the 8ir3 timeframe.

it works with the THICK oci drivers only. I have examples on this site as well.

Thanks !!!!!

April 20, 2005 - 2:26 pm UTC

Reviewer: Aparna from NC, USA

It helped me a lot to clear my doubts in using varray

Java objects through Callable statements.

September 09, 2005 - 12:04 am UTC

Reviewer: Deepak from India

Hi Tom,

Can we pass java objects through the Java Callable Statement to PL/SQL Procedures? My requirement is to Catch the object on Oracle side and access it's attributes.

Please help...

Tom Kyte

Followup  

September 09, 2005 - 7:01 am UTC

you can pass SQL objects ( create type myTable as object ..... ) to and from java.

But java classes, much like plsql record types, are somewhat "limited to their own language".

You have to map the datatypes to something both languages can understand (SQL datatypes).



What if we dont want to write java code in PL/SQL procedure

October 06, 2005 - 12:57 pm UTC

Reviewer: Vishal from India

I am trying to pass array (PL/SQL TABLE type) as out parameter through the CallableStatement

CallableStatement cs = con.prepareCall("call RPT_WEB_PKG.TASK_STAGE_ATTRIBUTE(?,?)",ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

in JSP file.

The definition of RPT_WEB_PKG.TASK_STAGE_ATTRIBUTE(Task_Id IN NUMBER,Stage_id OUT INT_ARRAY);

where INT_ARRAY is
create or replace type INT_ARRAY is table of number;

I am not sure how to register out parameter in Java.

From the above example I understood that we need to supply Java code in the PL/SQL package. But I dont want to go for it. Is there some other way.

I got some clue as to how to pass it as IN parameter from
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:712625135727 <code>

Any help would be appreciated.

Thanks




Tom Kyte

Followup  

October 06, 2005 - 1:40 pm UTC

if all you want to do is bind to a plsql table of numbers, see:

</code> http://docs.oracle.com/docs/cd/B10501_01/java.920/a96654/oci_func.htm#1017512 <code>

January 26, 2006 - 11:03 am UTC

Reviewer: Alex

Tom or Menon,

Can you pass an Object[] to Oracle consisting of mupltiple data types? Like element 1 might be an integer, 2 might be a string etc? I don't know if Oracle cares how that stuff comes in. Can I use a VARRAY to accept the incoming object array? Thanks.

Tom Kyte

Followup  

January 26, 2006 - 11:21 am UTC

ops$tkyte@ORA9IR2> create or replace type myType as table of sys.anyData
  2  /

Type created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_data myType := myType( sys.anyData.convertNumber(5),
  3                               sys.anyData.convertDate( sysdate ),
  4                               sys.anyData.convertVarchar2('Hello world' ) );
  5  begin
  6      null;
  7  end;
  8  /

PL/SQL procedure successfully completed.


you could have a collection (nested table or varray) of anydata 

January 26, 2006 - 12:04 pm UTC

Reviewer: Alex

Tom, Thanks, I'm still having a problem though. Would you mind looking at this?

public class Pass_ObjectArray {

static Connection conn = null;

public static void main(String[] args) throws Exception {
if (conn == null) {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@none_of_your_business:DEV",
"MY_USER", "MY_PASSWORD");
}

Integer x = 100;
String y = "Object Array";
Object[] arrayObject = { x, y };

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
"MY_USER.PASSING_ARRAY", conn);
ARRAY myArray = new ARRAY(descriptor, conn, arrayObject.toString());


CallableStatement cs = conn
.prepareCall("begin array_test.sp_array_test(?); end;");
cs.setArray(1, myArray);
cs.execute();
conn.close();
}
}

Database code:

CREATE TABLE t2 (mj VARCHAR2(14));
/

CREATE OR REPLACE TYPE passing_array AS TABLE OF VARCHAR2 (1000);
/

CREATE OR REPLACE PACKAGE array_test
AS
PROCEDURE sp_array_test (p_table IN passing_array);
END;
/

CREATE OR REPLACE PACKAGE BODY array_test
AS
PROCEDURE sp_array_test (p_table IN passing_array)
IS
BEGIN
FORALL i IN 1 .. p_table.COUNT
INSERT INTO t2
(mj
)
VALUES (p_table (i)
);
END sp_array_test;
END;
/

Causes this:

Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.Object;@115273a
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
at oracle.jdbc.oracore.OracleTypeCHAR.cArrayToDatumArray(OracleTypeCHAR.java:622)
at oracle.jdbc.oracore.OracleTypeCHAR.toDatumArray(OracleTypeCHAR.java:210)
at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1517)
at oracle.sql.ARRAY.<init>(ARRAY.java:117)
at Passing_array.Pass_ObjectArray.main(Pass_ObjectArray.java:48)

Thanks as always.

Tom Kyte

Followup  

January 27, 2006 - 8:07 am UTC

you seem to be putting an integer in there? it is a collection of strings?

January 27, 2006 - 8:44 am UTC

Reviewer: Alex

Uh did you see my first question? Does it have to be a collection of the same types? Or maybe I would convert my Object[] to a String[] and pass that? I'm not a java programmer so I don't know which side the problem is coming from.

Tom Kyte

Followup  

January 27, 2006 - 8:50 am UTC

Uh, yes I did, but you didn't use the anydata type at all here - so....umm, well - not sure what to say.

right now, you are using varchar2, you asked me yesterday if you could pass an array with a number, a date, a string and I said yes - and showed how to define it. Your current example - it contains nothing from yesterday.

You have a table of varchar2() - that is a table of strings. You would need a table of anydata types and use the anydata methods and (blah blah blah...)

In short, you would need slightly more code, code I don't have an example for offhand. It would be time to involve a java programmer to write the necessary java code to interface with the anydata type, create an ARRAY of that type and pass it to plsql (at which point, they might just realize that maybe we'll just pass "strings" - numbers and dates can be safely converted to a string and it'll be "easy")



January 27, 2006 - 9:26 am UTC

Reviewer: Alex

Sorry I don't know how to incorporate your example into what I'm trying to do so I figured you misunderstood was I was trying to do. I have a dynamic object array that could contain any data type so I don't know how I could call a convertdate, convertnumber on something I won't know until run time what it will be. Do you think it would be best to just use a String[]?

Tom Kyte

Followup  

January 27, 2006 - 11:21 am UTC

I would likely just use string to send an array of "some data" - but I don't know everything you are trying to do - but it seems that a java programmer would be required here (else why bother, you either have java to code or not and if you do, you'd have a programmer?)

January 27, 2006 - 10:58 am UTC

Reviewer: Alex

Nevermind I got it working. You can just pass an Object[] and it will work with a VARRAY. I had other issues going on, out of date character set libraries and such. Thanks for the help sorry if I pissed you off.

Tom Kyte

Followup  

January 27, 2006 - 11:31 am UTC

No worries - I don't get "pissed off", I just go away :)

January 27, 2006 - 1:10 pm UTC

Reviewer: Alex

Tom,
I'm not quite out of the woods yet. I have one other thing I was hoping you could help me resolve. It turns out I do need sys.anyData. The requirements call for an any type column. So I know I have to create my table column as anydata. I know I have to create the type as anyData.
My problem is, given my procedure with the insert, how do I determine what type each element is to call the appropriate convertxxx function? Some pseudo code would be great. I hope that makes sense.

Tom Kyte

Followup  

January 28, 2006 - 12:27 pm UTC

well, you'll sort of want to read all about the sys.anydata type itself and discover all of the methods you have available to you.

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_anydat.htm#i1000038

specifically 
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_anydat.htm#i1000029 <code>


And think, oh I don't know, maybe about 1,000,000 times before you actually go down this path. Sounds like "super generic, really cool, neato my friends will be wowed" data model.... That won't be truly usable by anyone ;) Word of advice: be careful going down the path of super generic.

January 28, 2006 - 2:43 pm UTC

Reviewer: Alex

If that's the case I may try and convince the java developers otherwise. I think I know exactly what you mean about anyData. I could not even get a simple query to run off of my anyData column. You cannot simply do

select * from t where anydata_col = 100

nor did this work

select * from t
where anydata_col = sys.anyData.convertNumber(100)

It looks to be a pain in the neck. Do you have any links or reason you can give me as ammo not to use this? I need to start coming up with alternatives.


Tom Kyte

Followup  

January 29, 2006 - 8:13 am UTC

in talking to a java programmer, you might be hitting your head on the proverbial wall. "generic", "obscure", "tricky", "neato-cool", "look how little code, so what if no one understands it" - are tricks of the trade.

Experience trying to use or let end users use the resulting mess is what convinces some of them over time, only some though.

January 30, 2006 - 10:59 am UTC

Reviewer: Alex

Tom how come you didn't cover anydata in your new book in the data types chapter? I'm having a real tough time finding information on it. The documentation is pretty limited.

Tom Kyte

Followup  

January 30, 2006 - 3:13 pm UTC

I don't approve of it (could you not tell? :)

I don't think it is a really good idea, so I don't promote it.

January 30, 2006 - 3:27 pm UTC

Reviewer: Alex

Oh I could tell, definitely

Actually what I am looking for is examples of why not to use it. In the past I have seen you mention things you don't like and why. Autonomous transactions in Expert-One-on-One comes to mind. "Here's how you get around mutating tables but if you have to do this there's something wrong with your logic" type of things.

It's important to talk about potentially dangerous stuff like that. Obviously the Oracle docs aren't going to say bad things about their own features and that's where I go to look a lot of stuff up. So people who don't know any better (me for example), aren't going to figure these things out unless experts (you) point it out.

Tom Kyte

Followup  

January 30, 2006 - 3:48 pm UTC

In Effective Oracle by Design I have a section entitled

<quote>
Don’t Use Generic Data Models

Frequently, I see applications built on a generic data model for “maximum flexibility” or applications built in ways that prohibit performance. Many times, these are one and the same thing! For example, it is well known you can represent any object in a database using just four tables:

Create table objects ( oid int primary key, name varchar2(255) );
Create table attributes
( attrId int primary key, attrName varchar2(255),
datatype varchar2(25) );
Create table object_Attributes
( oid int, attrId int, value varchar2(4000),
primary key(oid,attrId) );
Create table Links ( oid1 int, oid2 int,
primary key (oid1, oid2) );

That’s it. No more CREATE TABLE for me!....
</quote>

Perhaps the closest I come to that - well, short of the section:

<quote>
Use the Correct Datatype
Using the correct datatype seems like common sense, but virtually every system I look at does one of the following:
· Uses a string to store dates or times
· Uses a string to store numbers
· Uses VARCHAR2(4000) to store all strings.
· Uses CHAR(2000) to store all strings, wasting tons of space and forcing the use of a lot of trim function calls
· Puts text in a BLOB (raw) type
I have a very simple rule: Put dates in dates, numbers in numbers, and strings in strings. Never use a datatype to store something other than what it was designed for, and use the most specific type possible. Furthermore, only compare dates to dates, strings to strings, and numbers to numbers.
...
</quote>


put those two together and you have it.

String Array output in Ascii (hex) format !!

February 13, 2006 - 7:59 am UTC

Reviewer: Vincent

Hi Tom,

I have a peculiar problem regarding String array used as output parameter in stored procedures. I have compiled the stored procedure below.

CREATE OR REPLACE TYPE CENAME_ARRAY IS TABLE OF VARCHAR2(25);

CREATE OR REPLACE PROCEDURE GETKBNAMES(FDID IN NUMBER, KBNAMEARRAY OUT CENAME_ARRAY)
AS
KBNAME VARCHAR2(80);

CURSOR KBCE_CUR IS
SELECT KB_NAME FROM KNOWLEDGE_BASE WHERE FD_ID=FDID;

BEGIN
KBNAMEARRAY := CENAME_ARRAY();
OPEN KBCE_CUR;
LOOP
FETCH KBCE_CUR INTO KBNAME;
EXIT WHEN KBCE_CUR%NOTFOUND;

KBNAMEARRAY.EXTEND;
KBNAMEARRAY(KBNAMEARRAY.LAST) := KBNAME;

END LOOP;
CLOSE KBCE_CUR;

END ;

When I test it in sql/plus using the code below, it is giving me the the correct output

DECLARE
FDID NUMBER := 1;
KBNAMEARRAY CENAME_ARRAY;
BEGIN
GETKBNAMES(FDID,KBNAMEARRAY);
FOR K IN KBNAMEARRAY.FIRST..KBNAMEARRAY.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(KBNAMEARRAY(K));

END LOOP;
END;

second kbase
small kbase

but when I access it using a small java program, it is giving me the hex ascii equivalent of the string array above like
"0x7365636F6E64206B62617365"
"0x736D616C6C206B62617365"

I have tested my java code with other schemas and it's working properly.

A snapshot of my java code is as follows

String[] kbnamearray = null;

try {

int fdid = 1;
Class.forName(driver);

conn = DriverManager.getConnection(url_to_pass,username_to_pass,password_to_pass);

String query = "call GETKBNAMES(?,?) ";
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "CENAME_ARRAY", conn );
ARRAY array_to_pass = new ARRAY(descriptor, conn, kbnamearray);

cstmt = conn.prepareCall(query);
cstmt.setInt(1, fdid);
cstmt.registerOutParameter(2,OracleTypes.ARRAY,"CENAME_ARRAY");
cstmt.execute();
kbnamearray =(String[])cstmt.getArray(2).getArray();



thanks

Tom Kyte

Followup  

February 13, 2006 - 9:52 am UTC

got a 100% complete, yet very concise example, that has everything one would need to reproduce with.

From start to finish (you can make it very very small - I do all of the time :)

Passing array to Oracle using Standard JDBC, not Oracle JDBC driver

February 13, 2006 - 12:24 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

Hi Tom,

I would like to know if it's possible to pass Array from Java to Oracle using Sun Standard JDBC Driver,
such as: i-net Oranxo

</code> http://www.inetsoftware.de/products/jdbc/oracle/oranxo/ <code>

How to make a data type that Java and Oracle both recognise?

There is not a Method/Function like,
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBERTABLE",
conn);

Thanks a lot.

Tom Kyte

Followup  

February 13, 2006 - 12:52 pm UTC

then probably not - it would be a question for the makers of the Sun Standard JDBC driver to answer.

I doubt it though.

Since jdbc drivers are pretty much "free", why wouldn't you use the one that provides you all of the functionality against Oracle?

Re: String Array output in Ascii (hex) format !!

February 13, 2006 - 2:37 pm UTC

Reviewer: Vincent

Hi Tom,

I don't understand your reply regarding the strange output that I am getting from the stored procedure. I suspect it might have something to do with the character set. It only happens when I used String array(table of varchar2) as output parameters. With table of numbers as output, it's working properly.

cheers

Tom Kyte

Followup  

February 13, 2006 - 3:11 pm UTC

my reply was simply:

give me the full example that I can cut, paste, run, reproduce.




Do you know what is the limit on argument size passed to JDBC CallableStatement.setObject()?

February 14, 2006 - 1:00 pm UTC

Reviewer: Ranko Mosic from TO, CAnada

Regards, Ranko

Tom Kyte

Followup  

February 14, 2006 - 2:11 pm UTC

should only be limited by the amount of available memory - are you hitting some limit? I'm not aware of any documented limits but if you have the error stack from a failure and think it is size related - we can probably plug it into metalink and see what we see.

Standard JDBC, not Oracle JDBC driver

February 14, 2006 - 2:46 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

It's a long story.

Before I join this company.
They hit Transaction Process bugs with Oracle JDBC driver
(Oracle 8 or 9), and Oracle Support response was slow,
(Because it's a free product :) ), so they turned to i-net Oranxo JDBC driver, and used it for 3 to 5 years.

It's hard to persuade our Java Developer to turn back to Oracle JDBC driver, it's a 24*7 production system, Voodoo!

and we Chinese say:
'After once you was bited by a snake, you will look all kinds of line/rope as snake and get scared for 10 years'

It's kind of conventional wisdom.

Thanks,
Charlie

Charlie u r right

February 17, 2006 - 7:47 am UTC

Reviewer: Leela from India

Yes As you told once we strict to one platform. Its very difficult to shift to new tools and environments.
In my case i was trained in java/j2ee but working in datawarehousing.

without Using SQLLODER HOW CAN we retrieve data from Excel ?

September 01, 2008 - 7:47 am UTC

Reviewer: A Reader from India

Hi Tom,
How Can we Retrieve data from Excel with Out using SQLLOADER.
MIne is Oracle JDeveloper Machine
Tom Kyte

Followup  

September 01, 2008 - 1:15 pm UTC

funny, sqlldr would not be able to do a thing with a true excel spreadsheet other than load the entire file into a blob.... So, at least you make it easy for me - sqlloader cannot read excel, glad you want to do it without using it!!!


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

now to try and figure out what a "jdeveloper machine" is.

Using ARRAY AS COLUMNS IN TABLE

September 16, 2008 - 2:22 am UTC

Reviewer: A reader from India

Hi Tom,
I know that we can insert an array into a table.like this
1.create table sample(msg varchar2(10));

2.create or replace Type numarray is table of varchar2(10);

3.PROCEDURE ARR (W numarray)
IS
BEGIN
for i in 1 .. 5
loop
insert into sample values(W(i));
end loop;
NULL;
END;

If I Excute the procedure like this I am getting the output.

4.declare
data numArray := numarray('1','2','3','4','5');
begin
ARR(data);
end;

If I Run the procedure I am getting Intialization error like this,

DECLARE
W IMD_SAMPLE.NUMARRAY;
BEGIN
-- Modify the code to initialize the variable
-- W := NULL;

IMD_SAMPLE.ARR(
W => W
);
END;

ERROR:
Connecting to the database imd_sample.
ORA-06531: Reference to uninitialized collection
ORA-06512: at "IMD_SAMPLE.ARR", line 6
ORA-06512: at line 7
Process exited.
Disconnecting from the database imd_sample.

Q1.How can I intialise array in stored procedure?

Q2.I need to insert array into a table like this

create table sample(A varchar2(10),B varchar2(10),C varchar2(10,D Varchar2(10),E Varchar2(10),Msg Varchar2(10));

A=>array 1st value;W(1)
B=>Array 2nd value;W(2)
.
.
.
msg='hyd'.

How can I insert this array into above table.?


Thanks in Advance



Tom Kyte

Followup  

September 16, 2008 - 10:26 pm UTC

it is not the invoking code, it is the code handling the parameter - it should check for NULL-ness before accessing the elements if it permits NULL to be sent...

and use forall, not for loops, to bulk insert!

ops$tkyte%ORA10GR2> create table sample ( x varchar2(10) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace Type numarray is table of varchar2(10);
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace PROCEDURE ARR (W numarray)
  2  IS
  3  BEGIN
  4          if ( w is not null and w.count > 0 )
  5          then
  6                  forall i in 1 .. w.count
  7                          insert into sample values(W(i));
  8          end if;
  9  END;
 10  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2  data numArray := numarray('1','2','3','4','5');
  3  begin
  4  ARR(data);
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2    W NUMARRAY;
  3  BEGIN
  4    ARR( W => W );
  5  END;
  6  /

PL/SQL procedure successfully completed.

How can we intialise array parameters in stored procedure at run time

September 17, 2008 - 2:33 am UTC

Reviewer: A reader from India

Thank You Tom For the Array procedue,
But what I need is 1.To initialise parameters in stored procedure at run time.In below code where can we give the values to an array.

DECLARE
W NUMARRAY;
BEGIN
ARR(
W => W
);
END;

2.How can we insert an array into a table as column wise
Let array with values 1,2,3,4,5 & the table sam with the columns A,B,C,D,E,MSG
& the procedure is
declare
data numArray := numarray('1','2','3','4','5');
begin
ARR(data,'hyd');
end;
Then the expected output should be like this

A B C D E MSG

1 2 3 4 5 hyd



How can we write a procedure to get above output .Please help me.
Thank you
Tom Kyte

Followup  

September 17, 2008 - 9:05 am UTC

declare 
  w numarray := numarray();
begin
  .... start using w, w.extend and w(1) := whatever ....


It seems you already know how to initialize??? Your #2 point shows that already??


it seems rather straightforward to do the insert:

insert into t (a,b,c,d,e,msg) values ( w(1), w(2), w(3), w(4), w(5), w(6) );


you just reference the i'th array position?


You know, it doesn't seem right to use an array for that however, you should use formal named and typed parameters - the invoker should be passing a, b, c, d, e, msg - NOT an array.

Parameter as a Column

October 30, 2008 - 6:43 am UTC

Reviewer: A Reader

Hi Tom,
My table is:

create table tablename(SNO NUMBER(38),NAME VARCHAR2(40),CMV BLOB,WVW BLOB,OLR BLOB,QPE BLOB,CTTI BLOB,GPS BLOB,SYS_DATE DATE);

My Procedure is this: But this procedure is working fine.

PROCEDURE SAT (
P_NAME VARCHAR2,
P_TYPE VARCHAR2,
P_RET_VAL OUT BLOB
)
AS

V_SYS_DATE1 DATE;
V_SYS_DATE2 DATE;

BEGIN

SELECT MAX(SYS_DATE) INTO V_SYS_DATE1 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3A';
SELECT MAX(SYS_DATE) INTO V_SYS_DATE2 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3B';



IF P_TYPE = 'CMV' THEN
SELECT CMV INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'WVW' THEN
SELECT WVW INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'OLR' THEN
SELECT OLR INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'QPE' THEN
SELECT QPE INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 ANDNAME = P_NAME;

ELSIF P_TYPE = 'CTTI' THEN
SELECT CTTI INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'GPS' THEN
SELECT GPS INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

END IF;

END;





My requirement is this can we write a procedure like this if possible give me sample code.







PROCEDURE SAT (
P_NAME VARCHAR2,
P_TYPE VARCHAR2,
P_RET_VAL OUT BLOB
)
AS

V_SYS_DATE1 DATE;
V_SYS_DATE2 DATE;

BEGIN

SELECT MAX(SYS_DATE) INTO V_SYS_DATE1 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3A';
SELECT MAX(SYS_DATE) INTO V_SYS_DATE2 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3B';




SELECT P_TYPE INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;




Thanks in Advance.
Tom Kyte

Followup  

October 30, 2008 - 8:51 am UTC

v_sys_date2 is never used, lose it.

why this is more than a single query is beyond me.

ops$tkyte%ORA10GR2> create table insat_sat_det_mstr
  2  (SNO NUMBER(38),NAME VARCHAR2(40),CMV BLOB,WVW BLOB,OLR
  3  BLOB,QPE    BLOB,CTTI    BLOB,GPS    BLOB,SYS_DATE    DATE, sat_name varchar2(30));

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace PROCEDURE SAT (
  2   P_NAME    VARCHAR2,
  3   P_TYPE        VARCHAR2,
  4   P_RET_VAL     OUT BLOB
  5  )
  6  AS
  7  BEGIN
  8     select decode( p_type, 'CMV', cmv, 'WVW', wvw, 'OLR', olr, 'QPE', qpe, 'CTTI', ctti, 'GPS', gps )
  9       into p_ret_val
 10       from insat_sat_det_mstr
 11      where name = p_name
 12        and sys_date = (select max(sys_date) from insat_sat_det_mstr where trim(sat_name) = 'INSAT-3A' );
 13  END;
 14  /

Procedure created.



No, you cannot do it the way you described (it would require dynamic sql - but DO NOT EVEN DREAM OF DOING THAT, DO NOT GO THAT PATH, DO NOT)


Couple of issues I have

a) obviously, someone meant to have two tables and the blobs would be rows in a separate child table.

It should be

create table parent
( sno primary key,
name,
sys_date,
sat_name check sat_name=trim(sat_name) );

create table child
( sno references parent,
blob_name,
the_blob );


and then you would just join - no decodes, simple.


b) that trim(sat_name) - horrible, it shouldn't be there. You mean to verify the data upon input and make sure it is valid data already. You should definitely change that.

Re: blobs in a separate child table

October 30, 2008 - 1:03 pm UTC

Reviewer: Stew Ashton from Paris, France


Tom, a schema in my shop has this "parent table + child table for the blob" setup. Do you think this is a good idea, given that there will be few entries with blobs? There is nothing in the child table but the blob and the date it was uploaded.

I gathered that the point was to put the blobs in a separate tablespace, but that can be done without having two tables. I can't think of a good reason to have two tables; am I missing something?

Thanks again for everything :)
Tom Kyte

Followup  

November 02, 2008 - 3:27 pm UTC

hahahahah

ask your "shop" if they realize that blobs are already in a child table (we call it a lob segment).

AND THEY CONTROL THE TABLESPACES!!!! they can have the blob be in another tablespace already, without this child table.

A lob locator is a pointer. You can either have blobs

a) inline - upto 4000 bytes (like a varchar2 size) would be stored in the table with extremely huge performance gains in most all cases. When it grows over 4000 bytes, a pointer is left behind (the lob locator) and a lob segment is used

b) disable storage inline, we do not use the 4000 byte limit, we out of line it, we always point to another table.


So, all they have done is add extra IO an extra storage. That is, they have only made things absolutely, definitely slower and more storage consuming than they were before. They have done nothing good, they have only done bad.

Well, at least you got a good laugh out of it :)

November 02, 2008 - 6:12 pm UTC

Reviewer: Stew Ashton from Paris, France


Very clear, very concise, very helpful. Thanks!

Passing arrays from java to sql ...

November 04, 2008 - 4:34 pm UTC

Reviewer: Dave

The best article ever on this topic ...gives step by steps instructions. I just wanted to thank the author a ton !!!!!

http://ravivedala.blogspot.com/2008/03/oracle-10g-passing-arrays-to-database.html

Object Passing to Store Procedure from java

February 08, 2010 - 4:05 am UTC

Reviewer: shashi wagh

Hi

How do I pass Java object from Java to Oracle

Say I have
EmpBean Class with empId, empName, empDept.

I want to pass EMpBean object to Store Procedure and Use It

Please Help.................................................
Tom Kyte

Followup  

February 15, 2010 - 10:03 am UTC

that would be a java object - which java understands completely - but no other language does.

You can use a SQL object type - which every language can interface to, since there are documented API's and specifications to do so.

http://docs.oracle.com/docs/cd/B19306_01/java.102/b14188/toc.htm

March 04, 2010 - 4:59 pm UTC

Reviewer: A reader from DC, USA

Interestingly in your example the method pass(String l_in, String[] l_out) doesn't seem to be working.
Example:
DECLARE
L_OUT STRARRAY := STRARRAY ();
l_in varchar2(100):='Testing';
BEGIN
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;
/
ERROR at line 11:
ORA-06550: line 11, column 4:
PLS-00306: wrong number or types of arguments in call to 'PASS'
ORA-06550: line 11, column 4:
PL/SQL: Statement ignored

I have a requirement to pass strings and get String[] back, without connecting to Database. This would be a perfect example if it works for me. Thanks in advance.

Tom Kyte

Followup  

March 05, 2010 - 5:19 am UTC

please give the entire example, as I did.

I took the original source posted above

I cut that source

I added a "/" after the create or replace java bit that seems to be missing.

I ran it (successfully I might add) in 9ir2, 10gr2, 11gr2.

So, give FULL example and relate your version and we'll try to see what you missed during the cut and paste.


ops$tkyte%ORA11GR2> CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
  2  /

Type created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE PACKAGE DEMO_PASSING_PKG
  2  AS
  3     -- Varchar2's are most easily mapped to the java String type
  4     PROCEDURE PASS (
  5        P_IN IN VARCHAR2,
  6        P_OUT OUT VARCHAR2)
  7     AS
  8        LANGUAGE JAVA
  9           NAME 'demo_passing_pkg.pass( java.lang.String,
 10                                        java.lang.String[] )';
 11
 12       PROCEDURE PASS (
 13          P_IN IN STRARRAY,
 14          P_OUT OUT STRARRAY)
 15       AS
 16          LANGUAGE JAVA
 17             NAME 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,
 18                                                 oracle.sql.ARRAY[] )';
 19
 20     FUNCTION RETURN_STRING
 21        RETURN VARCHAR2
 22     AS
 23        LANGUAGE JAVA
 24           NAME 'demo_passing_pkg.return_string() return
 25             java.lang.String';
 26  END DEMO_PASSING_PKG;
 27  /

Package created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SET define off
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE AND COMPILE
  2  JAVA SOURCE NAMED "demo_passing_pkg"
  3  AS
  4  import java.io.*;
  5  import java.sql.*;
  6  import java.math.*;
  7  import oracle.sql.*;
  8  import oracle.jdbc.driver.*;
  9
 10  public class demo_passing_pkg extends Object{
 11
 12  public static void pass( java.lang.String p_in,
 13                           java.lang.String[] p_out ){
 14
 15      /*
 16       * the simplest of datatypes -- the String.  If you remember
 17       * the C version with 6 formal parameters, null indicators,
 18       * strlen's, strcpy's and so on -- this is trivial in
 19       * comparision
 20       */
 21
 22      if ( p_in != null ){
 23
 24      System.out.println
 25          ( "The first parameter is " + p_in.toString() );
 26
 27          p_out[0] = p_in.toUpperCase();
 28
 29          System.out.println
 30          ( "Set out parameter to " + p_out[0].toString() );
 31      }
 32  }
 33
 34  private static void show_array_info( oracle.sql.ARRAY p_in )
 35  throws SQLException{
 36
 37      System.out.println( "Array is of type      " +
 38                           p_in.getSQLTypeName() );
 39      System.out.println( "Array is of type code " +
 40                           p_in.getBaseType() );
 41      System.out.println( "Array is of length    " +
 42                           p_in.length() );
 43  }
 44
 45  public static void
 46  pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
 47  throws java.sql.SQLException,IOException{
 48
 49      show_array_info( p_in );
 50      String[] values = (String[])p_in.getArray();
 51
 52      for( int i = 0; i < p_in.length(); i++ )
 53          System.out.println( "p_in["+i+"] = " + values[i] );
 54
 55      Connection conn = new OracleDriver().defaultConnection();
 56      ArrayDescriptor descriptor =
 57         ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
 58
 59      p_out[0] = new ARRAY( descriptor, conn, values );
 60
 61  }
 62
 63  public static String return_string(){
 64      return "Hello World";
 65  }
 66
 67  }
 68  /

Java created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SET serveroutput on size 1000000
ops$tkyte%ORA11GR2> EXEC dbms_java.set_output( 1000000 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> DECLARE
  2     L_IN                          STRARRAY := STRARRAY ();
  3     L_OUT                         STRARRAY := STRARRAY ();
  4  BEGIN
  5     FOR I IN 1 .. 5
  6     LOOP
  7        L_IN.EXTEND;
  8        L_IN (I) := 'Element ' || I;
  9     END LOOP;
 10
 11     DEMO_PASSING_PKG.PASS (L_IN, L_OUT);
 12
 13     FOR I IN 1 .. L_OUT.COUNT
 14     LOOP
 15        DBMS_OUTPUT.PUT_LINE ('l_out('|| I || ') = ' || L_OUT (I) );
 16     END LOOP;
 17  END;
 18  /
Array is of type      OPS$TKYTE.STRARRAY
Array is of type code 12
Array is of length    5
p_in[0] = Element 1
p_in[1] = Element 2
p_in[2] = Element 3
p_in[3] = Element 4
p_in[4] = Element 5
l_out(1) = Element 1
l_out(2) = Element 2
l_out(3) = Element 3
l_out(4) = Element 4
l_out(5) = Element 5

PL/SQL procedure successfully completed.

March 05, 2010 - 11:34 am UTC

Reviewer: A reader

Here I used stripped version of your code.
SQL> CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
  2  /

Type created.
SQL> CREATE OR REPLACE PACKAGE DEMO_PASSING_PKG
  2  AS
  3  -- Varchar2's are most easily mapped to the java String type
  4  PROCEDURE PASS (
  5  P_IN IN VARCHAR2,
  6  P_OUT OUT VARCHAR2)
  7  AS
  8  LANGUAGE JAVA
  9  NAME 'demo_passing_pkg.pass( java.lang.String,
 10        java.lang.String[] )';
 11  END DEMO_PASSING_PKG;
 12  /

Package created.
CTPD>   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "demo_passing_pkg"
  2  AS
  3  import java.io.*;
  4  import java.sql.*;
  5  import java.math.*;
  6  import oracle.sql.*;
  7  import oracle.jdbc.driver.*;
  8  public class demo_passing_pkg extends Object{
  9  public static void pass( java.lang.String p_in, java.lang.String[] p_out ){
 10    /* the simplest of datatypes -- the String.  If you remember
 11      * the C version with 6 formal parameters, null indicators,
 12      * strlen's, strcpy's and so on -- this is trivial in
 13      * comparision
 14      */
 15   String[] names = new String[5];   
 16     if ( p_in != null ){
 17     System.out.println
 18      ( "The first parameter is " + p_in.toString() );
 19     names[0] = p_in.toUpperCase();
 20     names[1]="Testing Line2";
 21     names[2]="Testing Line3";
 22       System.out.println ( "Set out parameter to " + names[0].toString() );
 23       p_out=names;
 24      }
 25     }
 26  }
 27  /

Java created.

Now
1.How do I implement DEMO_PASSING_PKG.PASS ??
2. How do I pass String[] (Array of Strings) back to Oracle. Mapping this java to IN Varchar2, OUT Varchar2 doesn't seem to be right.
3. How do I dynamically expand "names" and still at the end assign "names" to "p_out"(OUT parameter).

Tom Kyte

Followup  

March 05, 2010 - 11:47 am UTC

umm, would you take about 5 seconds and review your code. The mistake you have made is actually fairly obvious.

Look at the type you define - strarray.

Now, look at the stripped down package spec you wrote.

Do you see strarray anywhere in there?


Oh, how I .... it when people do things like this and write:



... Interestingly in your example the method pass(String l_in, String[] l_out) doesn't seem to be
working.
...

Not mentioning that they *heavily modified the code in question*. Not even mentioning it. It turns out to be the case pretty much 100% of the time - that they have modified it - a lot.



Please, back up a second, take the original example, make sure you understand all of the bits of it (you don't yet, basing that on the comment #2 you made - you are right, it doesn't make sense to use a varchar2 - you wanted an ARRAY of strings....)




March 05, 2010 - 11:37 am UTC

Reviewer: A reader from DC, USA

My Version of Oracle 10gR2

ORA-00932: inconsistent datatypes

March 10, 2010 - 2:36 pm UTC

Reviewer: Tyrone Post from Farmington, CT USA

Hi Tom,

The information on your site has been very helpful. However I was wondering if you could help me with one tiny issue.
I'm attempting to use a java stored procedure to read a file from a remote server into a string array, and pass that array up to PL/SQL as an out parameter so that other functions and procedures can manipulate the data, format it, and insert it into various tables etc etc...

The code that I have written is similar to the code you have provided.

My PL/SQL code:

create or replace type STRARRAY as table of varchar2(32767);
/

create or replace package RF is

procedure read(       location varchar2
                    , fileName varchar2
                    , arry_in IN strarray
                    , arry_out OUT strarray    );

end RF;
/

create or replace package body RF is

procedure read(       location varchar2
                    , fileName varchar2
                    , arry_in IN strarray
                    , arry_out OUT strarray    )
is language java
name 'RFile.read(java.lang.String, java.lang.String, oracle.sql.ARRAY, oracle.sql.ARRAY[])'; 

end RF;
/

My Java Code:
create or replace and compile java source named rfile as

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import oracle.sql.*;
import oracle.jdbc.OracleDriver;

public class RFile {
public static void read(String location, String fileName,
   oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out)
   throws IOException, SQLException {
  BufferedReader br;
  FileReader fr = new FileReader(location + "\\" + fileName);
  br = new BufferedReader(fr);

  ArrayList a = new ArrayList();
  String line;
  while ((line = br.readLine()) != null) {
   a.add((String) line);
  }
  String[] values = new String[a.size()];
  for (int i = 0; i < values.length; i++) {
   values[i] = (String) a.get(i);
   if (values[i] == "")
    values[i] = null;
  }

  Connection conn = new OracleDriver().defaultConnection();
  ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(p_in.getSQLTypeName(), conn);
  p_out[0] = new ARRAY(descriptor, conn, values);
 }
}
/


Here is a test script that I've used which reads the file from the local machine:

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

declare
arry_in strarray := strarray();
arry_out strarray := strarray();
begin
rf.read('C:\oracle\io', 'io.dat', arry_in, arry_out);
for i in 1 .. arry_out.count
loop
    dbms_output.put_line('arry_out(' || i || ') = ' || arry_out(i) );
end loop;
end;
/


Now the weird thing about this is, when I run it on an oracle installation running on a fellow developers box, it works perfectly. When I run it on an oracle installation on a database test box, it works perfectly. However, when I try to run it on our actual development box, I get the following error:

ORA-00932: inconsistent datatypes: expected an OUT argument at position 4 that is an instance of a user defined Java class convertible to an Oracle type got an object that could not be converted


Now there are some differences between the installations.
Here's the information on the two boxes the procedure did work:

MS Windows XP Professional SP 3
Oracle Release 10.2.0.1

MS Windows Server 2003 R2
Oracle Release 10.2.0.1

(both 32 bit)

Here is the information for the box it did not work under:

MS Windows Server 2003 R2 Enterprise x64 Edition SP2
Oracle Release 10.2.0.4

I'm not sure if the inconsistent behavior is related to these differences but I thought it might be worth mentioning.

Thanks in advance,
Tyrone
Tom Kyte

Followup  

March 11, 2010 - 8:09 am UTC

I didn't read the code, I'll just tell you how I would do this.

why use java, you have to have access to the file on the server (even java does) so we can use external tables. Just use an external table. If a java stored procedure can read it, so can a plsql stored procedure, so can an external table. You are doing this the hard way.

Thanks and Note to Others

March 11, 2010 - 6:48 am UTC

Reviewer: Tim Jowers from Charlotte, NC

Thanks for the good depth article. Best on the web. A note to others is the original post is using the defined PROCEDURE "PASS" with the STRARRAY. See, it gets redefined in the first several lines of the example. so, the calling example near the end
DEMO_PASSING_PKG.PASS (L_IN, L_OUT);
is using that second proc definition. So, the question about the String versus String[] mapping to VARCHAR versus STRARRAY may lead you down a misleading path. Looks like the mapping is what you would expect but maybe Oracle doesn't have overloading of PROCEDURE names based on datatype.

March 23, 2010 - 11:22 pm UTC

Reviewer: A reader


String Array output in Ascii (hex) format !!

May 05, 2010 - 6:24 am UTC

Reviewer: Harold from Madrid, Spain

Hi Tom,

I have the same problem especified by Vincent in this post http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696816290928#56953050569757

My full code example:

CREATE or REPLACE TYPE EMPARRAY is VARRAY(3) of VARCHAR2(30)

CREATE or REPLACE FUNCTION EXAMPLE RETURN EMPARRAY
AS
l_data EmpArray := EmpArray();
--declare
type t_tabla is table of varchar2(10);
v_tabla t_tabla:= t_tabla ('A28057537','B91364737','A08924599');
-- declaration variables
conta_pdte_mat number:= 0;
conta_mat number:= 0;
begin
for i in 1 .. v_tabla.count
loop
--
select count(distinct pp.id_peticion)
into conta_pdte_mat
from peticiones_planes pp,
peticiones pet,
planes pl
where pp.id_peticion = pet.id_peticion
and pl.id_plan = pp.id_plan
and pp.id_plan = 0 --MITyC
and pet.fecha_creacion between to_date ('31/03/2010','dd/mm/yyyy') and to_date ('28/04/2010','dd/mm/yyyy')
and pet.cif_nif = v_tabla(i)
and exists (select null
from historico_estados hist
where hist.id_estado = 20
and hist.id_peticion = pet.id_peticion
and hist.id_plan = pp.id_plan
);
--
select count(distinct pp.id_peticion)
into conta_mat
from peticiones_planes pp,
peticiones pet,
planes pl
where pp.id_peticion = pet.id_peticion
and pl.id_plan = pp.id_plan
and pp.id_plan = 0 --MITyC
and pet.fecha_creacion between to_date ('31/03/2010','dd/mm/yyyy') and to_date ('28/04/2010','dd/mm/yyyy')
and pet.cif_nif = v_tabla(i)
and exists (select null
from historico_estados hist
where hist.id_estado = 10
and hist.id_peticion = pet.id_peticion
and hist.id_plan = pp.id_plan
);
--
l_data.extend;
l_data(i) := (v_tabla(i)||';'||conta_pdte_mat||';'||conta_mat);
--
end loop;
return l_data;
END EXAMPLE;

example of one element of the array --> A28057537;419;518

package generaExcel.agentesDeVentas;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;

public class ExcelEvolucionExpedientesAV {
public static void main(String[] args){
Connection cn = null;
OracleCallableStatement stm = null;
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
cn = DriverManager.getConnection(url, user, password);
stm = (OracleCallableStatement)cn.prepareCall("begin ? := EVOLUCION_EXPEDIENTES_AV;end;");
stm.registerOutParameter(1, OracleTypes.ARRAY, "EMPARRAY");
stm.executeUpdate();
ARRAY simpleArray = stm.getARRAY(1);
String[]list = (String[])simpleArray.getArray();
for(String s: list)
System.out.println(s);
}catch(SQLException sqle){
System.out.println(sql.getMessage());
}finally{
try{
if(stm != null)
stm.close();
if(cn != null)
cn.close();
}catch(SQLException sqle){
System.out.println(sql.getMessage());
}
}
}
}

the console shows 0x4132383035373533373B3431393B353138 instead A28057537;419;518

Do you know why it can happen?

Thanks

PD: My english is not very well, sorry
Tom Kyte

Followup  

May 06, 2010 - 2:00 pm UTC

I don't have your tables.

I therefore cannot run your code.

Perhaps you should code the plsql example in a manner that doesn't require any tables so I can run the example from start to finish?

Passing List of records to the Oracle Procedure

March 09, 2011 - 2:49 am UTC

Reviewer: Mohammad from UAE

Hello Tom,

Firstly, I would like to thank you a lot for helping people.

I am tring to send list of objects to Oracle procedure lets say:-
public class Employee{
String name;
Date birthDate;
int idNumber;
}
and I have a list looks like:-
ArrayList<Employee> alEmployees;

and I need to send the Employees list with more than one item to the stored procedure.

Can you please advise about the proper way to do it???

Thaaaaaaaaaaaaaaaaaaaanks,
Tom Kyte

Followup  

March 09, 2011 - 9:23 am UTC

You would need to map that to a collection of objects.

probably the easiest way to accomplish that would be via JPublisher, it'll generate the code to facilitate mapping your Java data to SQL data.

Passing List of records to the Oracle Procedure

March 09, 2011 - 11:26 pm UTC

Reviewer: Mohammad from UAE

Hello Tom,

Firstly, I would like to thank you a lot for helping people.

I am tring to send list of objects to Oracle procedure lets say:-
public class Employee{
String name;
Date birthDate;
int idNumber;
}
and I have a list looks like:-
ArrayList<Employee> alEmployees;

and I need to send the Employees list with more than one item to the stored procedure.

Can you please advise about the proper way to do it???

Thaaaaaaaaaaaaaaaaaaaanks,



Followup March 9, 2011 - 9am Central time zone:

You would need to map that to a collection of objects.

probably the easiest way to accomplish that would be via JPublisher, it'll generate the code to facilitate mapping your Java data to SQL data.
-----------------------------------------------------

I would like to thank you a lot for fast responce, but still there is one question, I have no idea about the steps for this and I have no idea about the JPublisher...

Thaaaaaaaaaaaaaaaaaaanks
Tom Kyte

Followup  

March 10, 2011 - 10:24 am UTC

your keyboard is stuck or something.

but anyway - fortunately, we document our tools:

http://www.oracle.com/pls/db112/portal.all_books#index-JPU

Passing array from Java to PLSQL

March 14, 2011 - 10:38 am UTC

Reviewer: V.Hari from Chennai India

Hi,
Thanks for your wonderful site and for your spelindid service to the oracle community.

If java as to send me an array of data like below, Need to know how should i declare the array in Oracle to make use of FORALL for bulk operations.

Empno, EmpName, Location, Deptno
101 Tom US D001
102 Rajini INDIA D002
103 Hari INDIA D003

Sample Code - Should i use SQL types like below or can use another form like Create or replace Type Record ?

Declaration Part in Oracle

CREATE OR REPLACE TYPE EMP_TYPE is object
(
empno Number(10),
empname varchar2(100),
Location varchar2(50),
dept varchar2(10)
);

CREATE OR REPLACE TYPE TY_EMP_TYPE IS TABLE OF EMP_TYPE;

Java Part

final ListlistOfEmployees = new LinkedList();

Employee e1 = new Employee();
e.setEmpId(101);
e.setEmpName("TOM");
e.setLocation("US");
e.Deptno("D001");

listOfEmployees.add(e1);

Employee e2 = new Employee();
e.setEmpId(102);
e.setEmpName("RAJINI");
e.setLocation("INDIA");
e.Deptno("D002");

listOfEmployees.add(e2);

// Like wise for rest of the data, add the data to the array.

// Now, create an array descriptor

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "TY_EMP_TYPE", dconn );

ARRAY array_to_pass = new ARRAY( descriptor, dconn, (Object[]) listOfEmployees.toArray());
ps = (OracleCallableStatement)dconn.prepareCall("begin insert_employees(:1); end;");
ps.setARRAY( 1, array_to_pass );
ps.execute();
conn.commit();
}


Basic logic is to get the data from the front end screen and java to pass the data to Oracle package. Using arrays for best performance.

Please let me know your views.

Thanks for your valuable time.

Regards
V.Hari


Tom Kyte

Followup  

March 14, 2011 - 12:03 pm UTC

you cannot send/receive a plsql record type from any language other than plsql.

you can send/receive a collection of records to/from any language that can connect to and use Oracle.


Passing arrays to plsql

March 14, 2011 - 12:37 pm UTC

Reviewer: V.Hariharaputhran from Chennai India

Mr.Oracle

Thanks for your time.

Inshort for the below data set from java, please help

Empno, EmpName, Location, Deptno 
101    Tom      US        D001 
102    Rajini   INDIA     D002 
103    Hari     INDIA     D003

Is the below declaration correct

CREATE OR REPLACE TYPE EMP_TYPE is object
(
empno Number(10),
empname varchar2(100),
Location varchar2(50),
dept varchar2(10)
);

CREATE OR REPLACE TYPE TY_EMP_TYPE IS TABLE OF EMP_TYPE;

PROCEDURE PR_INSERT(p_emparray in TY_EMP_TYPE)

Regards
V.Hari
Tom Kyte

Followup  

March 14, 2011 - 12:43 pm UTC

I cannot tell if your object is correct, it would fit the data displayed, but an example of data displayed does not specify all of the data values that could be used.

So, assuming you got the datatypes right - sure, the definitions look syntactically correct so far (but I'm not a compiler - for that you'd want to run the code :) )

Passing arrays to Oracle

March 14, 2011 - 12:59 pm UTC

Reviewer: V.Hariharaputhran from Chennai India

Mr.Oracle,

Thanks Guru. All i was looking is about the mapping and processing the java array in plsql.

Table

create table emp(Empno Number(10), EmpName Varchar2(100), Location Varchar2(50), Deptno Varchar2(10), Hiredate date);

Arrays Declaration

CREATE OR REPLACE TYPE EMP_TYPE is object
(
empno Number(10),
empname varchar2(100),
Location varchar2(50),
deptno varchar2(10),
hiredate date
);

CREATE OR REPLACE TYPE TY_EMP_TYPE IS TABLE OF EMP_TYPE;

PROCEDURE PR_INSERT(p_emparray in TY_EMP_TYPE)

Thanks for your time.

Regards
V.Hari




Invalid values of string array

March 28, 2011 - 1:52 am UTC

Reviewer: Vladimir from Russian Federation

Thanks a lot for this discussion, it was very useful for me. But I still have an issue with array output parameter passing from oracle to java layer. Here is my reproduce script:

SQL script is:

create or replace type test_array as table of varchar2(100);

CREATE OR REPLACE PROCEDURE test_arrays_proc(NAMEARRAY OUT test_array)
AS
BEGIN
NAMEARRAY := test_array();
NAMEARRAY.EXTEND;
NAMEARRAY(NAMEARRAY.LAST) := 'test1';
NAMEARRAY.EXTEND;
NAMEARRAY(NAMEARRAY.LAST) := 'test2';
NAMEARRAY.EXTEND;
NAMEARRAY(NAMEARRAY.LAST) := 'test3';
END;

Java:


package knock;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.pool.OracleDataSource;

public class OracleArrayTest {

private static void execTest(Connection conn) throws SQLException {
CallableStatement cs = conn.prepareCall("call test_arrays_proc(?)");

cs.registerOutParameter(1, Types.ARRAY, "TEST_ARRAY");
cs.execute();

Array namesArray = cs.getArray(1);
Object result = namesArray.getArray();
}

public static void main(String[] argv) {
try {
OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
ds.setURL("jdbc:oracle:thin:@ora31:1521/FCSMDMD");
Connection conn = ds.getConnection("strategy", "strategy");

execTest(conn);

conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


When you call test_arrays_proc, the value of NAMEARRAY out params will be: ["test1", "test2", "test3"]

But, when you execute the Java example, you will see, that result object will contain String[3]{"???", "???", "???"}

Driver version: ojdbc14_g-10.2.0.4.jar
Manifest:

Manifest-Version: 1.0
Specification-Title: Oracle JDBC driver classes for use with JDK14
Created-By: 1.4.2_14 (Sun Microsystems Inc.)
Implementation-Title: ojdbc14_g.jar
Specification-Vendor: Oracle Corporation
Specification-Version: Oracle JDBC Driver version - "10.2.0.4.0"
Implementation-Version: Oracle JDBC Driver version - "10.2.0.4.0"
Implementation-Vendor: Oracle Corporation
Implementation-Time: Sat Feb 2 11:53:00 2008

Tom Kyte

Followup  

March 29, 2011 - 2:59 am UTC

reading this code, I see it would produce:

<nothing>


where is the bit of code that shows it prints the wrong stuff?

Invalid values of string array

March 29, 2011 - 10:18 pm UTC

Reviewer: Vladimir from Russian Federation

Sorry, about previous useless post. Here is the printing example:

package knock;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import oracle.sql.ARRAY;
import oracle.sql.CHAR;
import oracle.sql.Datum;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

public class OracleArrayTest {

 private static void execTest(Connection conn) throws SQLException, IOException {
  CallableStatement cs = conn.prepareCall("call test_arrays_proc(?)");
  
        cs.registerOutParameter(1, Types.ARRAY, "TEST_ARRAY");
        cs.execute();
        
        ARRAY namesArray = (ARRAY) cs.getArray(1);
        Datum [] oraArr = namesArray.getOracleArray();
        
        for (int i = 0; i < oraArr.length; ++i) {
         CHAR c = (CHAR) oraArr[i];
         String s = c.toString();
         System.out.println("The resulting CHAR[" + i + "] charset = " + c.getCharacterSet() + " and value = \"" + s + "\" and expected value is \"" + new String(c.getBytes(), "windows-1251") + "\"");
        }
 }
 
 public static void main(String[] argv) {
     try {
      OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
      ds.setURL("jdbc:oracle:thin:@ora31:1521/FCSMDMD");
      Connection conn = ds.getConnection("strategy", "strategy");
      short oracleId = ((OracleConnection)conn).getJdbcCsId();
      System.out.println("Connection charset id: " + oracleId + " (which equals UTF8_CHARSET)");
      
      execTest(conn);
      
      conn.close();
  } catch (Exception e) {
   e.printStackTrace();
  } 
 }
}


Output is:

Connection charset id: 871 (which equals UTF8_CHARSET)
The resulting CHAR[0] charset = CL8MSWIN1251 and value = "???" and expected value is "test1"
The resulting CHAR[1] charset = CL8MSWIN1251 and value = "???" and expected value is "test2"
The resulting CHAR[2] charset = CL8MSWIN1251 and value = "???" and expected value is "test3"

Passing array to Java

October 08, 2013 - 11:35 pm UTC

Reviewer: R.Zorge from Columbus, OH USA

How about passing array from PL/SQL to Java. Provided we have all the necessary JDBC libraries.
Is it possible for Java stored proc connect to SQL Server, execute query based on the input parameters stored in local Oracle look-up table and then insert result-set of that query back into Oracle but different table. Sort of extract/load round-trip without the transformation.
We can't use Transparent Gateway. Some primitive example would be very helpful.

Thank you in advance.
Tom Kyte

Followup  

October 09, 2013 - 5:57 pm UTC

passing an array from java to plsql:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:712625135727


getting a java stored procedure to connect to sql server would involve loading the jdbc thin driver for sql server into the Oracle database and then opening a new connection in the stored procedure.


I don't know why you cannot use the heterogeneous gateway - it comes with the database and would let you just use sql. It is not that you CANNOT use the gateway, it is that you are making a decision NOT TO use the gateway. You certainly *can* use the gateway, you are choosing not to.

sorry, I don't have any examples of loading the sql server jdbc drivers - I don't have sql server installed anywhere (surprisingly enough..)