Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    17

    Unanswered: No_data_found and continue

    Hi

    I have written several select queries in my store procedure. I want to continue with other queries even if one returns nothing. How to proceed without exception handling?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As there are several queries, I'd suggest you to enclose each of them into its own BEGIN-EXCEPTION-END block. It would enable you to proceede with execution, even though an exception has occured. If you don't do it that way, you can catch the exception at the end of the procedure, do "something" about it (if nothing else, ignore it), but you can't continue.

    It would look like this, just as an example:
    Code:
    CREATE OR REPLACE PROCEDURE prc_test IS
      l_first_value number;
      l_second_value number;
    
    BEGIN
      -- first query
      BEGIN
        SELECT some_value INTO l_first_value
          FROM some_table
          WHERE id = 1234;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
      
      -- second query
      BEGIN
        SELECT another_value INTO l_second_value
          FROM another_table
          WHERE date_column = TO_DATE('11.08.2008', 'dd.mm.yyyy');
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_second_value := -2;
      END;
    END;

  3. #3
    Join Date
    Jun 2008
    Posts
    17
    Thanks LittleFoot. But I do not find this as a good option. I mean that enclosing each select statement in an exception block, unnecessary overhead. Is not there any other way out?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I'm concerned, feel free to use any other option.

    enclosing each select statement in an exception block (is an) unnecessary overhead
    How did you measure that? Could you, please, provide figures you got by performing a test? What is the percentage you are talking about (i.e. "using this option, your procedure ran xx.xx% slower than before")?

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by Littlefoot
    How did you measure that?
    My guess would be a visual check of the number of keystrokes required.

    @OP
    Your requirement states that you need to, for each select statement, handle the no_data_found exception The only other way that I can think of to do this without exception handling is to not have any exceptions. i.e. what you will need to do is count how many rows would be returned by each select statement. For each statement, if the count is not 1, then do not perform the select. Bear in mind that this is basically the wrong way to go about it and Littlefoot's is the correct way, but if you really insist on doing it without exception handling (bizzarre requirement, I'm guessing either homework or a fundamental misunderstanding of PL/SQL) you could waste your resources in the way that I describe.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •