Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Returning arrays from stored proc to java ?

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

581 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