Fetch INTO after Dynamic SQL in PL/SQL

[klaus1]

Hi!

I have a a problem using my Procedure with a dynamic SQL like the
pseudo code:
...
OPEN cursor FOR
'select emp from emlpoyee where .......';

LOOP
FETCH cursor into temp;
EXIT WHEN ret%NOTFOUND;
END LOOP;

I get an error like:
ERROR:
ORA-24338: statement handle not executed

So, I want to FETCH into my variables, but with the dynamic SQL
statement, Oracle says i should open a cursor before I fetch into.
How is it possible? Close the cursor, reopen it and Fetch into? if so,
how is this coded?

The other way I thought is, that I use a function, returning the
CURSOR, and Fetching it at another place.

If so, how can i open a returned cursor for fetching into variables?
Please give some code examples,

Thanks,
Klaus

[Thomas Olszewicki]

Klaus,
#1 - don't use "cursor" as a variable name
#2 - assuming new name: curVar
# declare return variable : recVar
OPEN curVar FOR 'SELECT ....';
LOOP
FETCH curVar INTO recVar;
EXIT WHEN curVar%NOTFOUND; ----<<<<<<<<<<<<<<<<<<<
-- %NOTFOUND must be used with cursor variable
-- in your case ret%NOTFOUND - what is ret??
END LOOP;

HTH
Thomas

[klaus1]

Thanks for your reply, I didn't call the Cursor cursor, I named it
"ret" I thougth for a better understanding, I use it as cursor in the
example, and so I made the mistake with the cur variable.

I have to conditions as follows:

IF ..... THEN
OPEN ret FOR
'SELECT ............';
LOOP
FETCH ret into ....;
EXIT WHEN ret%NOTFOUND;
%doing something with the variable..
END LOOP;

ELSIF ... THEN
OPEN ret FOR
'SELECT ............';
LOOP
FETCH ret into ....;
EXIT WHEN ret%NOTFOUND;
%doing something with the variable..
END LOOP;
END IF;

ERROR:

PL/SQL procedure successfully completed.

ERROR:
ORA-24338: statement handle not executed

[klaus1]

Here an example of the problem in a minimal example:

if I execute the following steps, I have the error of fetch out of
sequence:
var temp refcursor;
exec :temp:=ELBAMAIN.ELBI_5_4_0.testcursor2();

OUTPUT:
06-SEP-2007 12:12:09
06-SEP-2007 12:12:09

PL/SQL procedure successfully completed.

ERROR:
ORA-01002: fetch out of sequence

no rows selected

Elapsed: 00:00:00.01
Elapsed: 00:00:00.06
12:12:09 SQL>

Here the CODE:

PROCEDURE testcursor(nix IN VARCHAR2, cur1 IN OUT sys_refcursor)
IS
--l_cur sys_refcursor;
--cur1 sys_refcursor;
dual VARCHAR2(100):='sysdate';
datum DATE;
dSQL VARCHAR2(4000):=' ';
BEGIN

--OPEN l_cur FOR
dSQL:='SELECT '||dual||' as datum from dual';
OPEN cur1 for dSQL;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
-- RETURN 0;
--CLOSE l_cur;
END testcursor;

FUNCTION testcursor2(test IN VARCHAR2)
RETURN sys_refcursor
IS
type a is record (datum DATE);
b a;
ret sys_refcursor;
BEGIN
testcursor('NIX',ret);
loop
FETCH ret into b;
dbms_output.put_line(b.datum);
exit when ret%notfound;
end loop;
RETURN ret;
END testcursor2;

Please, has anyone an idea, how I can resolve the problem?
THANKS,
Klaus

[Rob Wolfe]

Again, I have to ask... why are you returning a cursor that has
already been wound all the way through?

I know this is supposed to be a toy example but telling what you are
trying to accomplish with it might be helpful to us because what you
are doing just doesn't seem to make sense (to me anyway)

the problem does not appear to be in your function per se but rather
when you return it out to sqlplus and assign it to the host variable.
To verify that, you could stick some exception handling into
testcursor2 just to give yourself the peace of mind that it is not
happening inside there.

the relationship between PLSQL and host variables is often rather
"interesting" and there are often unintended consequences. In other
words, the problem might just be because you are assigning a cursor
that is no longer valid.

[klaus1]

Hi!

well, it would be better to use just one single function doing the
following things:
- open dynamic cursor
- fetch data into host variables
- showing the host variables (or doing something else).
- returning the same cursor.

this is not possible, so I thougth, that I have to open a cursor,
returning to another function and reopen it for fetching.
but the problem as you mentioned is, that the cursors position is at
the end.

So can you tell me how I can make a function with the above described
features ? Do I have to reopen the cursor for returning? or do
I have to set the cursors position at the beginning?

Please reply,
Thanks,
Klaus

[Rob Wolfe]

well ... except for the fact that what you are saying you want to do
isn't what you are doing.

A "host variable" is different than a PL/SQL variable (it appears that
you know this though) and it behaves differently

The fundamental mistake you are making though, as far as the cursor is
concerned, is thinking that it is still useful after you have already
gone through the entire set of data that it returns. it is pointless
to return a cursor that is already past the end of all data. There is
no such concept as "rewinding" a cursor that I know of. If you want to
return the cursor, then you have to re-open it ... just as you did in
the first case... and then return that. A cursor isn't a collection.

not sure if this clarifies it at all but .....

[Thomas Olszewicki]

On Sep 6, 8:50 am, klaus1 <klaus.s...@gmail.com> wrote:

Klaus,
I'm starting to suspect you are trying to use very different database
model.
It looks like DBASE (Clipper or VO) model of data navigation.
Oracle like most of the RDBMS is different.
Set oriented model, not position oriented model.
Your set is your cursor and you can only :
open cursor
fetch cursor
close cursor.

In Pl/SQL you can fetch cursor only forward.
So you cannot change "the position" of the cursor.
If you need to do something with the results of the cursor more then
once,
I suggest you fetch results into one of Oracle collection variables.
With Oracle 9i and 10g you can also use bulk collect functionality.
I also suggest reading on basics:
Cursor and collections.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1273
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i26701

HTH
Thomas

[Rob Wolfe]

I think you are exactly right because I kept thinking as I was writing
my last response that a cursor isnt an array and I just couldn't get
it to come out right. Definitely need more coffee.

> Cursor and collections.http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqlope...http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collec...
>
> HTH
> Thomas