Re: Returning a ref cursor with dynamic SQL in PL/SQL

In article <3de25550$0$24228$afc38c87_at_news.easynet.co.uk>, "Mark" says...
>
>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 :=
'SELECT *
FROM ssr_person_report_view
WHERE cdas_sales_id IN :sales_ids
ORDER BY cdas_sales_id
, order_details';

open vr_person_report for vv_sql_statement;

your syntax before was wrong.

>
>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
>
>
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp