Stored procedure which returns array

Hi do anyone know how to get result set in an array from a stored procedure
I have the following stroed procedure which returns an array
PROCEDURE getModels
(p_Year_Code IN VARCHAR2,
p_Make_Code IN VARCHAR2,
P_Model_Id OUT t_Model_Id,
p_Model_Name OUT t_Model_Name) IS

v_count NUMBER := 0;

CURSOR c_Models IS
SELECT dm.model_code, mv.value_name
FROM EVDB.DCT_MODEL dm, EVDB.MDB_VALUE mv
WHERE dm.model_code = mv.value_code
AND dm.make_code = p_Make_Code
AND dm.year_code = p_Year_Code
ORDER BY mv.value_name;

BEGIN
FOR r_Models IN c_Models
LOOP
--FILL THE RECORDSET
v_count := v_count + 1;
p_Model_Id(v_count) := r_Models.model_code;
p_Model_Name(v_count) := r_Models.value_name;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
CLOSE c_Models;
DBMS_OUTPUT.PUT_LINE('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END; --Procedure getModels
The output parameter t_model_id and t_model_name are defined in package spec as
TYPE t_Model_Id IS TABLE OF EVDB.MDB_VALUE.VALUE_CODE%TYPE INDEX BY BINARY_INTEGER;
TYPE t_Model_Name IS TABLE OF EVDB.MDB_VALUE.VALUE_NAME%TYPE INDEX BY BINARY_INTEGER;
When i call the stored procedure what exactly should i declare the output parameter as it should be an array or what
Any code snippet will be really helpful
Thanks

To get an array from ResultSet, you will need to use Oracle JDBC Extensions. You will need to create ArrayDescriptor first describing your array and use OracleTypes.ARRAY to get the object from resultset.

Havent tried using java.sql.Types.ARRAY. Could be interesting to check.

As a side note...instead of populating tables proecdurally in PL/SQL tables you can also try using SQL single BULK COLLECT syntax.to improve the performance.

Thanks Neelesh,
To be frank i am clueless how to get the records, i was able to call stored procedures from java which returns a varcahr or a weak cursor as an out variable but never tried with this kind of stored proc. I pasted the code below of how exactly i am calling this stored procedure. The detailed stored procedure is mentioned in my first mesage of thsi topic

String query = null;
CallableStatement stmt = conn.prepareCall("{call EVDB.DCT_2_0.getModels(?,?,?,?)}");
stmt.setString(1,"K01439");
stmt.setString(2,"K00014");
stmt.registerOutParameter(3,OracleTypes.ARRAY);
stmt.registerOutParameter(4,OracleTypes.ARRAY);
ResultSet rs= stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stmt.close();
Thanks

Thanks Neelesh,
To be frank i am clueless how to get the records, i was able to call stored procedures from java which returns a varcahr or a weak cursor as an out variable but never tried with this kind of stored proc. I pasted the code below of how exactly i am calling this stored procedure. The detailed stored procedure is mentioned in my first mesage of thsi topic

String query = null;
CallableStatement stmt = conn.prepareCall("{call EVDB.DCT_2_0.getModels(?,?,?,?)}");
stmt.setString(1,"K01439");
stmt.setString(2,"K00014");
stmt.registerOutParameter(3,OracleTypes.ARRAY);
stmt.registerOutParameter(4,OracleTypes.ARRAY);
ResultSet rs= stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stmt.close();
Thanks

CREATE or REPLACE TYPE myStringArray AS TABLE OF VARCHAR2(40);
/

CREATE OR REPLACE TYPE myNumberArray AS TABLE OF NUMBER;
/

CREATE or REPLACE PACKAGE UTILS
AS

PROCEDURE getEmpDept ( deptNo IN NUMBER, dNames OUT myStringArray, empNos OUT myNumberArray);
END;

/

CREATE or REPLACE PACKAGE BODY UTILS
AS
PROCEDURE getEmpDept ( deptNo IN NUMBER, dNames OUT myStringArray, empNos OUT myNumberArray)
IS
BEGIN
SELECT d.dname, e.empNo BULK COLLECT INTO dNames, empNos
FROM emp e, dept d
WHERE e.deptNo = d.deptNo;
END;
END;
/

show errors

ArrayDescriptor stringDesc = ArrayDescriptor.createDescriptor("SCOTT.MYSTRINGARRAY",connection);
ArrayDescriptor numberDesc = ArrayDescriptor.createDescriptor("SCOTT.MYNUMBERARRAY",connection);

CallableStatement procCall = null;

procCall = connection.prepareCall("{ call UTILS.getEmpDept(?,?,?)}");

procCall.setInt(1,10);

procCall.registerOutParameter(2,OracleTypes.ARRAY,"SCOTT.MYSTRINGARRAY");

procCall.registerOutParameter(3,OracleTypes.ARRAY,"SCOTT.MYNUMBERARRAY");

procCall.execute();

oracle.sql.ARRAY dNames = ((OracleCallableStatement)procCall).getArray(2);

oracle.sql.ARRAY empNos = ((OracleCallableStatement)procCall).getArray(3);

// OR
//oracle.sql.ARRAY dNames = (Oracle.sql.ARRAY)procCall.getObject(2);

Now you can use methods on class ARRAY to get specific arrays. Since oracle.sql.ARRAY implements java.sql.Array, you can use methods
in that interface too.

String[] deptNames = (String[])dNames.getArray();

int[] empNumbers = (int[])empNos.getArray();

However.. I havent really tested this code. Its of top my head. I am also working on trying to find something where I donot have to use
Oracle specific extensions. If I find something I will post it.

Let me know if this helps.

check out the following thread.

http://www.coderanch.com/forums/

I have done the same way using Oracle DB and it works fine for me.

Rashid,

Since you are gonna get Array as OUT parameter...you wont need ArrayDescriptor here.

Taking your example code it will look like

String query = null;
CallableStatement stmt = conn.prepareCall("{call EVDB.DCT_2_0.getModels(?,?,?,?)}");
stmt.setString(1,"K01439");
stmt.setString(2,"K00014");
stmt.registerOutParameter(3,OracleTypes.ARRAY,"T_MODEL_ID"); //
//stmt.registerOutParameter(3,2003);
stmt.registerOutParameter(4,OracleTypes.ARRAY,"T_MODEL_NAME");
stmt.execute();
// Assuming your T_MODEL_ID is int
Array sqlArray = stmt.getArray(2);

int[] ids = (int[])sqlArray.getArray();

sqlArray = stmt.getArray(3);

String[] names = (String[])sqlArray.getArray();
}
stmt.close();

