Returning a ref cursor with dynamic SQL in PL/SQL

Hello,

I am having problems returning ref cursor information from my PL/SQL procedure using dynamic SQL.

Here's parts of my PL/SQL procedure:

FUNCTION my_function
RETURN ssr_person_report /* Ref cursor */ IS
vr_person_report ssr_person_report;
vv_sales_ids VARCHAR2(4000);

BEGIN ... populate vv_sales_ids...

vv_sql_statement :=
'OPEN vr_person_report FOR
SELECT *
FROM ssr_person_report_view
WHERE cdas_sales_id IN :sales_ids
ORDER BY cdas_sales_id
, order_details';

EXECUTE IMMEDIATE vv_sql_statement USING vv_sales_ids;

RETURN vr_person_report;

END my_function;

I get an error when accessing this function via JSP and JDBC:

java.sql.SQLException: ORA-00900: invalid SQL statement ORA-06512: at "MARK.SSR_PKG", line 161

I've checked the value of vv_sales_ids, and that seems to be OK because I've inserted the value into a temporary table for debugging purposes. It looks like this: (45, 49, 50, 52...)

Oracle version 8.1.7.

Thanks,

Mark Received on Mon Nov 25 2002 - 10:52:32 CST