sample:
import java.sql.*;
import java.math.*;
import java.util.*;
import oracle.sql.*;
import oracle.jdbc2.*;
import oracle.jdbc.driver.*;
// This is the Java Stored Procedure portion of a sample program that
// demonstrates how to pass a PLSQL table or varray to a Java Stored
// Procedure and materialize the parameter as a ResultSet.
//
// Other related files include:
//
// Types.sql -- this creates all of the database types
// PassTable.sql -- this creates the PLSQL package containing
// a driver PLSQL procedure and the call spec
// corresponding to the JSP herein.
public class TableParamTest { // Must be public static to use as a Java Stored Procedure public static void ReceiveTable (ARRAY tbl) throws SQLException, ClassNotFoundException { // Retrieve the contents of the table/varray as a result set ResultSet rs = tbl.getResultSet(); // Iterate through the rows returned by the result set // // NOTE: The JDBC 2.0 standard specifies that the ResultSet // contains rows consisting of two columns. // // Column 1 stores the element index for the row. // Column 2 stores the actual element value. // for (int ndx = 0; ndx < tbl.length(); ndx++) { rs.next(); // Retrieve the array index int aryndx = (int)rs.getInt(1); System.out.println("the array index is " + Integer.toString(aryndx)); // Retrieve the array element (an object // returned as type STRUCT) STRUCT obj = (STRUCT)rs.getObject(2); // Retrieve the attributes for the object // as an array of Java Objects Object[] attrs = obj.getAttributes(); // Retrieve the individual attributes // // Note: It is the application developer's // responsibility to know the type // and order of the attributes. BigDecimal attr1 = (BigDecimal)attrs[0]; String attr2 = (String)attrs[1]; STRUCT obj2 = (STRUCT)attrs[2]; // Retrieve the attributes from the nested object Object[] attrs2 = obj2.getAttributes(); BigDecimal objattr1 = (BigDecimal)attrs2[0]; String objattr2 = (String)attrs2[1]; // Print the results System.out.println("tbl(" + ndx + ") = "); System.out.println("attr1 = " + attr1); System.out.println("attr2 = " + attr2); System.out.println("attr3.attr1 = " + objattr1); System.out.println("attr3.attr2 = " + objattr2); } // Close the result set rs.close(); } }
import java.sql.*;
import java.math.*;
import java.util.*;
import oracle.sql.*;
import oracle.jdbc2.*;
import oracle.jdbc.driver.*;
// This is the Java Stored Procedure portion of a sample program that
// demonstrates how to pass a PLSQL table or varray to a Java Stored
// Procedure and materialize the parameter as a ResultSet.
//
// Other related files include:
//
// Types.sql -- this creates all of the database types
// PassTable.sql -- this creates the PLSQL package containing
// a driver PLSQL procedure and the call spec
// corresponding to the JSP herein.
public class TableParamTest { // Must be public static to use as a Java Stored Procedure public static void ReceiveTable (ARRAY tbl) throws SQLException, ClassNotFoundException { // Retrieve the contents of the table/varray as a result set ResultSet rs = tbl.getResultSet(); // Iterate through the rows returned by the result set // // NOTE: The JDBC 2.0 standard specifies that the ResultSet // contains rows consisting of two columns. // // Column 1 stores the element index for the row. // Column 2 stores the actual element value. // for (int ndx = 0; ndx < tbl.length(); ndx++) { rs.next(); // Retrieve the array index int aryndx = (int)rs.getInt(1); System.out.println("the array index is " + Integer.toString(aryndx)); // Retrieve the array element (an object // returned as type STRUCT) STRUCT obj = (STRUCT)rs.getObject(2); // Retrieve the attributes for the object // as an array of Java Objects Object[] attrs = obj.getAttributes(); // Retrieve the individual attributes // // Note: It is the application developer's // responsibility to know the type // and order of the attributes. BigDecimal attr1 = (BigDecimal)attrs[0]; String attr2 = (String)attrs[1]; STRUCT obj2 = (STRUCT)attrs[2]; // Retrieve the attributes from the nested object Object[] attrs2 = obj2.getAttributes(); BigDecimal objattr1 = (BigDecimal)attrs2[0]; String objattr2 = (String)attrs2[1]; // Print the results System.out.println("tbl(" + ndx + ") = "); System.out.println("attr1 = " + attr1); System.out.println("attr2 = " + attr2); System.out.println("attr3.attr1 = " + objattr1); System.out.println("attr3.attr2 = " + objattr2); } // Close the result set rs.close(); } }
drop type ATable;
drop type AnotherObject;
drop type AnObject;
create or replace type AnObject as object ( attr1 number, attr2 varchar2(10) ); /
create or replace type AnotherObject as object
( attr1 number,
attr2 varchar2(10),
attr3 AnObject );
/
create or replace type ATable as table of AnotherObject;
/
create or replace type AnArray as varray(10) of AnotherObject;
/
Save the following script as the file PassTable.sql: create or replace package TableTest is
procedure PassTable;
procedure ReceiveTable(tbl ATable);
procedure ReceiveArray(ary AnArray);
end TableTest;
/
create or replace package body TableTest is
procedure PassTable is
aTab ATable := ATable();
anAry AnArray := AnArray();
begin
aTab.extend(2);
aTab(1) := AnotherObject(1,'One',AnObject(2, 'Two'));
aTab(2) := AnotherObject(3,'Three',AnObject(4, 'Four'));
ReceiveTable(aTab);
anAry.extend(2);
anAry(1) := AnotherObject(1,'One',AnObject(2, 'Two'));
anAry(2) := AnotherObject(3,'Three',AnObject(4, 'Four'));
ReceiveArray(anAry);
end;
procedure ReceiveTable(tbl ATable) is
language java name 'TableParamTest.ReceiveTable(oracle.sql.ARRAY)';
procedure ReceiveArray(ary AnArray) is
language java name 'TableParamTest.ReceiveTable(oracle.sql.ARRAY)';
end TableTest;
/