How to call stored procedure that returns a cursor

[Jitesh Sinha]

Guyz,
I have spent several days trying to do this but have not been successful.I need to call an Oracle stored procedure from my java code.
My stored procedure looks like this :-

create or replace
PROCEDURE GET_GROUP_DATA(P_CURSOR OUT sys_refcursor) AS

L_CATEGORY VARCHAR2(250);
L_GOAL NUMBER;
L_MONTH_TO_DATE NUMBER;
L_STATUS VARCHAR2(50);


BEGIN
OPEN P_CURSOR for

SELECT
T1.CATEGORY
, T1.GOAL
, T1.MONTH_TO_DATE
,T1.status
from T1 ;

end get_group_data;


Here is my Java code :-

public String getMonthlyProgressData()  
		{
	    	Session session = getSession() ;
	    	
	    	Connection conn = session.connection() ;
	    	
	    	

	    	ResultSet rs = null ;
	    	
	    	ResultSet rs1 = null ;
	    	CallableStatement cstmt = null ;
	    	String str = "" ;
	    	
	    	try{
	    	cstmt = conn.prepareCall("CALL GET_GROUP_DATA(?)") ;
	    	
	    	cstmt.registerOutParameter(1,OracleTypes.CURSOR) ;
	    	
	    	
	    	cstmt.executeQuery() ;
	    	
	    	//log.debug("Result set first "+rs.getArray(1)) ;
	    	
	    	 rs = (ResultSet)cstmt.getObject(1) ;
	    	 while(rs.next())
	    	 {
	    		 str = rs.getString(1) ;
	    	 }
	    	
	    	}
	    	catch(SQLException sqex)
	    	{
	    		log.debug("sqex " +sqex.toString()) ;
	    		sqex.printStackTrace() ;
	    	}
	    	
	    	finally{
	    		try{
	    			if(rs!= null) rs.close() ;
	    			if(cstmt != null) cstmt.close();
	    			if(conn != null) conn.close() ;
	    		}
	    		catch(SQLException sqex)
	    		{
	    			log.debug("exception in finally "+sqex) ;
	    		}
	    		
	    	}
	    	
	    	return str ;
		
	}

Here is the error that I get - java.sql.SQLException: ORA-06553: PL**S*(ignore all *)-306: wrong number or types of arguments in call to 'GET_GROUP_DATA'.

The error message does not have * - but Javaranch thinks I am using abbreviation for PLEASE - so I had to put in * in the middle.
Please help me guyz.Thanks!

[Jitesh Sinha]

can someone please respond to this?Thanks.

[Ireneusz Kordal]

Which Oracle version - 7, 8, 9, 10 or 11 ?
This is described in documentation - look for JDBC Developer Guide.
Assuming you are using 11.2, here is a link to documentation with an example how to do it: http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#i1058743

[Jitesh Sinha]

Oracle version is 9.
Only difference from the example that you mentioned is execute instead of executeQuery on CallableStatement object.
Can that be the issue?

[Tina Smith]

I use "{call package_name.procedure_value(?)}". Not sure if the curly braces makes any difference, but the package name might. Also use execute() and class CallableStatement.

Make sure the package that defines the procedure has a public synonym defined: you will get this error if it does not. Bit misleading in my opinion but it's probably done for security reasons.

[Ireneusz Kordal]

try:

prepareCall("  declare x sys_refcursor;  begin  proc(  x ); ? := x; end " );

[Victor Lindberg]

Look this framework (https://code.google.com/p/lindbergframework/). Easy handling of stored procedures and functions, including treatment of cursors automatically.

https://code.google.com/p/lindbergframework/