Kid101 Kid101 - 3 months ago 12
Java Question

how to return a cursor along with new variable added to each row?

I want to call a procedure from java.

Example:
CREATE OR REPLACE PROCEDURE helloworld
AS
cursor data_test is
with required_data as( select *
from EMPLOYEES) select * from required_data rd join DEPARTMENTS d on d.department_id=rd.DEPARTMENT_ID ;
emp_rec data_test%ROWTYPE;
more_detail varchar2(50);
BEGIN
open data_test;
LOOP
FETCH data_test INTO emp_rec;
exit when data_test%NOTFOUND;
more_detail =BLA.GET_MORE_DATA(data_test.empid);// Lets say the fuction return varchar2 I've total of 4 similar fields to be added in total
END LOOP;
END;
/


Now how do I return all the data in the cursor(data_test ) along with more_detail in each row?? i.e I want my ResultSet in java to have all the values from the select statement as well as the value of more_detail.Also will my helloworld have out parameter as SYS_REFCURSOR or something else?? and when calling from java all I've to do is create a callable statement and register out parameter as Cursor to use this?

Answer

Well, when I look at it today, How could I ask this question. This so wrong. I didn't have much understanding of PLSQL then and asked the question without reading much. Anyway, there are many ways to solve it as I mentioned in the comment one can send back an associative array but that is a pain on the java side if you don't know the number of expected result. or one can create a nested array with an user with defined object something like this:

CREATE OR REPLACE TYPE keyvalue AS object (col Number(10), col2 VARCHAR2(30));
CREATE OR replace TYPE map IS TABLE OF keyvalue ;

hear's how I did it. You cannot add a column to cursor but you can create you own custom defined data type.

    CREATE OR REPLACE TYPE dbObject AS OBJECT
    (
       empId NUMBER (6),// here add as many fields you want to return or want your object to have
       emailId VARCHAR2 (25),
       hiredate DATE
    );
CREATE OR REPLACE TYPE datalist IS TABLE OF DBOBJECT;

PROCEDURE get_emp_data (list OUT datalist)
   AS
      CURSOR emp_cursor
      IS
         SELECT employee_id AS empId,
                EMPLOYEES.EMAIL AS emailId,
                EMPLOYEES.HIRE_DATE AS hiring
           FROM EMPLOYEES;
           c_datatype emp_cursor%rowtype;

   BEGIN
  OPEN emp_cursor();
      list := datalist();
      LOOP
        fetch emp_cursor into c_datatype;
         EXIT WHEN emp_cursor%NOTFOUND;
         list.extend;
         list(emp_cursor%ROWCOUNT):=DBOBJECT(c_datatype.empId,c_datatype.emailId,c_datatype.hiring);         
      END LOOP;
   END get_emp_data;

Now you want to call this from java: here is the code:

String dataTypeName = "DBOBJECT";
        String dataTypeListName = "datalist";
        StructDescriptor structDescriptor = StructDescriptor.createDescriptor(dataTypeName.toUpperCase(), connection);      
        ResultSetMetaData metaData = structDescriptor.getMetaData();
        CallableStatement cs = connection.prepareCall("{call TEST_PACKAGE.get_emp_data(?)}");
        cs.registerOutParameter(1, OracleTypes.ARRAY, dataTypeListName.toUpperCase());      
        cs.execute();
        Object[] data = (Object[]) ((Array)cs.getObject(1)).getArray();
        for(Object tmp : data) {
            Struct row = (Struct) tmp;
            int index = 1;
            for(Object attribute : row.getAttributes()) {               
                System.out.println(metaData.getColumnName(index) + " : " + attribute);                                          
                ++index ;
            }

        }
        cs.close();
Comments