Yep I've done this using one array only, I am attaching my java code which i
used for this purpose. I qam using Weblogic 5 with the Oracle Thin driver.
Gavin
1)My VARRAY of VARCHAR2 TYPE
===========================
CREATE TYPE taxonomy AS VARRAY(5) OF VARCHAR2(40);
2)My Java code:
============
String lid="";
sqlstmt = "begin mhlntrekkerpkg.gettaxonomy (:1,:2); end;";
cs = (OracleCallableStatement)conn.prepareCall(sqlstmt);
cs.registerOutParameter(1,Types.VARCHAR);
cs.setString (1, arg[0]);
cs.registerOutParameter(2,OracleTypes.ARRAY,"TLS.TAXONOMY");
//TLS is the user, TAXONOMY is the type of the varray
//make sure user.type as above is in caps, it worked only when I turned
it to upper case.
cs.execute();
lid = cs.getString(1);
System.out.println ("lid is : " + lid);
if ((lid != "")&&!(lid.equals("0")))
{
System.out.println ("lid condition : " + (lid != "0"));
ARRAY simpleArray = cs.getARRAY(2);
String[] values = (String[])simpleArray.getArray();
for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" + values[i] + "'" );
}
else
{
System.out.println ("No Data Found");
}
String sqlstmt = "begin mhlntrekkerpkg.getbyfamousperson (:1,:2); end;";
cs = (OracleCallableStatement)conn.prepareCall(sqlstmt);
cs.setString (1, "Pearson");
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet cursor = (ResultSet)cs.getObject(2);
System.out.println("ID FIRSTNAME LASTNAME");
String sqlst="";
while(cursor.next())
{
System.out.println(cursor.getString(1) +" "+
cursor.getString(2) +" "+ cursor.getString(3));
sqlst = cursor.getString(1);
}
sqlstmt = "begin mhlntrekkerpkg.getfamouspersonlinks (:1,:2); end;";
cs = (OracleCallableStatement)conn.prepareCall(sqlstmt);
cs.setString (1, sqlst);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
cursor = (ResultSet)cs.getObject(2);
System.out.println("RESULT DETAILS");
while(cursor.next())
System.out.println(cursor.getString(1) +" "+
cursor.getString(2) +" "+ cursor.getString(3));
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, December 20, 2001 9:20 AM
> Does anyone has the similar experience?
> I plan to write a stored procedure to return 3 arrays.
> It works fine in the database.
> But if I call it from Java codes, I got tons of
> errors.
> (the JDBC drivers is from Web Logic, not from Oracle).
>
> It would be wonderful if anyone is willing to share
> the codes.
>
> Merry Christmas to you all.
>
> CC
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: CC Harvest
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gavin D'mello
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
