posted 20 years ago
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