crystal reports gs1-128 USING ORACLE COLLECTIONS AND REFERENCES in Font

Creation Code 3 of 9 in Font USING ORACLE COLLECTIONS AND REFERENCES

As the first step, we prepare the statement that selects the varray column of varchar2 elements from the table varchar_varray_table. We then execute the statement and obtain the ResultSet: String stmtString = "select varray_column from varchar_varray_table"; pstmt = conn.prepareStatement( stmtString ); // Step 2 - execute the statement and get the result set rset = pstmt.executeQuery(); while( rset.next() ) { We then use the getArray() method of the ResultSet interface to retrieve the array. Note that alternatively we could have used the Oracle extension method getARRAY(), also in the interface OracleResultSet. That makes your code ever so slightly more dependent on the Oracle proprietary interface; otherwise, there is no difference between the two methods. array =( ARRAY ) rset.getArray(1); The methods _doUseGetArray(), _doUseResultSet(), and _doUseGetOracleArray() use the methods getArray() of the Array interface, getResultSet() of the Array interface, and getOracleArray() of the ARRAY class, respectively. I explain each of these methods in more detail alongside their definition shortly. _doUseGetArray( array ); _doUseResultSet( array ); _doUseGetOracleArray( array ); } } finally { JDBCUtil.close( rset); JDBCUtil.close( pstmt); } } Using the Standard getArray() Method of the Array Interface In the definition of the method _doUseGetArray(), we use the method getArray() to retrieve the collection elements. In this case, Oracle automatically converts the database collection into an array in Java, the elements of which are Java objects of types based on the conversion in Table A-1 in the Appendix. For example, in our case we have a varray of varchar2 elements, so the array itself is returned as an array of String objects. In the case of the number array, the array is returned as an array of java.math.BigDecimal objects, and so on. private static void _doUseGetArray( ARRAY array ) throws SQLException { System.out.println("In _doUseGetArray"); // Since varchar2 maps by default to String, // we can typecast the results to a String array. String[] arrayInJava = (String[])array.getArray();

for( int i=0; i < arrayInJava.length; i++ ) { System.out.println(arrayInJava[i]); } System.out.println("Exiting _doUseGetArray"); } Using the Oracle Extension Method getOracleArray() When you use the Oracle extension method getOracleArray(), the elements are retrieved into an oracle.sql.Datum[] array. A Datum is an interface in the oracle.sql package that represents the SQL data type stored in Java in Oracle internal format. This interface is implemented by all oracle.sql.* classes, such as oracle.sql.CHAR, etc. This is the format in which Oracle JDBC natively retrieves the database objects. If you use these objects as is, you can avoid the overhead of converting the objects into any other form. This can lead to performance improvement, as you ll see shortly. However, keep in mind that your application may become somewhat more complex since many standard utility classes don t recognize the Oracle proprietary classes such as oracle.sql.NUMBER. For example, to work with a utility in the java.lang.Math class that takes a double, you would have to use the method doubleValue() in oracle.sql.NUMBER. private static void _doUseGetOracleArray( ARRAY array ) throws SQLException { System.out.println("In _doUseGetOracleArray"); Datum[] arrayElements = (Datum[])array.getOracleArray(); for( int i=0; i < arrayElements.length; i++ ) { System.out.println((CHAR)arrayElements[i]); } System.out.println("Exiting _doUseGetOracleArray"); } Using the Standard Method getResultSet() The getResultSet() method of the standard Array interface returns a result set, each row of which contains two columns. The first column is the array object s index, and the second column is the value. In the case of varrays, the index represents the object s position in the varray; in the case of a nested table, it represents the order in which the elements were returned for a given invocation (recall that the nested table is an unordered collection). The method _doUseResultSet() illustrates this: private static void _doUseResultSet( ARRAY array ) throws SQLException { System.out.println("In _doUseResultSet"); ResultSet rset = null; try { rset = array.getResultSet(); while( rset.next() ) {