Solved

Returning arrays from stored proc to java ?

Posted on 2001-06-29
2
381 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
2 Comments
 
LVL 6

Accepted Solution

by:
jpk041897 earned 200 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
custom annotations 9 42
print map entry 34 72
windows explorer path to command prompt 5 54
Using jdbcTemplate.batchUpdate to improve INSERT performance 6 20
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This video teaches viewers about errors in exception handling.

790 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