2

With PL/SQL, I perform a SELECT INTO statement, based on the result I would like to perform the same logic. I would like to check for:

  • no result (0 rows returned)
  • empty result (1 row with '' returned)
  • null result (1 row with null returned)

In my current solution, I use a NO_DATA_FOUND exception for case 1 and IF checks for case 2+3, but call the same logic in either way, which I don't like:

     BEGIN
        SELECT some_value
          INTO my_variable
          FROM some_table
         WHERE somecheck = 123

        IF my_variable IS NULL OR my_variable = ''
        THEN
            ##perform logic##
        END IF;
     EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            ##perform same logic as above##
     END;

Can this be solved somehow better to prevent that I have to copy paste the same code for both, my IF check and my exception?

In particular, is it safe to extend my IF checks with

IF SQL%ROWCOUNT = 0 THEN ...

instead of using NO_DATA_FOUND exception to achieve my goals?

Improve this question

2 Answers 2

Reset to default
2

If you aggregate some_value, then you won't get NO_DATA_FOUND so you can omit that exception handler. NULL and '' are the same, so code can be shortened to 

select max(some_value) into my_variable ...

if my_variable is null then 
   ##perform logic##
end if;
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hm aggregating via MAX returns ORA-00932, inconsistent data type, clob returned but - expected
You never said it was a CLOB. Try with select max(to_char(some_value)) into ...
He will also silently 'avoid' too_many_rows - which probably is not desired. Common logic can and should be extracted to procedures - there's no need for aggregation in this case.
Well, yes, @piezol; that's what the OP did initially and wanted to avoid it. We wouldn't be discussing it otherwise.
@Littlefoot No, he didn't silently avoid too_many_rows - if there is more than one row his code will throw exception - yours will silently go on.
|
0

You could only put the logic in the exception handler, and explicitly raise that exception on null:

 BEGIN
    SELECT some_value
      INTO my_variable
      FROM some_table
     WHERE somecheck = 123;

    IF my_variable IS NULL
    THEN
        RAISE no_data_found;
    END IF;
 EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        ##perform logic##
 END;

or get the same effect by excluding null results in the first place:

 BEGIN
    SELECT some_value
      INTO my_variable
      FROM some_table
     WHERE somecheck = 123
       AND some_value IS NOT NULL;

 EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        ##perform logic##
 END;

Not that null and an empty string ('') are the same thing so you don't need to test for both. But as you mentioned the column is actually a CLOB, you could could check for a not-null but empty CLOB by adding a check for dbms_lob.getlength(some_value) > 0, either to the query or before the explicit raise.

A third approach is to add a local function that performs the logic, and call that from both places in your original code.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.