Please make NVL2 available in PL/SQL

ApexBine

NVL2 is precise and it's shorter than the corresponding case expression.

I can't see a reason why it should be available in SQL only.

NVL2 SQL doc:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL2.html#GUID-414D6E81-9627-4163-8AC2-BD24E57742AE

If you can't resist, feel free to post your workarounds, but please don't forget to vote up

(Yes, writing my own NVL2 is workaround #1.)

NVL2 for PL/SQL has been implemented in Oracle Database 20c.

FatMartinR

Along with DECODE.

However, I don't think either of these will happen.   It's been long-requested on places like AskTom for decades, but the issue is that this would involve digging down deep into the kernel of Oracle, and they don't want to do that in case they disrupt it.   The same reason LONGs still remain in the data dictionary.

But who knows.   We can keep asking...

Billy Verreynne

Along with DECODE.

However, I don't think either of these will happen.   It's been long-requested on places like AskTom for decades, but the issue is that this would involve digging down deep into the kernel of Oracle, and they don't want to do that in case they disrupt it.   The same reason LONGs still remain in the data dictionary.

But who knows.   We can keep asking...

The database C module used by SQL can be defined for use by the PL/SQL engine, via the STANDARD package interface - the same way as the existing common NVL() module is defined.

Assuming of course that the NVL2() module is not a hack...

Sven W.

Personally I don't need DECODE in PLSQL.

However NVL2 and also NULLIF are a quite useful as a shorthand syntax. Would be nice to see those in PLSQL too.

NVL2 is a bit like the ternary operator in JavaScript. Maybe something like that would be even more useful:

Suggestion:

x := (a is null) ? b : c;

x is set to b if a is null otherwise x is set to c.

Sven W.

I have to correct my previous statement. NULLIF is supported. I think when I wrote this I only had an older DB (11g?) at hand or made some crude mistake while testing. NULLIF works in plsql.

Cherif bh

Hello , Thanks for this idea,

It would be useful to have NVL2 implemented for PL/SQL.

NVL2 for PL/SQL has been implemented in Oracle Database 20c.

I am currently usning

SELECT NVL2(P_IDSEQ, 'M', 'A') INTO v_maj FROM DUAL

Thanks

Cherif