?
Solved

Returning arrays from stored proc to java ?

Posted on 2001-06-29
2
Medium Priority
?
386 Views
Last Modified: 2012-05-11
For clarity, I'll include my pl/sql code.
My goal is to get the out param "My_Table" with a call from
jdbc. I've searched forever for a working example.
The problem I think is to find a compatable jdbc out parameter i.e. OracleTypes.ARRAY, java.sql.STRUCT.

Any help would be great.  
create or replace type test_type as object
(
a varchar2(50)
)
//package
CREATE OR REPLACE PACKAGE "PKGTEST"                                                                
                      IS

Type t Is Table Of test_type;
Mytable t;
PROCEDURE SelectRows(My_table OUT test_type);
End;
//package body
CREATE OR REPLACE PACKAGE BODY "PKGTEST"    
PROCEDURE SelectRows(My_table OUT test_type)
Is

Begin
My_table:=test_type('a');

END;
END;  
0
Comment
Question by:mespo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
jpk041897 earned 800 total points
ID: 6241866
What you are looking for are the java.sql.Array and oracle.sql.Array classes.

The following example, from the Oracle 8i JDBC Developers Guide and Reference should explain the process:


Weakly Typed Arrays?ArrayExample.java
This sample program uses JDBC to create a table with a VARRAY. It inserts a new
array object into the table, then prints the contents of the table. For more
information on arrays, see Chapter 10, "Working with Oracle Collections".


import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.oracore.Util;
import oracle.jdbc.driver.*;
import java.math.BigDecimal;
public class ArrayExample
{
  public static void main (String args[])
    throws Exception

  {
// Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connect to the database
// You need to put your database name after the @ sign in
// the connection URL.
//
// The sample retrieves an varray of type "NUM_VARRAY",
// materializes the object as an object of type ARRAY.
// A new ARRAY is then inserted into the database.
    Connection conn =
DriverManager.getConnection ("jdbc:oracle:oci8:@",
"scott", "tiger");
// It?s faster when auto commit is off
    conn.setAutoCommit (false);
// Create a Statement
    Statement stmt = conn.createStatement ();
    try
    {
      stmt.execute ("DROP TABLE varray_table");
      stmt.execute ("DROP TYPE num_varray");
    }
    catch (SQLException e)
    {
// the above drop statements will throw exceptions
// if the types and tables did not exist before. Just ingore it.
    }
    stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF     NUMBER(12, 2)");
     stmt.execute ("CREATE TABLE varray_table (col1 num_varray)");
    stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");
    ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);
//now insert a new row
// create a new ARRAY object
    int elements[] = { 300, 400, 500, 600 };
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn);

    ARRAY newArray = new ARRAY(desc, conn, elements);
    PreparedStatement ps =
    conn.prepareStatement ("insert into varray_table values (?)");
    ((OraclePreparedStatement)ps).setARRAY (1, newArray);
    ps.execute ();
    rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);
// Close all the resources
    rs.close();
    ps.close();
    stmt.close();
    conn.close();
  }

  public static void showResultSet (ResultSet rs)
throws SQLException
    {
      int line = 0;
      while (rs.next())
      {
        line++;
        System.out.println("Row "+line+" : ");
        ARRAY array = ((OracleResultSet)rs).getARRAY (1);
        System.out.println ("Array is of type  "+array.getSQLTypeName());
        System.out.println
("Array element is of typecode "+array.getBaseType());
        System.out.println ("Array is of length "+array.length());
// get Array elements
        BigDecimal[] values = (BigDecimal[]) array.getArray();
        for (int i=0; i<values.length; i++)
        {
          BigDecimal value = (BigDecimal) values[i];
          System.out.println(">> index "+i+" = "+value.intValue());
        }
      }
    }
  }



Hope this helps.
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 8950495


No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:


--  points to jpk


Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
sudhakar_koundinya
EE Cleanup Volunteer
---------------------
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post your concern in THIS thread.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month10 days, 23 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question