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.

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? – 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.
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;
/
  • 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