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(2500);
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!

[simflex]

append CREATE OR REPLACE to PROCEDURE proc

[simflex]

by the way, i am assuming that you are using sql*plus or something other than forms or procedure builder.

[ales]

Opening a PL/SQL cursor as you tried is not allowed.
Native dynamic SQL (NDS) could be good choice for that thing:

PROCEDURE proc( 
  p_var1 IN varchar2, 
  … 
  p_cur In OUT cur_type) 
IS 
  lvcSql VARCHAR2(2500); 
  v_where varchar2(1000); 
BEGIN 
  lvcSql :='OPEN p_cur FOR '|| 
    'SELECT col1, col2 '|| 
    'FROM table '||v_where ; 
  OPEN p_cur FOR lvcSql;
end;

[leburs]

Thank you for replies.
To simflex: the procedure is the part of the packege, that's why it's written this way. cur_type is defined in package spec.
To ales: It's not compiled this way, we don't have NDS, it's Oracle 7.
Problem still exists...

[ales]

Oh, I'm sorry I overlooked the version in title ...

In 7.3 is not possible to open PL/SQL cursor for dynamically built query. Try to redesign the proc, perhaps you could write more statically defined statements like "OPEN p_cur FOR SELECT ..." and put them into "IF-THEN-ELSIF-END IF".
Or, if you need create you query dynamically, just use ordinary dbms_sql cursor (lnCursor) instead of PL/SQL cursor. You can also pass it out of the proc and fetch from it.
Of course, it means more coding.
HTH,
Ales