There are differences in the implementation of the compatibility functions in Greenplum Database from the original PostgreSQL orafce module extension implementation. Some of the differences are as follows:

• The original orafce module implementation performs a decimal round off, the Greenplum Database implementation does not:
  • 2.00 becomes 2 in the original module implementation
  • 2.00 remains 2.00 in the Greenplum Database implementation
• The provided Oracle compatibility functions handle implicit type conversions differently. For example, using the decode function:

  decode(expression, value, return [,value, return]...
              [, default])

  The original orafce module implementation automatically converts expression and each value to the data type of the first value before comparing. It automatically converts return to the same data type as the first result.

  The Greenplum Database implementation restricts return and default to be of the same data type. The expression and value can be different types if the data type of value can be converted into the data type of the expression. This is done implicitly. Otherwise, decode fails with an invalid input syntax error. For example:

  SELECT decode('a','M',true,false);
  CASE
  ------
   f
  (1 row)
  SELECT decode(1,'M',true,false);
  ERROR: Invalid input syntax for integer:"M" 
  LINE 1: SELECT decode(1,'M',true,false);

• Numbers in bigint format are displayed in scientific notation in the original orafce module implementation but not in the Greenplum Database implementation:
  • 9223372036854775 displays as 9.2234E+15 in the original implementation
  • 9223372036854775 remains 9223372036854775 in the Greenplum Database implementation
• The default date and timestamp format in the original orafce module implementation is different than the default format in the Greenplum Database implementation. If the following code is executed:

  CREATE TABLE TEST(date1 date, time1 timestamp, time2 
                    timestamp with time zone);
  INSERT INTO TEST VALUES ('2001-11-11','2001-12-13 
                   01:51:15','2001-12-13 01:51:15 -08:00');
  SELECT DECODE(date1, '2001-11-11', '2001-01-01') FROM TEST;

  The Greenplum Database implementation returns the row, but the original implementation returns no rows.

  Note: The correct syntax when using the original orafce implementation to return the row is:

  SELECT DECODE(to_char(date1, 'YYYY-MM-DD'), '2001-11-11', 
                '2001-01-01') FROM TEST

• The functions in the Oracle Compatibility Functions dbms_alert package are not implemented for Greenplum Database.
• The decode() function is removed from the Greenplum Database Oracle Compatibility Functions. The Greenplum Database parser internally converts a decode() function call to a CASE statement.