1 2 Previous Next 21 Replies Latest reply: May 2, 2013 4:08 PM by rp0428 RSS

    NVL statement for all datatypes

    bobmagan
      On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
      Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is.
      ie
      nvl(x,'1') or nvl(mydate,sysdate)

      thanks
        • 1. Re: NVL statement for all datatypes
          BluShadow
          The documentation details what arguments are acceptable to the NVL function:

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions119.htm#SQLRF00684

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements002.htm#g195937
          • 2. Re: NVL statement for all datatypes
            kendenny
            You need something that will work for all data types. If you set your nls_date_format to something all numeric like 'YYYYMMDD' then you can use
            nvl(column,20991231)
            • 3. Re: NVL statement for all datatypes
              bobmagan
              thanks, but I was hoping there was something that would work directly in pl/sql and would not care what the datatype of the item was.
              • 4. Re: NVL statement for all datatypes
                odie_63
                bobmagan wrote:
                thanks, but I was hoping there was something that would work directly in pl/sql and would not care what the datatype of the item was.
                I'm not sure what you're asking.
                If you want to use NVL then you have to pass a second argument of the same datatype as the first, don't you ?
                • 5. Re: NVL statement for all datatypes
                  bobmagan
                  Right, but i am dynamically building the comparison using the fields from user_tab_columns (see below). It can return all different datatypes (number,varchar,clob,blob) and I wanted something that would work with all of them. In the nvl stmt below I am using '~', which wont work for dates, blobs, number etc.

                  CURSOR get_columns IS
                  SELECT column_name
                  FROM user_tab_columns
                  WHERE table_name = l_table_name and

                  FOR x in get_columns LOOP
                  if l_if_stmt is not null then
                  l_if_stmt := l_if_stmt || ' OR ';
                  end if;
                  l_if_stmt := l_if_stmt ||
                  '(nvl(:old.' || substr(x.column_name,5) || ',' || '''' || '*~*' || '''' || ')' || ' <> ' ||
                  'nvl(:new.' || substr(x.column_name,5) || ',' || ''''
                  || '*~*' || '''' || ')' || ')';
                  END LOOP;
                  • 6. Re: NVL statement for all datatypes
                    odie_63
                    bobmagan wrote:
                    Right, but i am dynamically building the comparison using the fields from user_tab_columns
                    And there comes the missing part of the puzzle...

                    Too much genericity kills genericity.
                    Your approach is flawed. Why not write down all columns explicitly instead of this mess ?
                    • 7. Re: NVL statement for all datatypes
                      thomaso
                      Bob
                      View USER_TAB_COLUMNS does have a column DATA_TYPE.
                      What you can do is to add 'nvl' function specific to data type of the column:
                      ...
                         if x.data_type='VARCHAR2' then
                            l_if_stmt := ....'*~*'...;;
                         elsif x.data_type='DATE' then
                            l_ifStmt := ... '19000101'...; 
                         elsif ...
                      ...
                      Just an idea.

                      HTH
                      Thomas
                      • 8. Re: NVL statement for all datatypes
                        jeneesh
                        I dont know whether this will work for all the datatypes, but will work for DATE, NUMBER..
                        nvl(to_char(column_name),'~')
                        • 9. Re: NVL statement for all datatypes
                          bobmagan
                          It has to be dynamic as the user can specify what table they want this to work against. based on that, it dynamically creates the DB trigger with the check in it.

                          Another possibility may be using the 'INTERSECT', as I don't care which column value is different only that there is at least 1 column value that is different.

                          CURSOR check_data IS select 'same' from dual
                          where exists(select :old.val1,:old.val2,:old.val3 from dual INTERSECT select :new.val1,:new.val2,:new.val3 from dual);

                          If I get no rows back, 1 of the values was different. Any thoughts on this approach?
                          • 10. Re: NVL statement for all datatypes
                            Iordan Iotzov
                            Instead of NVL’s, can you use a clause like this:
                            (
                                   TAB1.X = TAB2.Y
                            OR
                                   (TAB1.X  IS NULL and TAB2.Y is NULL)
                            )
                            …
                            ?

                            Are you planning to use the NVLs in where clauses of SQL statements, or just in PL/SQL code?

                            Iordan Iotzov
                            • 11. Re: NVL statement for all datatypes
                              bobmagan
                              Just in PL/SQL. I wanted to use 'not equal OR' instead of '= AND' because I may be comparing lots of fields and it would be quicker when doing the check.

                              Edited by: bobmagan on May 2, 2013 7:40 AM
                              • 12. Re: NVL statement for all datatypes
                                rp0428
                                >
                                Any thoughts on this approach?
                                >
                                Yes - why don't you start over and tell us WHY you are doing this.

                                What is the problem you are trying to solve?

                                Oracle already provides the UPDATE trigger which fires when a column is updated. And that UPDATE trigger can specify the list of columns that you care about.

                                See the PL/SQL Language Doc
                                http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#BABCIBBJ
                                >
                                How Column Lists Affect UPDATE Triggers

                                An UPDATE statement might include a list of columns. If a triggering statement includes a column list, the trigger fires only when one of the specified columns is updated. If a triggering statement omits a column list, the trigger fires when any column of the associated table is updated. A column list cannot be specified for INSERT or DELETE triggering statements.
                                >
                                So the ONLY possible use of your complicated approach is to detect is a user performs an UPDATE of a column but specifies the same value that the column already has.

                                WHY WOULD YOU CARE ABOUT THAT?

                                Also your use of 'USER_TAB_COLUMNS' is also flawed. There can be hidden columns that your code would also return and try to use and fail on since you can't access them directly.

                                If you are trying to 'capture' changes at the column level Oracle already has plenty of funcitonality for that also: materialized view logs, change-data-capture and the replication facilities.

                                Sounds like you are trying to reinvent the wheel. Most attempts at doing that wind up missing a few spokes.
                                • 13. Re: NVL statement for all datatypes
                                  Iordan Iotzov
                                  In my opinion, nothing beats simple comparisons when it comes to speed.

                                  Iordan Iotzov
                                  • 14. Re: NVL statement for all datatypes
                                    bobmagan
                                    Thanks. I know the UPDATE trigger can list the columns you care about, but unfortunately the system has lots of legacy code in it that issues false updates for data that hasn't changed. I can't change that. The only way to be sure is to physically check the old and new values. Works great except for he null issues. The other methods you listed each have pieces of what I need, but there is not 1 that has all of them.
                                    1 2 Previous Next