DBMS_SQL and REF CURSOR (Oracle 7)

[leburs]

Hello,

What I’m missing in the following:

PROCEDURE proc(
p_var1 IN varchar2,
…
p_cur In OUT cur_type)

IS
lnCursor INTEGER;
lnDummy INTEGER;
lvcSql VARCHAR2(2000);
v_where varchar2(1000);
BEGIN
--v_where defined here from p_var1
lnCursor := DBMS_SQL.OPEN_CURSOR;
lvcSql :='OPEN p_cur FOR SELECT col1, col2 '||
'FROM table '|| v_where ;
dbms_output.put_line(lvcSql); --looks fine
DBMS_SQL.PARSE( lnCursor, lvcSql, DBMS_SQL.V7 );
lnDummy := DBMS_SQL.EXECUTE( lnCursor );
DBMS_SQL.CLOSE_CURSOR( lnCursor );

I get here ORA-00900: invalid SQL statement when parsing.

Please help!

 

[Dagon]

You shouldn't need dbms_sql for this. You can do it as follows:

PROCEDURE proc(
p_var1 IN varchar2,
…
p_cur In OUT cur_type)

IS
lnCursor INTEGER;
lnDummy INTEGER;
lvcSql VARCHAR2(2000);
v_where varchar2(1000);
BEGIN
--v_where defined here from p_var1
OPEN p_cur FOR 'SELECT col1, col2 '||
'FROM table '|| v_where ;
dbms_output.put_line(lvcSql); --looks fine
fetch p_cur into ....;
close p_cur;
DBMS_SQL.CLOSE_CURSOR( lnCursor );
END;

I think the "open ..for .." syntax is for use with Native Dynamic SQL rather than dbms_sql.

 

[leburs]

Thank you for reply, but it is not compiled this way. In the original way it is compiled but gives a runtime error.

 

[Dagon]

Maybe you have an old version of Oracle which doesn't have NDS. In that case, you'll probably have to use dbms_sql. If so, you don't need the "open..for.." in your SQL statement as this syntax is designed for NDS rather than dbms_sql. Your cursor should just be an ordinary select statement.