Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Friday, 1 April 2016


NVL2 Function we can pass three parameters if first parameter is not null it returns second parameter. if first parameter is null it return third parameter

Syntax:  NVL2 (String1, expr2 , expr3)

If String1 is not null expr2 will return
If String1 is null expr3 will return

         SQL> select * from student;          -- here for 3rd row marks value is null

                         NO  NAME    MARKS
                          ---   -------       ---------
                          1        a           100
                          2        b           200
                          3        c

SQL> select no, name, nvl2(marks,300,100) from student;

                       NO NAME  NVL2(MARKS,300)
                       ---  -------  ---------------------
                        1           a              300
                        2           b              300
                         3          c              100

You May Like                                             
                            SQL Abs
                            SQL Sign
                            SQL Sqrt
                            SQL Mod
                            SQL NVL                           
                            SQL Power
                            SQL Exp
                            SQL Ln
                            SQL Log
                            SQL CEIL
                            SQL Floor
                            SQL Round
                            SQL Trunc
                            SQL Bitand
                            SQL Greatest
                            SQL Least
                            SQL Coalesce
Share this article :


  1. Hello There,

    Your blog is such a complete read. I like your approach with Oracle Apps Tutorials . Clearly, you wrote it to make learning a cake walk for me.

    I have two tables A and B. Where A is a parent table and B is a child table.
    I need all the records from table A that is not present in table B.

    I would like to know the best way in terms of performance to achieve this. (I have millions of records in the tables).

    But nice Article Mate! Great Information! Keep up the good work!

    Thank you,

  2. Hi There,

    In debt to you for making my learning on the SQL-NVL2 area so hassle-free! I lay my faith on your writings.
    I am facing one ora-error while executing the procedure.
    ORA-06512: at "Lax.CLOSE_DUP"
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    i am suspecting it could be length issue with some of variables i have declared inside procedure
    l_effectreqcount number;----> This is tracking no of records being fetched.
    l_postdata varchar2(999); ---> This is the variable for which i believe the error has come.
    The reason is that in this variable l_postdata I am concatenating all the data being fetched from is something like this.

    THANK YOU!! This saved my butt today, I’m immensely grateful.


  3. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  4. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.