Sql%notfound
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Sql%notfound

  1. #1
    Join Date
    Nov 2002
    Posts
    25

    Sql%notfound

    I've declared a cursor c3 that I know pulls back no rows at the moment, however when I run the procedure I keep getting the message 'Rows deleted'. I've checked the no of rows with SQL%ROWCOUNT and get 1 as the result. Can anyone explain why and how I should be writing the code??

    for rec in c3 loop
    delete from tester where dbid=rec.dbid;
    end loop;
    if SQL%NOTFOUND then
    dbms_output.put_line('No rows to delete');
    else
    dbms_output.put_line('Rows deleted');
    end if;

  2. #2
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Post ur script

    Hi,
    Please post your script to resolve this issue.
    Thanks.
    Regards,
    Kumar. RP
    RP Kumar
    You Can Win, if u believe Yourself

  3. #3
    Join Date
    Nov 2002
    Posts
    25
    DECLARE
    v_dbid number;
    v_inst_num number;

    cursor c2 is
    select * from stats$database_instance di
    where instance_number = v_inst_num
    and dbid = v_dbid
    and not exists (select 1
    from stats$snapshot s
    where s.dbid = di.dbid
    and s.instance_number = di.instance_number
    and s.startup_time = di.startup_time);

    BEGIN

    -- Get info about the Database and instance
    --select dbid into v_dbid from v$database;
    v_dbid := 3824701041;
    --select distinct instance_number into v_inst_num from stats$database_instance where dbid = v_dbid;
    v_inst_num := 1;

    for rec in c2 loop
    delete from stats$database_instance where snap_id=rec.snap_id;
    end loop;
    commit;
    if SQL%NOTFOUND then
    dbms_output.put_line('No hanging SQL to delete');
    else
    dbms_output.put_line('Hanging SQL deleted');
    end if;

    END;
    /

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Change your last part of the script to the following and see what happens:
    Code:
    for rec in c2 loop
      delete from stats$database_instance where snap_id=rec.snap_id;
    end loop;
    commit;
    if SQL%NOTFOUND then
      dbms_output.put_line('No hanging SQL to delete');
    elsif NOT SQL%NOTFOUND then 
      dbms_output.put_line('Hanging SQL deleted');
    else
      dbms_output.put_line('Hey, what exactly do you want me to show???');
    end if;
    
    The cursor atribute SQL%NOTFOUND has nothing to do with your cursor C2!!! When you call the atribute SQL%NOTFOUND your cursor C2 doesn't exist any more, it is closed allready!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2002
    Posts
    25

    Question

    Thats what I thought - but if I put the IF statement inside the cursor I get no output at all??:

    for rec in c2 loop
    delete from stats$database_instance where snap_id=rec.snap_id;
    if SQL%NOTFOUND then
    dbms_output.put_line('No hanging SQL to delete');
    else
    dbms_output.put_line('Hanging data deleted');
    end if;
    end loop;
    commit;

  6. #6
    Join Date
    Jan 2003
    Location
    Hyd
    Posts
    4
    Hi,

    as you know SQL%NOTFOUND,Sql%found,Sql%rowcount are basically implicit cursor attributes, these attributes will execute and value is captured whenever transaction of DML statement is executed with out any exceptions like value error (except no data_found).

    in ur case delete statement is executing hence u r getting value in cursor attribute.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by cleggfr
    Thats what I thought - but if I put the IF statement inside the cursor I get no output at all??:

    for rec in c2 loop
    delete from stats$database_instance where snap_id=rec.snap_id;
    if SQL%NOTFOUND then
    dbms_output.put_line('No hanging SQL to delete');
    else
    dbms_output.put_line('Hanging data deleted');
    end if;
    end loop;
    commit;
    Change your SQL%NOTFOUND into c2%NOTFOUND inside the cursor for-loop.

    But if your cursor doesn't return any rows then the body of the loop will never be executed, hence you still won't get any output .

    In your case, I would do something like:
    Code:
    DECLARE
      ....
      v_found BOOLEAN := FALSE;
    BEGIN
      ...
      for rec in c2 loop
        v_found := TRUE;
        delete from stats$database_instance where snap_id=rec.snap_id;
      end loop;
      if v_found then
        dbms_output.put_line('Hanging data deleted');
      else
        dbms_output.put_line('No hanging SQL to delete');
      end if;
      commit;
      ....
    END;
    Last edited by jmodic; 01-07-2003 at 09:01 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2002
    Posts
    25
    Ah - slowly the fog lifts
    Thanks for the help - it was driving me nuts!!

Posting Permissions

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



Click Here to Expand Forum to Full Width