Re: Return VArray to Java through JDBC?

From: Gavin D'mello <gavin_d_at_tis.co.in>
Date: Thu, 20 Dec 2001 22:33:15 -0800
Message-ID: <F001.003E17B3.20011220220018@fatcity.com>

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" <ORACLE-L_at_fatcity.com> 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: ccharvest_at_yahoo.com

>


> 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: ListGuru_at_fatcity.com (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: gavin_d_at_tis.co.in

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: ListGuru_at_fatcity.com (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).

Received on Fri Dec 21 2001 - 00:33:15 CST