Is this bug??

[abhaysk]

Hi All,

PS the below code...

BEGIN

  FORDATE('&&1', '&&2', v_fordate);
  v_region_cd := '&&1';

  /***************************************************************\
  *  Insert dummy record that indicates Region's processing has   *
  *  started.  ASP reports will see this record and will prevent  *
  *  HUB reports being run until processing is complete           *
  \***************************************************************/

  UPDATE HUB_INV_PART_SUM
    SET HUB_CD = 'LOAD',
	  VENDOR_CD = 'LOAD',
        PART_NO  = 'HUB_DATA_LOADING',
        FORDATE = v_fordate
    WHERE REGION_CD = v_region_cd
    AND  PLANT_CD = 'STAT';
    update_cnt := update_cnt + 1;
    COMMIT;

 -- No entry found, insert record.     
  IF SQL%NOTFOUND THEN                  -- NOT WORKING 
    INSERT INTO HUB_INV_PART_SUM
      (REGION_CD,
       PLANT_CD,
       HUB_CD,
       VENDOR_CD,
       PART_NO,
       FORDATE)
    VALUES
      (v_region_cd,
       'STAT',
       'LOAD',
       'LOAD',
       'HUB_DATA_LOADING',
       v_fordate);

    Insert_Cnt := Insert_Cnt + 1;

    COMMIT;
  END IF;

End;

In the above code, the row for Plant_CD = 'STAT' and Region_CD='AP'
is getting updated to LOAD and all that stuff....

If this is true that SQL%NOTFOUND should return False right...and Insert statement should not be processed...but actually it is Processing Insert statement and we are getting UNIQUE Constraints errors.

This code is the part of the code. This works fine if this is the only PL/SQL code....if its associated with another part of code its erroring....

Now You guys may think...the error might be from other part of code...
The answer is no..due to 2 reasons i am sure that this is the part of code which is erroring....

1) If i comment this part of code...Script runs fine...
If uncommented then i see error...

2) When we capture error...we capture associated values and the values correspond to the values that it trying to insert in above insert statement....

To mention....Till yestarday it was running fine.
When yestarday we upgraded our DB from 8.1.7 to 9.2.0 we are seeing this strange event....

Is there any bug in 9.2 related to SQL%NOTFOUND Fn???

Thanks
Abhay.

[pando]

comment your commit

[abhaysk]

Originally posted by pando
comment your commit

Thanks for input..I tried with commenting commit in Vain.

But could you explain in the effects of having commit and after that using SQL%NOTFOUND?
Or rather the relation between Commit and the Fn SQL%NOTFOUND?

If that were the case, as i said above, If that part of the code was the only code then it works fine ( With Commit as well )..
And also i mentioned this code was working fine till i upgraded to 9.2..

Thanks
Abhay.

[pando]

UPDATE HUB_INV_PART_SUM
    SET HUB_CD = 'LOAD',
	  VENDOR_CD = 'LOAD',
        PART_NO  = 'HUB_DATA_LOADING',
        FORDATE = v_fordate
    WHERE REGION_CD = v_region_cd
    AND  PLANT_CD = 'STAT';
    update_cnt := update_cnt + 1;
    COMMIT;

 -- No entry found, insert record.     
  IF SQL%NOTFOUND THEN

SQL%NOTFOUND will be always true because of that commit

[pando]

hmm I make a pardon, the sql%notfound stays false after commit, guess it´s something else


btw I am using 9.2.0.1

I am a bit confused by my own test hehe

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
        10 SMITH      CLERK           7902 17-DEC-80        800                    10
        10 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         10
        10 WARD       SALESMAN        7698 22-FEB-81       1250        500         10
        10 JONES      MANAGER         7839 02-APR-81       2975                    10
        10 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         10
        10 BLAKE      MANAGER         7839 01-MAY-81       2850                    10
        10 CLARK      MANAGER         7839 09-JUN-81       2450                    10
        10 SCOTT      ANALYST         7566 09-DEC-82       3000                    10
        10 KING       PRESIDENT            17-NOV-81       5000                    10
        10 TURNER     SALESMAN        7698 08-SEP-81       1500          0         10
        10 ADAMS      CLERK           7788 12-JAN-83       1100                    10
        10 JAMES      CLERK           7698 03-DEC-81        950                    10
        10 FORD       ANALYST         7566 03-DEC-81       3000                    10
        10 MILLER     CLERK           7782 23-JAN-82       1300                    10
lsc@LNX920-RAC1>begin
  2    update x set deptno = 10 where deptno = 10;
  3    dbms_output.put_line(sql%rowcount);
  4    commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11  end;
 12  /

14
NOTFOUND -- I expect it says FOUND though

lsc@LNX920-RAC1>
  1  begin
  2    update x set deptno = 10 where deptno = 10;
  3    dbms_output.put_line(sql%rowcount);
  4  --  commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11* end;
14
FOUND -- here I commented commit and it says FOUND as expected

lsc@LNX920-RAC1>begin
  2    update x set deptno = 10 where deptno = 30;
  3    dbms_output.put_line(sql%rowcount);
  4    commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11  end;
 12  /

0
NOTFOUND -- here I expect it says notfound

lsc@LNX920-RAC1>lsc@LNX920-RAC1>begin
  2    update x set deptno = 10 where deptno = 30;
  3    dbms_output.put_line(sql%rowcount);
  4    -- commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11  end;
 12  /
