CODEWITHSUNDEEP
oracle-databaseplsqlcase-statement
user6771619
user6771619

Reputation: 13

Oracle CASE statement - return expression value?

I would like to evaluate the expression of a CASE statement (which is the value returned from a function) and if it does not meet one of the conditions return the expression value itself.

For instance:

CASE UPDATE_RECORDS_F(party_number)
WHEN 'ONE_RECORD_UPDATED' THEN RETURN 'OK';
WHEN 'MULTIPLE_RECORDS_UPDATED' THEN RETURN 'OK_MULTIPLE';
ELSE RETURN (expression value);
END CASE;

The 'else' case is needed for when an exception is thrown. I could assign the returned function value to a string and evaluate that, but I don't know how long an error message could be. I would rather handle the string value on the fly instead of creating a variable with a set length that could be exceeded.

Is there a way to do this?

Upvotes: 0

Views: 1873

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

If an exception is thrown, the return value is undefined and the exception will be propagated through the code until it finds an appropriate exception handler or returns it to the client.

If you want to cater for any return value, then look at what the function is defined as returning, which is probably a VARCHAR2. The maximum size of that is 32767 so you can be certain your function will never return a value larger than that.

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

I assume that an exception you are talking about is actually a string. Is that right?

Anyways, I believe you should be storing the output of a function in a variable of type CLOB which can be more than 2 milions of characters long. This way you won't limit your output to 4000 characters as it is in case of varchar type.

See documentation on Oracle data types.

Upvotes: 0

hchaznedaroglu
hchaznedaroglu

Reputation: 155

First thing that comes to my mind is to modify your UPDATE_RECORDS_F function so that it catches any exception and returns a predefined string so that you can detect it in your case statement. You do not need to assign the actual exception string. Just return something that you come up with when your function drops to exception block.

Upvotes: 0

Related Questions