dynamic sql in PL/SQL

From: jimmy gogo (james.lawless_at_vhi.ie)
Date: 03/22/05 

Date: 22 Mar 2005 13:21:56 -0800

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

Relevant Pages