Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721

    Unanswered: Freezing while using UPDATE statement

    Hi. I am trying to update a few rows with bulk update, but somehow the instance freeze when I try to execute it on more than 100 rows, without any reason.

    An example of this is below:
    Code:
    SQL> create table prueba as select codigo,est_codigo from inmuebles where rownum < 50;
    
    Table created.
    
    SQL> exec actmass3;
    
    PL/SQL procedure successfully completed.
    
    SQL> drop table prueba;
    
    Table dropped.
    
    SQL> create table prueba as select codigo,est_codigo from inmuebles where rownum < 101;
    
    Table created.
    
    SQL> exec actmass3;
    There it hangs and I have to actually kill the session. The procedure's code (actmass3) is this:

    Code:
    create or replace procedure actmass3 is
     type t_inmuebles is table of prueba.codigo%TYPE INDEX BY BINARY_INTEGER;
     r_inmuebles t_inmuebles;
     cursor inmuebles is
      select codigo
        from prueba;
    begin
     open inmuebles;
     fetch inmuebles bulk collect into r_inmuebles limit 100;
     LOOP
      forall i in r_inmuebles.first..r_inmuebles.last
       update prueba
          set est_codigo = 'AC'
        where codigo = r_inmuebles(i);
      exit when inmuebles%notfound;
     end loop;
    end;
    If anyone can shed a light for me here and tell me the reason why it is freezing that would be appreciated.

    Thanks!
    Last edited by JMartinez; 07-08-04 at 17:40.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What are the last two dozen lines in the alert_SID.log file?
    Is this DB in archive redo logfile mode?
    Is the whole DB really, really hung?
    Can an existing session still do queries?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Where can I locate the file alert_SID.log ? it is not on Oracle home.

    I dont know wether or not is in red logfile mode, how can I check ?

    Not the whole db is hung, but my session is.

    I can do queries from another session if its not from the same OS user.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    The code isn't freezing. It's looping like crazy, updating the same rows over and over. Also, this looks like a very simple update - why all the pl/sql? A learning excersize I hope.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by shoblock
    The code isn't freezing. It's looping like crazy, updating the same rows over and over. Also, this looks like a very simple update - why all the pl/sql? A learning excersize I hope.
    Hi. Why would the code "loop like crazy" when I have >= 100 rows, but not when I have <= 50 rows ?

    And Yes, this is just an example I am making myself, since I did another huge procedure which does the same.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    Well, to start with, you one fetch statement, but it's outside the loop. So how whill the notfound ever happen (when using bulk, if the # of records returned is less than the limit, notfound is true, that's why it works for 50 records). Since the notfound doesn't fire on the first pass, and the fetch is never executed again, the loop continues forever, performing the same update over and over again.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    shoblock, thank you. You were right about the FETCH being outside the LOOP, but notfound only fires when theres no more records, without taking care about the limit actually. I guess the LIMIT clause belongs to the fetch rather than the LOOP then.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •