Solved

Returning arrays from stored proc to java ?

Posted on 2001-06-29
2
382 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
egit plugin on eclipse 8 102
Delete Android all or certain applications data programmatically 9 113
Eclipse for Java EE development 2 45
Java 8 to Java 6 8 33
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…
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…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

730 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