Why Sqlcode 100 does not come with exception

exceptionlockingoracle

I have following query in a PL\SQL procedure on Oracle 10.2:

This is the code

LOOP
  BEGIN

    SELECT a.poid_id0 into v_acc_account_poidid0 
    FROM account_t a
    WHERE a.poid_id0=i_acct_id0 
    FOR UPDATE OF a.poid_id0 NOWAIT;

    EXIT WHEN sqlcode = 0;

  EXCEPTION

    WHEN resource_busy THEN

      BEGIN
        v_max_retry_times_counter := v_max_retry_times_counter + 1 ;
        IF (v_max_retry_times_counter>v_max_retry_limit) THEN
          RAISE_APPLICATION_ERROR (ERROR_SELECTING,'Resource Busy with Nowait Option.',TRUE);
          EXIT;
        END IF;
        DBMS_LOCK.sleep(2);
      END;

    WHEN OTHERS THEN

      BEGIN
        RAISE_APPLICATION_ERROR (ERROR_SELECTING,'ORACLE ERROR DESCRIPTION'||sqlerrm ,TRUE);
        EXIT; 
      END;

  END;
END LOOP;

The statement returns sqlcode 100 and does not throw an exception.

From the Oracle documentation I understand that the error code is accompanied by an exception. What may be the reason behind this behaviour?

The documentation says the error code 100 is DATA NOT FOUND; we have data for the select query.

In this context does DATA NOT FOUND mean the select is failing or the lock is not available?

Any help much appreciated.

Best Answer

I will explain the sequence of actions.

  1. The select returns no rows and throws an exception NO_DATA_FOUND
  2. The WHEN OTHERS exception handler handles the exception.
  3. the Raise_Application_Error should raise the new exception.

Problems:

  • The use of the sqlcode variable is not wise, you should remove it as it is not within an error handler.
  • The EXIT statements after the RAISE_APPLICATION_ERROR's are dead code.
  • I cannot quite see how you could see the ora-00100 error with this code and still be correct in what you wrote.
Related Question