0
NOTFOUND -- here I expect it says notfound

I just confused myself, guess I am not made to program

[pando]

did same test in 8.1.7, and they behvae differently

  1  begin
  2    update x set deptno = 10 where deptno = 10;
  3    dbms_output.put_line(sql%rowcount);
  4    commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11* end;
14
FOUND  -- as expected

  1  begin
  2    update x set deptno = 10 where deptno = 10;
  3    dbms_output.put_line(sql%rowcount);
  4  --  commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11* end;
14
FOUND -- as expected

  1  begin
  2    update x set deptno = 10 where deptno = 30;
  3    dbms_output.put_line(sql%rowcount);
  4    commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11* end;
0
NOTFOUND -- as expected

  1  begin
  2    update x set deptno = 10 where deptno = 30;
  3    dbms_output.put_line(sql%rowcount);
  4    -- commit;
  5    if sql%notfound
  6    then
  7      dbms_output.put_line('NOTFOUND');
  8    else
  9      dbms_output.put_line('FOUND');
 10    end if;
 11* end;
0
NOTFOUND -- as expected

so I guess it´s a bug

[abhaysk]

Originally posted by pando
did same test in 8.1.7, and they behvae differently

so I guess it´s a bug

So in 8i it was all fine, may it have COMMIT statement in between a DML/DDL and Fn SQL%Found or SQL%NOTFOUND....

Actually speaking COMMIT should not make any difference on the Fn which returns a value based on, If Statement did executed for True,
as we have saw in 8i it didnt make any...

But in 9.2 we see such eratic things...some times eratic & sometimes correct...Yes than it shuould be a bug..but No Bug number or related notes in metalink ???


And one more thing, we may have to consider, is if client uses SQL Plus 8.x, then it may cause any problems?

I am suspecting this because, i encountered problems in copying data from 8.x to 9.x with client being 8.x....

Posible reasons why it can be incompatible ( lower versions of client ) to connect to higer versions of Server & its associated execution, is due the conversion of SQL text to machine language and sending it across N/W thru TCP/IP and at server...retriving SQL Text may go wrong ( Due higher version )????

In our case PRO, ACP, DEV DBs are all 9.2.0.3 and every other things are same...and some of the scripts which use SQL%NOTFOUND are behaving erraticly only on PRO and behaves normally in ACP & DEV DBs.....

Well when everythings are same, then wat the heck can be the problem?????

If it were to be a BUG it shud well behave in same manner for ACP & DEV DBs....

am confused ( to the core )...

Thanks
Abhay.

[abhaysk]

Originally posted by pando
comment your commit

Pando it indeed is correct, untill 8i it didnt matter, but from 9.x yes it does matter.
I rechecked in our DEV and ACP DBs..its behaving erratic there as well...and i tested in my local DB also its behaving same.
Please see below test case

SQL> select * from emp where id=23;

                  ID NAME                        SAL
-------------------- ---------- --------------------
                  23 Checking                   2333

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  update emp set NAME = 'CHECK' where id=23;
  3  --commit;
  4  if sql%notfound then
  5  dbms_output.put_line('Hi Abhay - Fail');
  6  else
  7  dbms_output.put_line('Hi Abhay - Sucess');
  8  end if;
  9* end;
SQL> /
Hi Abhay - Sucess

PL/SQL procedure successfully completed.

-- With commit commented, its working as desired.

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  update emp set NAME = 'CHECK' where id=23;
  3  commit;
  4  if sql%notfound then
  5  dbms_output.put_line('Hi Abhay - Fail');
  6  else
  7  dbms_output.put_line('Hi Abhay - Sucess');
  8  end if;
  9* end;
SQL> /
Hi Abhay - Fail

PL/SQL procedure successfully completed.


-- Without commenting commit its behaving erratically.
-- Now we may have to discuss the change in squence of events on Commit from 8.x to 9.x


SQL> select * from emp where id=222;

no rows selected

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  update emp set NAME = 'CHECK' where id=222;
  3  commit;
  4  if sql%notfound then
  5  dbms_output.put_line('Hi Abhay - Fail');
  6  else
  7  dbms_output.put_line('Hi Abhay - Sucess');
  8  end if;
  9* end;
SQL> /
Hi Abhay - Fail

PL/SQL procedure successfully completed.

-- With Commit uncommented it will however return true hence u see Fail.

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  update emp set NAME = 'CHECK' where id=222;
  3  --commit;
  4  if sql%notfound then
  5  dbms_output.put_line('Hi Abhay - Fail');
  6  else
  7  dbms_output.put_line('Hi Abhay - Sucess');
  8  end if;
  9* end;
SQL> /
Hi Abhay - Fail

PL/SQL procedure successfully completed.

-- With out Commit... it will say Fail because no rows have been updated....

BTW ur test case on 9i is absloutely correct.



Now the question arrises what sequence of events have changed ON issuing COMMIT to that from 8.x to 9.x ????


Does any one have any idea about the change


Thanks
Abhay.

[pando]

I still think is a bug, because %NOTFOUND should have effect with LAST fetch, a commit is not a fetch so %NOTFOUND should hold a value for the fetch previous commit

I also think it´s a bug because this only happens with %NOTFOUND, if it works correctly then if we replace it with %ROWCOUNT then we should see ZERO for %ROWCOUNT because of the commit. However I tried and %ROWCOUNT works correctly, it holds a value for the last fetch