3

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.

  • Please specify which database. SQL Server? Oracle? MySQL? PostgreSQL? – Animesh Jan 11 '12 at 10:56
  • Oracle database. Updated the query – Nandish A Jan 11 '12 at 10:58
  • 1
    HOW did the statement return "sqlcode of 100"? – René Nyffenegger Jan 11 '12 at 11:20
  • Copied the sqlcode into temporary variable after executing this and then printed it – Nandish A Jan 11 '12 at 11:25
  • NO DATA FOUND suggests that your query is not matching any records, make sure there are records present in the table and the <input parameter> is of the correct type and format and is actually the value you think it is. – Ollie Jan 11 '12 at 11:28
1

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.
| improve this answer | |
0

DATA NOT FOUND means the select is failing.

If the lock could not be established, sqlcode would be -54.

I am 100% positive that you had an exception (that obviously was caught somehow).

If you want to catch the exception, you should try something like

declare
  v_acc_account number;
begin

  select a.col1 into v_acc_account
    from account_t a
   where col2 = 5
     for update of a.col1 nowait;

  dbms_output.put_line(v_acc_account);

exception when others then
  dbms_output.put_line(sqlcode);

end;
/
| improve this answer | |
  • Thanks for the reply. I have updated my entire block of code. It gets through infinite loop here because a exception is not thrown and sqlcode is 100 – Nandish A Jan 11 '12 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy