dynamic sql in PL/SQL

Hi,

I have a package which returns a refcursor to a Java client.

PROCEDURE GET_POLICY_SUBSCRIBER_YTD(i_grp_num IN VARCHAR2, i_from_date IN DATE, i_to_date IN DATE, orderBy IN NUMBER DEFAULT 1, all_joined OUT subscriber_policy_ytd_ref_c);

Java -
cstmt = conn.prepareCall("{call
STATEMENT_YTD.GET_POLICY_SUBSCRIBER_YTD(?,?,?,?,?)}"); cstmt.registerOutParameter(5, java.sql.Types.OTHER);

cstmt.setString(1, grpNum);
cstmt.setDate(2, from);
cstmt.setDate(3, to);
cstmt.setString(4, orderBy);

cstmt.execute();

rset = (ResultSet)cstmt.getObject(5);

The first three parameters are just used as bind variables inside where clauses of SQL inside the procedure. I want to dynamically order the returned data using the order by parameter passed in.

However it ignores the orderBy parameter inside the procedure. I think this is because I am trying to dynamically execute different SQL based on this rather than just bind variables.

"..snippet.. and T1.sub_id = opi3.OPI_SUBSCRIBER_ID

	   and ogs3.OGS_TO_DAT <= i_to_date)
		ORDER BY orderBy;"

 I have tried using dynamic sql but got the error - "PLS-00455: refcursor cannot be used in
dynamic SQL OPEN statement"

I tried to get around this by using a weakly typed return type but it complained about 'COULD NOT PARSE SQL' or similar..

I also tried using the column numbers rather than the column name and changing the orderBy parameter type to NUMBER but no more luck..

Do I have to use dynamic SQL here? What other options might I have?

Thanks, Jimmy Received on Tue Mar 22 2005 - 15:21:56 CST