jdbc return array from plsql

plsql

A simple snippet getting an array of numbers through JDBC from a PL/SQL function.

 

Define the oracle type.

 

 

CREATE OR REPLACE
TYPE        scott.number_array    AS table of NUMBER(10) ;


--Define the PL\SQL function


create or replace function getNumArray
  return number_array
as
 vdata  number_array := number_array();
begin
  for i in 1 .. 10 loop
    vdata.extend;
      vdata(vdata.count) :=  i;
  end loop;
  return vdata;
end;

 

The Java Code Snippet.

 

List numberList = new ArrayList<>();
 try(Connection connection = db.getConnection){
    try(CallableStatemet cs = connection.prepareCall("{ call ? := getNumArray }")){
         cs.registerOutParameter( 1,  OracleTypes.ARRAY, "number_array" );
         cs.execute();
         try(ResultSet rs = ((OracleCallableStatement)cs).getArray(1).getResultSet()){
             while(rs.next()){
                 numberList .add(rs.getLong(2));
            }
         };
     
   }
}catch(Exception e){
   e.printStackTrace();
}