Use of table variables in Oracle in JDBC

1. First define a method to return the array type
Create or replace type my_table_type is table of varchar2 (20 );
 
Create or replace function func
Return my_table_type
Is
I my_table_type: = my_table_type ();
Begin
Select name bulk collect into I from emps;
Return I;
End;
 
2. Call in JDBC. If the returned table variable is
 
Java code
Public void SelectAgus (String SQL)
{
OracleCallableStatement call = null;
Try
{
Call = (OracleCallableStatement) con. prepareCall (SQL );
// If table is returned, ARRAY is used. If the returned OBJECT is returned, STRUCT is used.
// The third parameter defines the table type name.
Call. registerOutParameter (1, OracleTypes. ARRAY, "MY_TABLE_TYPE ");
Call.exe cute ();
// Obtain the first parameter (return value)
ARRAY array = call. getARRAY (1 );
// Obtain the elements in the table
Datum [] dat = array. getOracleArray ();
// Print the traversal in sequence
For (Datum d: dat)
{
System. out. println (new String (d. getBytes ()));
}
} Catch (Exception e)
{
E. printStackTrace ();
}
}
2. If the nested table structure is defined,
Definition:
Create or replace type all_table is object (id number, name varchar2 (20 ));
Create or replace type emps_table_type is table of all_table;
-- Create a function. The return type is emps_table_type;
Create or replace function funcc
Return emps_table_type
Is
I emps_table_type;
Begin
-- Read all the values of ID and NAME in emps to I.
Select all_table (id, name) bulk collect into I from emps;
Return I; -- return table
End;

Java code
Public void SelectAgus (String SQL)
{
OracleCallableStatement call = null;
Try
{
Call = (OracleCallableStatement) con. prepareCall (SQL );
Call. registerOutParameter (1, OracleTypes. ARRAY, "EMPS_TABLE_TYPE ");
Call.exe cute ();
ARRAY array = call. getARRAY (1 );
Datum [] dat = array. getOracleArray ();
For (Datum d: dat)
{// Obtain the elements in a row
STRUCT struct = (STRUCT) d;
// Garbled characters may occur here, so I use two methods to obtain elements respectively.
Datum [] d1 = struct. getOracleAttributes ();
Object [] d2 = struct. getAttributes ();
System. out. println ("ID =" + d2 [0] + "" + "NAME =" +
New String (d1 [1]. getBytes ()));
}

} Catch (Exception e ){
E. printStackTrace ();
}
}

 

Author "aazham"