PL SQL String comparison

Question

I'm writing a procedure in PL/SQL, where I need to check whether a given value does not equal x or y. I use the following code to check:

IF p_campaignPaymentType != 'per month' || 'per creation' THEN
    RAISE ex_invalidPaymentType;
END IF;

Now, even if the string given in p_campaignPaymentType is 'per month' or 'per creation', it raises the exception ex_invalidPaymentType.

What is the problem with the IF-sentence, and/or what could I do to as an alternative for checking?

|| is string concatenation operator, not logical OR operator (even if it was it'd be used incorrectly anyway). Use OR operator. — Nick Krasnov
– Nick Krasnov, Commented Mar 2, 2017 at 11:04
@nicholas-krasnov Using OR instead of || resulted in the following error: Error(53,46): PLS-00382: expression is of wrong type — Winston Smith
– Winston Smith, Commented Mar 2, 2017 at 11:07
First of all, sorry for OR operator in the phrase "Use OR operator", it was a typo I meant to say AND, of course. You need to place it between two logical conditions. — Nick Krasnov
– Nick Krasnov, Commented Mar 2, 2017 at 11:14

Mureinik · Accepted Answer · 2017-03-02 11:19:05Z

3

|| is the string concatination operator, not the logical or operator I think you're assuming it is. So, your snippet actually means:

IF p_campaignPaymentType != 'per monthper creation' THEN
    RAISE ex_invalidPaymentType;
END IF;

But even if it were the logical or operator, it would still be wrong, as any string is either not equal to 'per month' or to 'per creation' (e.g., 'per month' is not equal to 'per creation'). Instead, you should use the logical and operator:

IF p_campaignPaymentType != 'per month' AND 
   p_campaignPaymentType != 'per creation' THEN
    RAISE ex_invalidPaymentType;
END IF;

Or, more elegantly, the not in operator:

IF p_campaignPaymentType NOT IN ('per month', 'per creation') THEN
    RAISE ex_invalidPaymentType;
END IF;

edited Mar 2, 2017 at 11:19

answered Mar 2, 2017 at 11:07

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

Winston Smith Over a year ago

Cheers! That solved my problem. I can't accept your answer for another 7 minutes, but I'll make sure to do it :)

Collectives™ on Stack Overflow

PL SQL String comparison

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related