VARRAY size limitation problems (>4095)

From: christian kropf <christian.kropf_at_infineon.com>
Date: 19 Apr 2002 05:19:42 -0700
Message-ID: <15157ac8.0204190419.232ed601@posting.google.com>

Hello NG,

need help by the following problem:

We've been using VARRAYs and NestedTables thru Java(JDBC) to upload data to a table.
Unfortunately, if we make the VARRAY size (or the row size in the NestedTable)
larger than 4095, the following happens:

Every block of 4095 entries is cut off and only the first entries were send to the table.

Example:

We want to store a VARRAY of 8200 rows,
but only the first 10 entries are stored in the table! (8200 - 4095 - 4095 = 10)

//--------------------------------------------------------------------

Here's the used source (original Oracle-docu):

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 @ symbol in
// the connection URL.
//
// The sample retrieves an varray of type "NUM_VARRAY" and
// materializes the object as an object of type ARRAY.
// A new ARRAY is then inserted into the database.

// Please replace hostname, port_number and sid_name with
// the appropriate values

    Connection conn =
      DriverManager.getConnection("jdbc:oracle:oci8:scott/tiger_at_myDatabase");

// It's faster when auto commit is off
    conn.setAutoCommit (false);

// Create a Statement

    Statement stmt = conn.createStatement ();

    try
    {

      stmt.execute ("DROP TABLE Rvarray_table");
      stmt.execute ("DROP TYPE Rnum_varray");

    }
    catch (SQLException e)
    {

      // the above drop statements will throw exceptions
      // if the types and tables did not exist before
    }                                                       

    stmt.execute ("CREATE TYPE Rnum_varray AS VARRAY(8200) OF NUMBER(12, 2)");
    stmt.execute ("CREATE TABLE Rvarray_table (col1 Rnum_varray)");  

//now insert a new row

// create a new ARRAY object

    int elements[] = new int[8200];

    for(int i=0; i<8200; i++)
    {
      elements[i] = i * 10;
    }
    ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("RNUM_VARRAY", conn);

    ARRAY newArray = new ARRAY(desc, conn, elements);                                                             

    PreparedStatement ps =
      conn.prepareStatement ("INSERT INTO Rvarray_table VALUES (?)");     ((OraclePreparedStatement)ps).setARRAY (1, newArray);

    ps.execute ();

    rs = stmt.executeQuery("SELECT * FROM Rvarray_table");     showResultSet (rs);

  }                                                         
  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 type code" +
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 = values[i];
        System.out.println(">> index " + i + " = " +
value.intValue());
      }
    }
  }       
}                                                           
// --------------------------------------------------------------------
 
The Oracle version being used: 	Oracle server 8.1.7 
Driver for connection: 		jdbc:oracle:oci8

Any Suggestions would be greatly appreciated, Thank-You in Advance.

Christian Kropf (germany) Received on Fri Apr 19 2002 - 07:19:42 CDT