[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Returning arrays from stored proc to java ?

Posted on 2001-06-29
2
Medium Priority
?
389 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
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 learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This video teaches viewers about errors in exception handling.
Suggested Courses

656 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