/*

This code shows how to retrieve various common datatypes off an Oraxle REFCURSOR.

We show how to get the following datatypes off a refcursor

1) number
2) string
3) date
4) clob
5) refcursor (yes, nested refcursors (this makes getting data way easy))
6) xml
7) simple array (we use string array, but number/date (maybe even clob) are also possible)

Some things to keep in mind:

1) java result sets are cast as OracleResultSet
2) there are several ways to deal with arrays, we show only one, converting them to a result set
3) complex (or STRUCTURED) datatypes (ie. complex collections) are not shown here, only simple types and single column arrays of simple types
4) numbers are interesting, as oracles default traslation isn BigDecimal in java, but this is very java expensive
5) dates require getTimstamp to keep the seconds, as getDate will trunc the seconds (notice our use of date formatting for printing)
6) when working with clobs you sometimes must convert it to a string, but java has a max string size (32000 I think)
7) our java is compiled and executed inside oracle, yours will be outside so connection syntax is a little different for you

You see below the following:

1) a table type, we create an oracle object that is essentially a string array definition (nested table of varchar2(4000))
2) we create a function that returns a refcursor
3) this refcursor returns two rows each of which has data of the types mentioned above
4) we can test our function for correctness easily from sqlplus
5) then comes our oracle java routine that calls the function and prints the data returned
6) a plsql wrapper is needed to make the oracle java package executable via normal plsql/sql syntax
7) finally we can turn output on for sqlplus java and run the routine to see the data

*/

create or replace type c_varchar2_4000 is table of varchar2(4000)
/

create or replace package pkg_latha_kevin_xml_test1 is

   function get_some_data return sys_refcursor; 

end; 
/ 
show errors 

create or replace package body pkg_latha_kevin_xml_test1 is 

   function get_some_data return sys_refcursor is 
      rc1_v sys_refcursor; 
   begin 
      open rc1_v for 
                      select 
                               rownum anumber
                             , 'xyz' astring
                             , sysdate adate
                             , to_clob('lots of words') aclob
                             , cursor(select * from dual) anested_rc
                             , dbms_xmlgen.getxmltype('select * from dual') axml
                             , c_varchar2_4000('abc','def','ghi') astringarray
                      from dual
                      connect by level <= 2
      ; 
      return (rc1_v); 
   end; 

end; 
/ 
show errors 

select pkg_latha_kevin_xml_test1 .get_some_data from dual 
/ 

create or replace and compile java source named "JavaGettingRefcursorData" as 

   import oracle.jdbc.driver.*; 
   import oracle.xdb.XMLType;
   import java.sql.*; 
   import java.text.DateFormat;
   import java.text.SimpleDateFormat;

   public class GettingRefcursorData { 

     public static void Refcursordata() throws SQLException { 

        String query = "begin ? := pkg_latha_kevin_xml_test1.get_some_data; end;"; 
	DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

        System.out.println(" query = " + query); 

        Connection conn = new OracleDriver().defaultConnection(); 

        CallableStatement cstmt = conn.prepareCall(query); 
        cstmt.registerOutParameter(1, OracleTypes.CURSOR); 
        cstmt.execute(); 
        OracleResultSet orset = (OracleResultSet)cstmt.getObject(1);

        for (int i = 0; orset.next(); i++) {
            System.out.println("number = " + orset.getInt(1));
//            System.out.println("number = " + orset.getBigDecimal(1));
//            System.out.println("number = " + orset.getFloat(1));
//            System.out.println("number = " + orset.getDouble(1));
            System.out.println("string = " + orset.getString(2));
            System.out.println("date = " + dateFormat.format(orset.getTimestamp(3)));
            System.out.println("clob = " + orset.getClob(4).getSubString(1,4000));
//            System.out.println("clob = " + orset.getString(4));
            OracleResultSet orset2 = (OracleResultSet)orset.getObject(5); 
            for (int j = 0; orset2.next(); j++) {
                System.out.println("nested refcursor column 1 = " + orset2.getString(1));
            }
            orset2.close();
            System.out.println(XMLType.createXML(orset.getOPAQUE(6)).getStringVal());
            orset2 = (OracleResultSet)orset.getARRAY(7).getResultSet();
            for (int j = 0; orset2.next(); j++) {
                System.out.println("array element (index,value) = (" + orset2.getInt(1) + "," + orset2.getString(2) + ")");
            }
            orset2.close();
            System.out.println("  --------");
        }
        ;
        orset.close();
     } 
   } 
/ 
show errors 

create or replace 
procedure show_GettingRefcursorData
as language java 
name 'GettingRefcursorData.Refcursordata()'; 
/ 

CALL DBMS_JAVA.SET_OUTPUT(1000000);
set serveroutput on 
exec show_GettingRefcursorData

create or replace package body pkg_vit_abc_test is

           

function getMembersInfo(             
                p_vit_policy_no       in number,   
                pr_array              out member_array,
                pr_errMessage         out varchar2,
                pr_errCode            out number
            ) is
   
my_member member_object; --member_object defined already-has 3 attributes
       
type member_array is varray(10) of member_object;    
my_arr member_array; 

--temp variables
e_no number :=0;
firstName varchar2(20);
lastName varchar2(20);  

begin  
-- null;       
   e_no:=1002354;
   firstName:='John';
   lastName:='Doe';
   
   my_member.entity_number:=e_no;
   my_member.f_name:=firstName;
   my_member.l_name:=lastName;
   

my_arr(0):=my_member; -I will create more objects and add to varray


--I need to cast this varray to a cursor and return it.

 
end;

                                  
end pkg_vit_abc_test;

create or replace type myobject is object (a number,b number,c number)
/
create or replace type mycollection is varray(10) of myobject
/

declare
--
-- pack a varray of records
--
   varray_v mycollection := mycollection(myobject(1,2,3),myobject(2,3,4),myobject(3,4,5));
   av number;
   bv number;
   cv number;
begin null;
--
-- unpack a varray into is original parts
--
   for r1 in (
               select a,b,c
               from table(cast(varray_v as mycollection)) x
             ) loop
      av := r1.a;
      bv := r1.b;
      cv := r1.c;
   end loop;
end;
/

--
-- both at the same time in sql
--
select *
from table(cast(
                 mycollection(myobject(1,2,3),myobject(2,3,4),myobject(3,4,5))
                as mycollection
               )
          )
/