Oracle CASE statement - return expression value?

Question

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?

Your question contains its own answer. The way to preserve a value in PL/SQL is to assign it to a variable. You say you are concerned about the length of the return value; but you are going to return it from your own function. What's the return type of your function? If it is VARCHAR2, then use the maximum length of a VARCHAR2 (32767) for the local variable. — Dave Costa
– Dave Costa, Commented Aug 30, 2016 at 0:05
Aside from other considerations - the code you wrote can be simplified as follows: RETURN CASE UPDATE_RECORDS(...) WHEN... THEN ... ..... END; (warning: in this case it's END, not END CASE, since now you are using a CASE expression). Better to have one RETURN statement instead of three. — user5683823
– user5683823, Commented Aug 30, 2016 at 2:52

Gary Myers · Accepted Answer · 2016-08-29 23:59:41Z

1

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.

answered Aug 29, 2016 at 23:59

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hchaznedaroglu · Accepted Answer · 2016-08-29 20:04:30Z

0

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.

answered Aug 29, 2016 at 20:04

hchaznedaroglu

15511 bronze badges

Comments

Kamil Gosciminski · Accepted Answer · 2016-08-29 20:08:28Z

0

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.

answered Aug 29, 2016 at 20:08

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

1 Comment

Dave Costa Over a year ago

Potentially good idea to use CLOB. But depends on the return type of the function that will contain the CASE statement.

Collectives™ on Stack Overflow

Oracle CASE statement - return expression value?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related