Why Sqlcode 100 does not come with exception

Question

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.

• Animesh D: Please specify which database. SQL Server? Oracle? MySQL? PostgreSQL?
• Nandish A: Oracle database. Updated the query
• René Nyffenegger: HOW did the statement return "sqlcode of 100"?
• Nandish A: Copied the sqlcode into temporary variable after executing this and then printed it
• Ollie: 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.

Answer 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.

Answer 2

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;
/

• Nandish A: 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