CODEWITHSUNDEEP
oracle-databaseoracle11g
aru
aru

Reputation: 129

Oracle 11g sql%notfound issue

We have migrated our .database from oracle 8 to oracle 11g

in one of the procedure after update statement there is a if condition which checks whether any rows have been affected or not . if yes then it would do nothing otherwise it would insert data into the table 

IF (SQL%NOTFOUND) THEN
        -- The record does not exist so try to insert the master customer data.
        insert_order_master_customer(p_host_country_id,
                  p_order_id,             p_accting_year,
                  p_master_cust_id,
                  p_master_cust_name     );
    END IF;

but this condition is not working after successful update it's evaluating true and control is going inside if block.

Upvotes: 0

Views: 4427

Answers (3)

Rene Hincapie
Rene Hincapie

Reputation: 1

  1. Use SQL%ROWCOUNT for counting how many rows were affected. %NOTFOUND only works with Open-Fetch a cursor. SQL%NOTFOUND would TRUE inside a WHEN NO_DATA_FOUND exception.

  2. It is, NO_DATA_FOUND, not DATA_NOT_FOUND

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

does not reproduce here on 11.2.0.2.

SQL> create table foo(id number);

Table created.

SQL> insert into foo values (1);

1 row created.

SQL> set serverout on
SQL> begin
  2    update foo set id = 2 where id = 1;
  3     IF (SQL%NOTFOUND) THEN
  4       dbms_output.put_line('not found!');
  5    elsif (SQL%NOTFOUND = false)
  6     then
  7       dbms_output.put_line('found!');
  8     end if;
  9  end;
 10  /
found!

is the update right before the check, ie nothing else sits between it? also what's the output if you put dbms_output.put_line(sql%rowcount); just before the IF check ?

Upvotes: 1

Andrey Khmelev
Andrey Khmelev

Reputation: 1161

exception when DATA_NOT_FOUND then

insert_order_master_customer(p_host_country_id,
                      p_order_id,             p_accting_year,
                      p_master_cust_id,
                      p_master_cust_name     );

Upvotes: 0

Related Questions