CODEWITHSUNDEEP
oracle-databaseplsqldatabase-cursor
Nathan Russell
Nathan Russell

Reputation: 85

Oracle Cursor, No Data Found exception

I'm trying to modify a procedure that removes a user from the system, adding a bit that generates a list of any areas over which they are the only admin. I have read that cursors can work when 0 results are found, so I am assuming that my query is the problem. Tables 1 (A) and 2 (B&C) are joined by the primary key of table A. Subquery B is a list of all the areas a user is admin over, and Subquery C is a list of all areas with only one admin. In the specific instance I'm testing, there is no matching area_id between subqueries B and C. Is there a way I can modify this query to work properly? I tried moving the opening of the cursor behind an if statement, but still receive the error, so I'm assuming it's the declaration of the cursor that is resulting in the exception being thrown.

   CURSOR AA_CUR IS
             SELECT AREA_NAME
               FROM FILE_TRANSFER.AREA_LU A,
                    (SELECT AREA_ID 
                        FROM FILE_TRANSFER.USER_AREA_ACCESS
                       WHERE AREA_ADMIN='Y'
                         AND USERNAME IN (SELECT USERNAME 
                                            FROM FILE_TRANSFER.USER_INFORMATION
                                           WHERE USER_INFORMATION_ID = v_UIID)) B,
                    (SELECT AREA_ID, AREA_ADMIN, COUNT(*)
                        FROM FILE_TRANSFER.USER_AREA_ACCESS
                       WHERE AREA_ADMIN='Y'
                      HAVING COUNT(*) = 1
                       GROUP BY AREA_ID, AREA_ADMIN) C
              WHERE A.AREA_ID = B.AREA_ID
                AND B.AREA_ID = C.AREA_ID;

Upvotes: 0

Views: 1664

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Your assumption is wrong.

Cursor's SELECT can't return NO_DATA_FOUND. If it returns nothing, it is silently ignored, nothing happens.

SQL> declare
  2    cursor c1 is
  3      select 'x' from dual
  4      where 1 = 2;                -- this query returns "nothing"
  5    c1r c1%rowtype;
  6  begin
  7    open c1;
  8    fetch c1 into c1r;
  9    close c1;
 10  end;
 11  /

PL/SQL procedure successfully completed.

See? Nothing happened. Just to show that query will raise NO_DATA_FOUND (if ran separately):

SQL> declare
  2    l_val varchar2(1);
  3  begin
  4    select 'x' into l_val from dual
  5    where 1 = 2;                  -- this will raise NO_DATA_FOUND
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

SQL>

Therefore, there must be some other SELECT statement that is raising that error. Which one? Can't tell as you didn't post much of your code and - even if you did - without your tables and data it would be difficult to guess.

So, what to do? Debug your code. A simple option is to put DBMS_OUTPUT.PUT_LINE calls between every two statements so that you'd see which part of code actually failed - then you'll be able to try to fix it.

Upvotes: 2

Related Questions