Oracle Certification Notes: Part X

Here is a continuation of my notes as I study for the Oracle Certified Associate credential:

Character Conversion Functions

  • NVL2(value, if_not_null, if_null): Watch out that the 2nd parameter after the compared value is if_NOT_null
  • NULLIF(if_not_equal, compare_value): IF compare_value equals if_not_equal THEN return null; ELSE return if_not_equal;
  • COALESCE(if_not_null_1, if_not_null_2, … if_not_null_n): Like using nested NVL() statements. It will return the first NOT NULL value.
  • Implicit Conversion: Oracle attempts to automatically convert a value into the appropriate datatype before execution
  • Explicit Conversion: Examples TO_CHAR(), TO_NUMBER(), or TO_DATE()
  • TO_NUMBER(value, mask): Watch out for the use of format masks. If the value doesn’t fit within format mask, an error is thrown; whereas, an error would NOT be thrown if done as a part of TO_CHAR()
  • Date Formatting: SELECT TO_CHAR(SYSDATE, ‘fmMonth, DDth, Yyyysp, YEAR’) FROM dual; equals ‘February, 3RD, Two Thousand Nine, TWO THOUSAND NINE’

    Posted via Woodland Blog (

  • Leave a Comment

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.