-
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!
-
append CREATE OR REPLACE to PROCEDURE proc
-
by the way, i am assuming that you are using sql*plus or something other than forms or procedure builder.
-
Opening a PL/SQL cursor as you tried is not allowed.
Native dynamic SQL (NDS) could be good choice for that thing:
Code:
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;
-
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...
-
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
