This discussion is archived
1 2 Previous Next 21 Replies Latest reply: May 2, 2013 2:08 PM by rp0428 Go to original post RSS
  • 15. Re: NVL statement for all datatypes
    Iordan Iotzov Expert
    Currently Being Moderated
    How about this:
    (
                      :NEW.col1  < >        :OLD.col1  
             OR
                      (       :NEW.col1  IS NULL and        :OLD.col1  is NOT NULL)
             OR 
                     (       :NEW.col1  IS NOT  NULL and        :OLD.col1  is NULL)
    )
    OR
    (
                      :NEW.col2    < >          :OLD.col2
             OR
                      (       :NEW.col2  IS NULL and        :OLD.col2  is NOT NULL)
             OR 
                     (       :NEW.col2  IS NOT  NULL and        :OLD.col2  is NULL)
    )
    …..
    Iordan Iotzov
  • 16. Re: NVL statement for all datatypes
    rp0428 Guru
    Currently Being Moderated
    >
    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.
    >
    Isn't that what I just said above?
    >
    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.
    >
    Except I also said this which you didn't respond to:
    >
    WHY WOULD YOU CARE ABOUT THAT?
    >
    Again - what is the problem you are trying to solve?

    What difference does it make if there is a 'false' update?
  • 17. Re: NVL statement for all datatypes
    bobmagan Newbie
    Currently Being Moderated
    The requirements are to only write records that have a change (no others). The only way to enure that would be to do the comparison.
  • 18. Re: NVL statement for all datatypes
    William Robertson Oracle ACE
    Currently Being Moderated
    To be fair, it doesn't seem completely unreasonable to want to generate code to handle logging (or whatever) selectively on a load of tables. What people usually want to do is have the trigger code dynamic, and we tell them they're crazy and (if they really must) they should generate the trigger code once.

    The deeper problem is that, even if a valid NVL expression can be generated for each datatype, you have to make sure it is not a value that can actually exist in the column. For example, you might use DATE '-0001-01-01' for a DATE column and 0.1 for an INTEGER column (or maybe just for fun, DATE '0000-01-01' and BINARY_FLOAT_NAN).
  • 19. Re: NVL statement for all datatypes
    rp0428 Guru
    Currently Being Moderated
    >
    The requirements are to only write records that have a change (no others). The only way to enure that would be to do the comparison.
    >
    There is no mention of 'WRITE RECORDS' anywhere in your thread until now.

    That is because you have still not told us WHAT you are really trying to do. I suspect it is what William just mentioned in his reply, logging, but it is YOUR responsibility to tell us so we don't have to guess or make assumptions.

    We can't help you if we don't know what you are really trying to accomplish.

    The simplest generic comparison would be to convert every column using TO_CHAR and compare those. TO_CHAR doesn't have a problem with NULL values. Add a single space (' ') to the end of each value.
    select * from emp where to_char(comm) || ' ' = ' '
    and where rownum < 3;
    
    EMPNO     ENAME     JOB     MGR     HIREDATE     SAL     COMM     DEPTNO
    7369     SMITH     CLERK     7902     12/17/1980     800          20
    7566     JONES     MANAGER     7839     4/2/1981     2975          20
  • 20. Re: NVL statement for all datatypes
    bobmagan Newbie
    Currently Being Moderated
    I didn't mention 'WRITING' records because it has nothing to do with the problem. Your suggestion won't work as you can't do a to_char on a BLOB column. I think i will go with the suggestion that include the the null and not null checks in the comparison. Thanks everyone for their suggestions.
  • 21. Re: NVL statement for all datatypes
    rp0428 Guru
    Currently Being Moderated
    >
    I didn't mention 'WRITING' records because it has nothing to do with the problem
    >
    I'd venture that it has EVERYTHING to do with the problem. It would make NO sense at all to identify differences in a trigger and then not 'write' something somewhere about what was found. The only other action you could take is raise an exception and that would undo the 'good' as well as the 'bad'.
    >
    Your suggestion won't work as you can't do a to_char on a BLOB column.
    >
    What? So now you want to throw BLOBs into the requirements?

    Does YOUR proposed solution, that tries to perform '=' and '!=' on BLOBs work?

    Your original post and proposed process is flawed to begin with. As I pointed out earlier you can't do ANYTHING with hidden columns in that ALL_TAB view you are using or with LONG columns or with VARRAYS, or nested table columns or XMLTYPE columns, and so on and so on.

    That is why we can't really help you unless you first document and post what the requirements really are. And that includes ALL of the datatypes that need to be supported.

    Don't shoot the messenger when they provide a solution by telling us, afterwards, that our solution doesn't take into account a requirement that you never mentioned to begin with.

    What kind on nonsense is that?

    Post the complete list of requirements and explain how you plan to deal with all of those non-standard datatypes using your method.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points