This discussion is archived
7 Replies Latest reply: Apr 1, 2013 5:21 PM by newbi_egy RSS

where current of clause

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2 ,
hi all ,

i am tying to learn the use of the "where current of" clause , but i am trying to code an example to know how it works .

so i tried this example :
declare
cursor c is select ename from emp where deptno = 20 for update ;
crec c%rowtype ; 
begin
open c ; 
loop 
fetch c into crec ;
update emp set sal = 5 where current of c ; 
exit when c%notfound ; 
end loop ;
close c ;  
end ;
/
but i am facing this :
{code
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 8
??

and to know it's functionality , i suppose that this code will update the "sal" column with the value "5" 
where deptno = 20 ? is that right ? 

thanks in advance                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 1. Re: where current of clause
    rp0428 Guru
    Currently Being Moderated
    >
    i am tying to learn the use of the "where current of" clause , but i am trying to code an example to know how it works .

    so i tried this example :

    declare
    cursor c is select ename from emp where deptno = 20 for update ;
    crec c%rowtype ;
    begin
    open c ;
    loop
    fetch c into crec ;
    update emp set sal = 5 where current of c ;
    exit when c%notfound ;
    end loop ;
    close c ;
    end ;
    /

    but i am facing this :
    {code
    declare
    *
    ERROR at line 1:
    ORA-01410: invalid ROWID
    ORA-06512: at line 8
    ??
    
    and to know it's functionality , i suppose that this code will update the "sal" column with the value "5" 
    where deptno = 20 ? is that right ? 
    {quote}
    Yes - that is right.
    
    It looks like your example is patterned after the one from the PL/SQL Language doc
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#sthref592
    
    Unfortunately, that doc example, and yours, has a common error.
    
    For a single row FETCH the EXIT condition needs to be IMMEDIATELY AFTER the FETCH. Otherwise the code executes one too many times and you get your error.
    
    Assume that your query returns NO rows. Your loop will not exit until AFTER the update is attempted but there is no row to update!
    
    Modify your code 
    {quote}
    declare
    cursor c is select ename from emp where deptno = 20 for update ;
    crec c%rowtype ; 
    begin
    open c ; 
    loop 
    fetch c into crec ;
    exit when c%notfound ; --> EXIT GOES HERE!!!
    update emp set sal = 5 where current of c ; 
    end loop ;
    close c ;  
    end ;
    / 
    {quote}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 2. Re: where current of clause
    newbi_egy Explorer
    Currently Being Moderated
    a lot of thanks to you , but

    first of all , this example you posted :
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#sthref592
    does not get me an error , unless i remove the if statement .
    2-For a single row FETCH .
    what is a single row fetch ? is there a multiple row fetch ?
    3- Otherwise the code executes one too many times and you get your error.
    what does that mean ? do you mean process one or "executes one" , if you could clarify the whole statement ?
    Assume that your query returns NO rows.
    my query does not , it returns rows ,
    Your loop will not exit until AFTER the update is attempted but there is no rows to update!
    -if my does not return rows , should not the update statement returns an error or message or tell me in anyway ?

    at last , it works when i do what you say .
    and there is something i want to ask , is the "where current of" statement the same as
    loop 
    fetch c into crec ;
    update emp set sal = 5 where ename = crec.ename ; 
    is not it the same but the problem resides in the locks of the for update clause ?
    - invalid rowid error .
    is that error means that there is a line above another line in the code and it should be under that line ?
    thanks again

    Edited by: newbi_egy on Apr 1, 2013 4:15 PM

    Edited by: newbi_egy on Apr 1, 2013 4:24 PM
  • 3. Re: where current of clause
    rp0428 Guru
    Currently Being Moderated
    >
    first of all , this example you posted :

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#sthref592

    does not get me an error , unless i remove the if statement .
    >
    That example has the same flaw your code has: the EXIT condition is at the END of the loop and needs to be right after the FETCH.
    >
    2-For a single row FETCH .
    what is a single row fetch ? is there a multiple row fetch ?
    >
    Yes - bulk processing using the BULK COLLECT INTO clause. When you use the LIMIT clause you need to put the EXIT at the END of the loop.
    >
    3- Otherwise the code executes one too many times and you get your error.
    what does that mean ? do you mean process one or "executes one" , if you could clarify the whole statement ?
    >
    You can't FETCH and then try to do an UPDATE right away. The last FETCH won't return anything since there isn't any more data. So if you do the UPDATE right after the FETCH you are doing the update when the fetch didn't give you any data: your 'code executes one too many times'.
    >
    Assume that your query returns NO rows.
    my query does not , it returns rows ,
    >
    Do you know what the word ASSUME means? If doesn't matter if your cursor returns NO rows, 10 rows or any other number. The last FETCH won't return ANY rows so there is no ROWID to use WHERE CURRENT OF for.
    >
    Your loop will not exit until AFTER the update is attempted but there is no rows to update!
    -if my does not return rows , should not the update statement returns an error or message or tell me in anyway ?
    >
    YOU DID GET AN ERROR MESSAGE! That is why you posted the thread. You tried to do an UPDATE when the cursor was already over because your EXIT was in the wrong place.
    >
    at last , it works when i do what you say .
    and there is something i want to ask , is the "where current of" statement the same as

    loop
    fetch c into crec ;
    update emp set sal = 5 where ename = crec.ename ;
    . . .
    is not it the same but the problem resides in the locks of the for update clause ?
    >
    No - it is NOT the same. Your cursor selects data based on DEPTNO = 20. That UPDATE just above updates based on ENAME. How is that the same?
  • 4. Re: where current of clause
    newbi_egy Explorer
    Currently Being Moderated
    That example has the same flaw your code has
    it works , try it .
    bulk processing using the BULK COLLECT INTO clause
    is this clause is the multiple row fetch ?
    is not it the same but the problem resides in the locks of the for update clause ?
    i've tried it , and it does the same thing , and i do not know how you say that it can not do the same , because

    the enames retrieved with the first situation is the same as enames retrieved with the second situation , and the record crec holds the same enames and i update the sal in a loop where ename = crec.ename(the same enames) .

    how should not it work ?
    is that error means that there is a line above another line in the code and it should be under that line ?
    you did not answer this .

    thanks
  • 5. Re: where current of clause
    sb92075 Guru
    Currently Being Moderated
    newbi_egy wrote:
    That example has the same flaw your code has
    it works , try it .
    then for a change, mark this thread as answered!

    Handle:     newbi_egy
    Email:     eyesmagicx@yahoo.com
    Status Level:     Newbie (10)
    Registered:     Mar 6, 2011
    Total Posts:     761
    Total Questions:     200 (109 unresolved)
  • 6. Re: where current of clause
    newbi_egy Explorer
    Currently Being Moderated
    i will mark it but i am waiting your answer .

    and about my unresolved questions , that was at the beginning of registering in this forum , and i did not know about marking the thread .
  • 7. Re: where current of clause
    rp0428 Guru
    Currently Being Moderated
    >
    That example has the same flaw your code has
    it works , try it .
    >
    The flaw is putting the EXIT clause at the END of the loop instead of immediately after the FETCH.

    It isn't enough for code to work with a specific set of data. It has to work for ALL data. Neither the example code nor yours with work with ALL data.
    >
    i've tried it , and it does the same thing , and i do not know how you say that it can not do the same , because

    the enames retrieved with the first situation is the same as enames retrieved with the second situation , and the record crec holds the same enames and i update the sal in a loop where ename = crec.ename(the same enames) .

    how should not it work ?
    {quote
    You can't be serious!

    You select ENAME values for DEPT = 20 and then you update ALL records in the table for the same ENAME whether their DEPTNO = 20 or not?

    ENAME is not unique. I can have rows with ENAME = 'SMITH' and DEPTNO = 10, 20, 30, 40 or any other deptno. Your last sample code would update ALL of the rows where ENAME = 'SMITH' just because there is a smith with DEPTNO = 20.
    >
    is that error means that there is a line above another line in the code and it should be under that line ?
    you did not answer this .
    >
    Yes I did - you just either didn't read, or didn't understand, what I said.

    For a single row FETCH in a loop the EXIT clause has to be IMMEDIATELY AFTER the FETCH statement; not at the END of the loop.

    There is no 'above a line' or 'below a line' in PL/SQL. You can (but should not) put multiple statements on the same line.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points