Not sure how to solve the problem, but you are correct varrays are limited
to 4095 or 4096. Nested tables are not and they do't require you to rewrite
the entire array if youchange one element.
Jim
"christian kropf" <christian.kropf_at_infineon.com> wrote in message
news:15157ac8.0204190419.232ed601_at_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 - 09:27:49 CDT
