Nice!
Mihai Manuta, April 04, 2001 - 9:30 am UTC
Its one of the most unanswered questions in the forums. Unfortunately your answer raised another question: when I do the following:
cstmt=(OracleCallableStatement)conn.prepareCall("{call test(?,?)}");
cstmt.setObject(1,new String("ceva"),java.sql.Types.VARCHAR);
cstmt.registerOutParameter((2,OracleTypes.ARRAY,"PKG_DATATYPES.TV20");
cstmt.execute();
I get an exception:
Fail to construct descriptor: Unable to resolve type: "PKG_DATATYPES.TV20"
The PKG_DATATYPES.TV20 type is declared in the database (but wasnt declared by me). Can you help me on that?
Wai, May 27, 2001 - 10:23 pm UTC
array passwing between java client & stored procedure
Wai, May 27, 2001 - 10:25 pm UTC
The example is good because the
code is simple and it worked (for 8.1.x only).
Unfortunately, when I apply the sample code to
8.0.4 database. I got the similar
result as "Mihai Manuta from Paris" have
(unable to resolve type).
Then I downloaded the most recent
Oracle thin client driver (use 8.1.7
driver conntecting to 8.0.4 database),
the program overcome the resolve type problem.
However it introduce another problem againt,
the result is in Hexdecimal format instead of
ASCII string!!!
Is there any special setting require at
both database server/java client level?
How ARRAY type can be used using 8.0.5 JDBC driver?
Avijit Saha, July 16, 2001 - 7:16 am UTC
Without changing JDBC 8.0.x driver how can I use ARRAY type? Now I'm working with Oracle client version 8.0.6 and JDBC version is 8.0.5 ( e.g. DatabaseMetaData meta = conn.getMetaData ();
System.out.println("JDBC driver version is " + meta.getDriverVersion()); ). Without using 8.1.x JDBC driver how can I get PL/SQL table as ARRAY type? Can any body help me?
A reader, July 23, 2002 - 8:46 am UTC
The array in the example is a simple array of table type varchar2(). what about a complex array..say String[][]
How would you go about it.
July 23, 2002 - 10:46 am UTC
two dimensional arrays are not supported until 9i (and string[][] is a 2 dim array) then they would map to a complex object type and you would use jpub to create a mapping class cause the code gets really ugly.
Read about jpublisher.
A reader, July 24, 2002 - 12:19 am UTC
Does that mean 2 Dim arrays cannot be passed by a java stored proc/function in 8i. Is there a work around.
ossie j, July 26, 2002 - 4:12 am UTC
so if 9i supports double dimensional arrays, is there any documentation or any sample program which demonstrates this, particularly in java egsample.
July 26, 2002 - 6:48 am UTC
yes, the plsql guide shows collections of collections and you'll want to use jpublisher to create the java mapping class.
How to Receive Arrays from Java in PL/SQL
Chandra S.Reddy, February 07, 2003 - 9:02 am UTC
Hi Tom,
I have one oustanding issue reg passing this arrays from Java to PL/SQL.
Am using Weblogic and Tomcat as application servers.So we obviously their(WL or Tomcat) connection pools.But not Oracle pool.
Plese look at the code below.
drop table t1 ;
create table t1 (a number(1), b varchar2(5));
drop type Num_List ;
create TYPE Num_List as TABLE OF NUMBER; -- create array of number
/
CREATE OR REPLACE PROCEDURE SP_PoC_Test_Array (in_val Num_List) IS
BEGIN
FORALL i IN in_val.FIRST..in_val.LAST
INSERT INTO T1 (A) VALUES (in_val(i)) ;
END SP_PoC_Test_Array;
/
-- Java program.
import java.sql.*; import oracle.jdbc.driver.*; import oracle.sql.*;import oracle.jdbc.oracore.Util;import java.math.BigDecimal;
public class ArrayTest1
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbc:oracle:thin:@spindc4:1521:java1";
Connection conn;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "chandra", "chandra");
CallableStatement cstmt = conn.prepareCall("{CALL sp_cmsa_test_array(?)}");
int elements[] = { 1, 2, 3, 4, 5 };
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_LIST", conn);
ARRAY newArray = new ARRAY(desc, conn, elements);
((OraclePreparedStatement)cstmt).setARRAY(1, newArray);
cstmt.execute();
cstmt.close();
}
}
The above implementations are working fine, when we tested as a standalone programs.
But, while developing the application, we should use Weblogic connection or Tomcat connection. Not Oracle driver.
In the above code "ArrayDescriptor" object is Oracle's own implementation(correct if am wrong).
There actually we are landing into errors.
Is there anyway to overcome this.
Shall we do this Java SPs or any other kind.
Another restriction, we have is there should not be any SQL, PL/SQL work inside in Java.Everything must be inside Stored procs.
*******
Corrently we are implementing this issue like below.
TYPE t_Roles IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; -- declare collection element for Ids and Flags.
v_Roles t_Roles ;
v_Indx := 0;
LOOP
v_Indx := v_Indx+1;
v_Position := INSTR(v_AllRoles, '~'); -- find position of delimiter (tild character). if string has any value, at least one delimiter is always there with it.
EXIT WHEN v_Position = 0 ; -- exit loop when delimiter not found
v_Roles(v_Indx) := TO_NUMBER(SUBSTR(v_AllRoles, 1, v_Position - 1)) ; -- take string before the position of delimiter
v_AllRoles := SUBSTR(v_AllRoles, v_Position + 1) ; -- reassign remaining string to same variable
END LOOP ;
-- more code here---
Where "v_AllRoles" is an IN parameter. I get values for this from application with '~' as a delimeter(ex:- '1~2~3~4~0'). In PL/SQL I tokenize the string(v_AllRoles) and construct an array v_Roles as in the above code.
And use the the "v_Roles" to do other transactions.
This kind of IN strings(~ delimited strings)are 'n' in number some times. Then I need to add 'n' number of loops.Finally Code looks very ugly.
Please suggest us.
February 07, 2003 - 12:20 pm UTC
array's are proprietary -- if a database has them, they are implemented in their fashion.
jdbc is "open" -- meaning the spec only defines that which is at the lowest common denominator.
We have extensions in our jdbc driver to allow you to use all of our functionality.
They do not.
Now, that aside:
...But, while developing the application, we should use Weblogic connection or
Tomcat connection. Not Oracle driver....
is something I don't agree with -- you don't have to, you are free to use OUR driver if you like. No reason you shouldn't -- especially if you use this construct alot.
nested table of object type as parameter to proc
A reader, April 10, 2003 - 4:55 am UTC
if i have a nested table of an object type can i map that to java?
meaning:
create type test_obj as object
(col1 varchar2(10),col2 number,col3 number);
create type test_tbl as table of test_obj;
can java use this test_tbl for mapping?
or is this considered as a two dimensional array?
how do i handle such situaions?
Returning a pl/sql table from a procedure and function
A reader, September 17, 2003 - 11:43 pm UTC
Returning a pl/sql table from a procedure and function, i.e. the solution you gave above in pl/sql
Can you give a simple example for the above.
returning a result set....The best way
Dan White, December 24, 2003 - 12:04 pm UTC
Tom,
From what I have read there are 2 possible ways of returning result sets from Oracle stored procedures to Java(ref cursors and Arrays)(oracle 8.1.7.4). We are having an enternal debate at my client about the best way to go about this. We need to return multiple columns of data with multiple rows. We (java and Oracle Developers) would like to use ref cursors to return data due to the dynamic nature of our application. The DBA's here would like us to use arrays(nested table) to pass information back to the java client. there reasoning is that if the connection to the webserver is lost then there will be an open cursor left. If a session is activly retrieving data from a webserver and the connection to that server is lost what will happen to the session?
December 24, 2003 - 1:35 pm UTC
there is only one way to return a result set -- ref cursor.
to return an array is to return an array (no result set semantics about it).
The DBA's would rather you use scads of memory? To be really inefficient. The only correct method is a ref cursor, really ( wrote about this in my new book Effective Oracle By Design and showed the reasons you don't want to consider the arrays -- its ugly ).
How would there be any open cursors? they are worried about something that isn't going to happen. Say the browser crashes -- the jsp or whatever finishes and wah-lah, gives up the connection and all cursors would be closed.
Say the connection from the app server to the db is lost. Well, thats what DCD (dead client detection) will handle, database will clean up.
A lost connection will not result in open cursors lying about (and even if they do, so what? cursors are session specific -- the session would be left open -- no one else would be able to get to that session -- THAT is the problem they want to focus on and it is a problem you have with both techniques.
The only way to do this is a ref cursor.
Tom Execelent answer
Dan White, December 29, 2003 - 1:15 pm UTC
Tom,
Thank you for the Answer, This is exactally what I have been trying to get across to our DBA staff.
Thank you
Dan
correct answer for Wai
xiewen, November 19, 2004 - 5:21 am UTC
/*
drop table varray_table;
drop type num_varray;
CREATE TYPE num_varray AS VARRAY(10) OF VARCHAR2(12)
/
CREATE TABLE varray_table (col1 num_varray);
INSERT INTO varray_table VALUES (num_varray('你好', 'abc'));
select * from varray_table;
*/
import java.sql.*;
import java.math.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class Array1
{
public static void main(String args[]) throws Exception
{
int oracleId = CharacterSet.ZHS16GBK_CHARSET;
CharacterSet dbCharset = CharacterSet.make(oracleId);
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection
("jdbc:oracle:thin:@10.9.200.58:1521:db01",
"mytest",
"mytest");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");
while (rs.next()) {
ARRAY my_array = ((OracleResultSet)rs).getARRAY(1);
// return the SQL type names, integer codes,
// and lengths of the columns
System.out.println ("Array is of type " + my_array.getSQLTypeName());
System.out.println ("Array element is of typecode " + my_array.getBaseType());
System.out.println ("Array is of length " + my_array.length());
// get Array elements
String[] values = (String[]) my_array.getArray();
for (int i = 0; i < values.length; i++)
{
oracle.sql.CHAR out_value = new oracle.sql.CHAR(values[i], dbCharset);
System.out.println(">> index " + i + " = " + out_value);
}
}
rs.close();
stmt.close();
conn.close();
}
}
Resultset is passing to the Array
Ramesh Battineni, May 18, 2005 - 12:08 am UTC
Hello,
It's very usefull for me because I want to take the Resultset Data and store it in to an array, then that array need to paas in to the JTable Objects.
returning Incorrect type code
Anup Toshniwal, February 10, 2008 - 2:51 am UTC
Hi,
I have executed the same code ( passing Oracle Array to Java) as above using JDK 1.4 and Oracle 8i, but the java Array returns me type code 12 (varchar) instead of 1 (char), and this returns me values just like this (? ? ?) for every row of the Array. Not able to find out what they problem is and am not able to change the type code so that it returns the data in correct format. I suspect it is either the problem with either the JDBC library (ojdbc14.jar ) or the oracle 8i installation/options. We have also tested the above code with a 8i, jdk1.2, classes12.zip environment, but it still returns code 12.
Can you please guide as to what are the areas to be looked and how do we resolve for returning type code 1.
Regards,
Anup
February 11, 2008 - 10:12 pm UTC
well, that is what this code was first written with.
so, did you run *my* code or *your* code.
Perhaps your code was not correct code?
To: Anup Toshniwal re: returning Incorrect type code
Stew Ashton, February 12, 2008 - 2:36 am UTC
haaseg, February 13, 2008 - 7:41 am UTC
We also started using the JDBC Array interface mapped by Oracle to object relational data.
This is a good solution to provide data into procedures.
But we changed to use the PL/SQL PIPE ROW machanism and sql table operator to read data from stored procedures.
See
http://www.psoug.org/reference/pipelined.html for an example.
There are many advantages using PIPE ROW:
1. you can read the data via the standard JDBC/resultset interface without using oracle specific extensions.
2. You don't have to overload the procedure when attributes are added to the result and you want to be backward compatible.
3. it needs less resources for large arrays because it is piped.
4. Personal performance tests show that the time need is comparable to ref cursors (of cause the fastet solution) and the array interface.
orai18n.jar does the job for NLS support
Anup Toshniwal, February 14, 2008 - 10:14 am UTC
Hi everyone,
Thanks to everyone for their suggestions, but hearty thanks to Stew for his advise on the query raised by me. The code is really working fine now giving desired results after using orai18n.jar along with ojdbc14.jar. This has been tested with a jdk1.4 , oracle 8i environment on Unix, so should work fine with further release combinations.
Regards,
Anup