1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

This code execute witout error, why???

Discussion in 'SQL PL/SQL' started by preston, May 9, 2012.

  1. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    The code below executes without error. First, I opened a cursor and then delete all rows from underlying table then fetch all values to collection, it works fine how ever when I delete table before opened cursor it gives error. Why???


    Code (Text):

    set serveroutput on;
    declare
    cursor cur is
    select * from a1;
    type my_type is table of a1%rowtype index by pls_integer;
    my_arr my_type;
    begin
      open cur;
      delete from a1;
      fetch cur bulk collect into my_arr;
            for i in my_arr.first..my_arr.last
            loop
            dbms_output.put_line(my_arr(i).id);
            end loop;
      close cur;
    end;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Is this the error:

    Code (SQL):
    SQL> DECLARE
      2  cursor cur IS
      3  SELECT * FROM a1;
      4  TYPE my_type IS TABLE OF a1%rowtype INDEX BY pls_integer;
      5  my_arr my_type;
      6  BEGIN
      7    DELETE FROM a1;
      8    OPEN cur;
      9    fetch cur bulk collect INTO my_arr;
     10          FOR i IN my_arr.FIRST..my_arr.LAST
     11          loop
     12          dbms_output.put_line(my_arr(i).id);
     13          END loop;
     14    close cur;
     15  END;
     16  /
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: NUMERIC OR VALUE error
    ORA-06512: at line 10

    SQL>
     
    If so there are no records to select in the table since you deleted everthing prior to opening the cursor thus the first and last array indices are NULL. That is what produces the error shown above, and the error I expect you are seeing.
     
  3. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Apparently, you didn't undeerstand me. Even if all rows were deleted, I get them all and they were displayed witout error. When I write delete statement after opening cursor.

    And my question is, when I write delete statement AFTER open cursor, I can get all rows, I mean, Although all table were deleted but cursor display all values. However when I write delete statement BEFORE open cursor, compiler gives error. Why?

    I just would like to understand how come I get all deleted values, even if I deleted all of them????

    Thanks
     
  4. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Any ideas?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to understand what is happening in that pl/sql block; in the first example that you wrote (again I could not get it to run without column aliases) you open the cursor which fetches all of the rows from table a1; these are now in memory in the cursor. Deleting from the table does not delete from the cursor, thus accessing the cursor displays ITS contents, NOT the contents of the table.

    In the second example the table is deleted from BEFORE you open the cursor; there is nothing to fetch at that point and the error shown is displayed because there is no data, no first or last record in the array.
     
  6. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Zargon, is there any documentation about this information in Oracle web site? I mean, you explained that when cursor opened the data will be hold in the memory, Is there any documents related to this subject in Oracle's web site?

    Thanks for your time.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is how cursors work; it's how a ref cursor can be opened and passed to another program and the data follows with it. You should look at the online documentatio on cursors in the SQL and PL/SQL reference.