Forum Stats

  • 3,826,733 Users
  • 2,260,699 Discussions
  • 7,897,064 Comments

Discussions

Please make NVL2 available in PL/SQL

ApexBine
ApexBine Member Posts: 153 Silver Badge
edited May 8, 2020 3:44PM in Database Ideas - Ideas

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.

ApexBineWilliam RobertsonSven W.FatMartinRGregVPiotr Wrzosek
6
6 votes

Delivered · Last Updated

Comments

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    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
    Billy Verreynne Software Engineer Member Posts: 28,828 Red Diamond

    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.
    Sven W. Member Posts: 10,535 Gold Crown

    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.

    William RobertsonApexBinefac586
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    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
    Cherif bh Member Posts: 158 Bronze Badge

    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