[johnwcv]

HI.

I have a problem with fetching cursor. The pl/sql is

.....
LOOP
FETCH entidades into entidad;
EXIT WHEN entidades%NOTFOUND;
END LOOP;
CLOSE entidades;
.....

When entidades has 1 or more records, it works.
But when entidades has no records, I get the next error:
ORA-24338: statement handle not executed


What can i do?

Thanks in advance

[anacedent]

24338, 00000, "statement handle not executed"
// *Cause:  A fetch or describe was attempted before executing a
//          statement handle.
// *Action: Execute a statement and then fetch or describe the data.

[johnwcv]

I am executing the statement before fetching the cursor

[anacedent]

Do you honestly think Oracle is mis-reporting reality?
Do you think the problem is a bug in Oracle & not in your code?

[Littlefoot]

Originally Posted by johnwcv

I am executing the statement before fetching the cursor

Which one?

Just a suggestion: why don't you use cursor FOR loop? It is easier to maintain as you don't have to declare cursor variable, open a cursor, worry when to exit a loop and close a cursor.

Here's an example of code you currently use:

declare
  cursor c1 is 
    select dname from dept;
  c1_r c1%rowtype;
begin
  open c1;
  loop
    fetch c1 into c1_r;
    exit when c1%notfound;
    dbms_output.put_line(c1_r.dname);
  end loop;
  close c1;
end;

Here's the same code, but slightly rewritten:

begin
  for c1_r in (select dname from dept) loop
    dbms_output.put_line(c1_r.dname);
  end loop;
end;

Which one do you prefer?

[johnwcv]

anacedent, I know there is a bug in my code.

Littlefoot, the problem is that entidades (the cursor), is an out parameter of other procedure:

int_packagename_pkg.int_otherprocedurename_spr(pin _id, pin_login, entidades);

LOOP
FETCH entidades INTO entidad;
EXIT WHEN entidades%NOTFOUND;
END LOOP;
CLOSE entidades;