Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 11 '12 at 13:12

This question came from our site for professional and enthusiast programmers.

    
Please specify which database. SQL Server? Oracle? MySQL? PostgreSQL? –  Nanda 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

2 Answers 2

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

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.