Window 2 World

create types in oracle

1) create or replace TYPE IDHolderList IS TABLE OF NUMBER;
2) create public synonym S_IDHOLDERLIST for IDHOLDERLIST; -- incase of multiple schemas create this public synonym

3) write the procedure

PROCEDURE prc_test(v_IDList OUT s_IDHolderList)
IS
BEGIN -- START OF PROCEDURE

v_IDList.EXTEND;
v_IDList(v_IDList.COUNT) := 1;
v_IDList.EXTEND;
v_IDList(v_IDList.COUNT) := 2;

END prc_test;


4) java code

// sepcial imports needed
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import java.math.BigDecimal;

// sample code
Connection objConnection = getConnection();
OracleCallableStatement objCall = null;
objCall = (OracleCallableStatement)objConnection.prepareCall("call prc_test(?)");
objCall.registerOutParameter(3,OracleTypes.ARRAY,"S_IDHOLDERLIST");
objCall.execute();
ARRAY simpleArray = objCall.getARRAY(3);
if(simpleArray!=null){
BigDecimal[] values = (BigDecimal[])simpleArray.getArray();
}



few clarifications about oracle collection types
------------------------------------------------

PL/SQL offers these collection types:

Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Arrays in other languages become VARRAYs in PL/SQL.

Sets and bags in other languages become nested tables in PL/SQL.

Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.

-------------

Choosing Between Nested Tables and Associative Arrays
Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables are appropriate for important data relationships that must be stored persistently.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers when appropriate.

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to associative arrays.

Choosing Between Nested Tables and Varrays
Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the table is stored and retrieved, because the order and subscripts are not preserved when a nested table is stored in the database.