Thanks Neelesh
I defined the array descriptor and it worked. When i dont define the array descriptor and directly use Oracletypes.ARRAY it is giving error.
As for now i think i can use your code to make it run
Thanks

I get an invalid name pattern t_Model_Id when i use the code like follows

String query = null;
CallableStatement stmt = conn.prepareCall("{call EVDB.DCT_2_0.getModels(?,?,?,?)}");
stmt.setString(1,"K01439");
stmt.setString(2,"K00014");
//The line in bold throws the error
stmt.registerOutParameter(3,OracleTypes.ARRAY,"t_Model_Id");

stmt.registerOutParameter(4,OracleTypes.ARRAY,"t_Model_Name");
stmt.execute();
Array sqlArray = stmt.getArray(2);
//int[] ids = (int[])sqlArray.getArray();
String []ids = (String[])sqlArray.getArray();
sqlArray = stmt.getArray(3);
String[] names = (String[])sqlArray.getArray();

stmt.close();
stmt.registerOutParameter(3,OracleTypes.ARRAY,"t_Model_Id");

oops sorry Rasheed..didn't check messages for a while. Have you figured your problem out yet ? If not then try following

stmt.registerOutParameter(3,Types.ARRAY,"T_MODEL_ID");

I think, the type needs to be in CAPSLOCK.

If this doesn't work then probably the DB user of your connection object may not have direct access to this type. So try -

stmt.registerOutParameter(3,Types.ARRAY,"<OracleUserName>.T_MODEL_ID");

Hope you have already figured this out. Just in case not then.. it should help.

nilesh
neilindallas@hotmail.com

thanks neelesh,
i made it work using the first technique which u described to define types for each return column. Well i am faced with other problem now
i have a stored procedure which has an output para as a cursor this procedure was given by a database guys here is hte code for this
procedure GetValues(oCursor out sys_refcursor
,istatus in number
,icontext in number
,isort in varchar2
,iascdesc in number
,ilanguage in number
,ilang_srch in number
,isrch_strg in varchar2) is
v_select varchar2(1000);
v_from varchar2(1000);
v_where varchar2(1000);
v_order varchar2(1000) := 'order by ';
c_updated varchar2(15) := ' t.Update_Date'; /* updated_date will always be the last field in the order by clause */

begin

v_select := 'select t.original_string, t.translated_string, ' ||
'to_char(t.update_date, ' || '''' || 'mm/dd/yyyy' || '''' ||
'), c.context_name, s.status, t.translation_id ';
v_from := 'from context c, translation t, context_translation_map ct, status s ';
v_where := 'where c.context_id = ct.context_id ' ||
'and ct.translation_id = t.translation_id ' ||
'and s.status_id = t.status_id ' ||
'and t.language_id = ' || to_char(ilanguage) || ' ';

if istatus = -1 then /* -1 means all values except published and new */
v_where := v_where || 'and t.status_id > 1 ';
else
v_where := v_where || 'and t.status_id = ' || to_char(istatus) || ' ';
end if;

if icontext > 0 then /* -1 means all values of context */
v_where := v_where || 'and c.context_id = ' || to_char(icontext) || ' ';
end if;

if ilang_srch = 0 then /* -1 means don't search */
v_Where := v_where || 'and upper(t.original_string) like upper(' || '''' || '%' || isrch_strg || '%' || '''' || ') ';
elsif ilang_srch > 0 then
v_where := v_where || 'and upper(t.translated_string) like upper(' || '''' || '%' || isrch_strg || '%' || '''' || ') ';
end if;

if isort is not null then
v_order := v_order || isort || ', ';
end if;

v_order := v_order || c_updated || ' ';

if iascdesc > 0 then /* -1 means ascending order */
v_order := v_order || 'desc';
end if;

open oCursor for
v_select || v_from || v_where || v_order;
end;
basically it returns a cursor and i am using the following way to access the cursor
CallableStatement stmt = conn.prepareCall("{call Translation.GetValues(?,?,?,?,?,?,?,?)}");

stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setInt(2,-1); //for status
stmt.setInt(3,-1); //for context
stmt.setString(4,new String("translated_string")); //for sor col
stmt.setInt(5,-1); //for sort asc desc
stmt.setInt(6,1); //for language
stmt.setInt(7,1); //for lang search
stmt.setString(8,new String("rh")); //for lang string

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

System.out.println("The size is " + rs.getFetchSize());

// print the results
while (rs.next()) {
System.out.println("now printing out");
System.out.println(rs.getString(1));
}
It doesn't throw any error but it doesn't return any result set any help where exactly i am going wrong

The storedprocedure is working now.
Threre was some silly mistake in my program any way its fine now
Thanks a lot for the help