Retrieving Array from oracle
1 posts in topic
Flat View  Flat View

Posted By:   Abhay_Shukla
Posted On:   Thursday, November 1, 2001 11:08 AM

I want to send an array of integers to the database. The database the performs some checks on the values of this array and returns the valid ones back.

How do I do this?

Re: Retrieving Array from oracle

Posted By:   Bernie_Acs  
Posted On:   Tuesday, November 6, 2001 10:05 AM

For a complete discussion and example reference :

Oracle8i JDBC Developer's Guide and Reference

Release 2 (8.1.6)

the following clips are from the above references

The following example assumes that a connection object conn and a statement object stmt have already been created. In the example, an array with the SQL type name NUM_ARRAY is created to store a VARRAY of NUMBER data. The NUM_ARRAY is in turn stored in a table VARRAY_TABLE.

A query selects the contents of the VARRAY_TABLE. The result set is cast to an OracleResultSet object; getARRAY() is applied to it to retrieve the array data into my_array, which is an oracle.sql.ARRAY object.

Because my_array is of type oracle.sql.ARRAY, you can apply the methods getSQLTypeName() and getBaseType() to it to return the name of the SQL type of each element in the array and its integer code.

The program then prints the contents of the array. Because the contents of my_array are of the SQL datatype NUMBER, it must first be cast to the BigDecimal datatype. In the for loop, the individual values of the array are cast to BigDecimal and printed to standard output.

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");
ARRAY my_array = ((OracleResultSet)rs).getARRAY(1);

// return the SQL type names, integer codes,
// and lengths of the columns
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[]) my_array.getArray();

for (int i=0; i{
BigDecimal out_value = (BigDecimal) values[i];
System.out.println(">> index " + i + " = " + out_value.intValue());

Note that if you use getResultSet() to obtain the array, you would first get the result set object, then use the next() method to iterate through it. Notice the use of the parameter indexes in the getInt() method to retrieve the element index and the element value.

ResultSet rset = my_array.getResultSet();
while (rset.next())
// The first column contains the element index and the
// second column contains the element value
System.out.println(">> index " + rset.getInt(1)+" = " + rset.getInt(2));

Of course you may be using a different version of Oracle but you should be able to find a compliment sample in your Oracle JDBC Reference.

As input

Prepared Statement:

ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor(sql_type_name, connection);

ARRAY array = new ARRAY(arraydesc, connection, elements);
// Create a java.sql.PreparedStatement object containing the SQL statement to execute.
(OraclePreparedStatement)stmt.setARRAY(parameterIndex, array);

Callable Statement:

OracleCallableStatement ocs =
(OracleCallableStatement)conn.prepareCall("{? = call func()}");
(int param_index, int sql_type, string sql_type_name);
//Where param_index is the parameter
// index, sql_type is the SQL typecode,
// and sql_type_name is the name of the
// array type. In this case, the
// sql_type is OracleTypes.ARRAY.
oracle.sql.ARRAY array = ocs.getARRAY(1);

About | Sitemap | Contact