Home » SQL & PL/SQL » SQL & PL/SQL » [CURSORS] Strange cursor behavior
[CURSORS] Strange cursor behavior [message #206624] Thu, 30 November 2006 19:46 Go to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
hi to all, i'm new...
...ok, let's go with the strange thing.
First fix this table, named prova, in mind.
|  PROVA  | Table name
-----------
|GINO|PINO| Columns name
-----------
| 1  |  a | Tuples
| 2  |  b |
| 3  |  c |
-----------

GINO has the primary key constraint. All tuples respect this.

Now I wrote this anonymous block in oracle 10gR2:
set serveroutput on 
declare
  v_b prova.pino%type;
  v_a prova.gino%type;
  CURSOR c (id_in IN prova.gino%type) IS
      SELECT pino FROM prova
      WHERE  id_in = gino;
begin
  v_a:=1;
  --ok the next statement works!!! one row fetched.
  select pino into v_b from prova
        where gino = v_a;
  dbms_output.put_line('Fetched: ' ||  v_b);
  --now let's go with the cursor
  open c(v_a);
  --this loop works two times instead of one!!! (why?)
  loop
    fetch c into v_b;
    dbms_output.put_line('Fetched: ' || v_b);
    exit when c%notfound;
  end loop;
  close c;
end;


when executing the output is:

Fetched: a  <- abou 1st select stat.
Fetched: a  <| the loop stat. works 2 times!!!
Fetched: a  <|


The former is ok, it's about the 1st select statement.
But the last two are not!!!
A head ache is taking me all!!!
What's the hell about parametrized cursor?

I found this prob. even changing variables, cursors names and other kind of table!!!
Nothing changed!!!

Is it a problem regarding variables binding in a precompilation context?

any help apreciated!!!
thank u very much!!!
bye
Re: [CURSORS] Strange cursor behavior [message #206635 is a reply to message #206624] Thu, 30 November 2006 22:38 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

No problem with parametarised cursors.

why don't you end your loop when fetch statement fails to return a row.

See the modification below
you should write exit statement before your dbms_output.put_line


loop
fetch c into v_b;
exit when c%notfound;
dbms_output.put_line('Fetched: ' || v_b);
end loop;
Re: [CURSORS] Strange cursor behavior [message #206636 is a reply to message #206624] Thu, 30 November 2006 22:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
    fetch c into v_b;
    dbms_output.put_line('Fetched: ' || v_b);
    exit when c%notfound;

Fetch should be followed by Exit Statement.

    fetch c into v_b;
    exit when c%notfound;
    dbms_output.put_line('Fetched: ' || v_b);


For the first time the value is assigned to v_b and it got printed.
As c%notfound gives false this time, control iterate in the loop again. There are no rows. So, v_b has not been assigned with anything means the old value persists. And it got printed.
Now c%notfound gives true and the control comes out of the loop.

Hope I'm clear.

By
Vamsi
icon10.gif  Re: [CURSORS] Strange cursor behavior [message #206696 is a reply to message #206624] Fri, 01 December 2006 02:45 Go to previous messageGo to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
just hanging myself for my stupid question Laughing maybe
the beer worked wrong with my brain!!!
It was a simple, lame stupid question!!!


tank u very much!!!
icon12.gif  Re: [CURSORS] Strange cursor behavior [message #206698 is a reply to message #206696] Fri, 01 December 2006 02:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
./fa/1693/0/
By
Vamsi
icon14.gif  Re: [CURSORS] Strange cursor behavior [message #206844 is a reply to message #206696] Fri, 01 December 2006 13:45 Go to previous message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

u can also do just like that :

Quote:
fetch c into v_b;
exit when c%rowcount>2;-- simple as many time as u like.
dbms_output.put_line('Fetched: ' || v_b);
Previous Topic: how to run a date a day before using sysdate
Next Topic: Loading XML into a table
Goto Forum:
  


Current Time: Thu Dec 08 06:14:09 CST 2016

Total time taken to generate the page: 0.10180 seconds