Describes the Oracle Compatibility SQL functions in Greenplum Database. These functions target PostgreSQL but can also be used in Greenplum Database.

About the Oracle Compatibility Functions for Greenplum Database

Oracle Compatibility Functions for Greenplum Database is based on the open source Orafce project at https://github.com/orafce/orafce.

The modified Orafce source files for Greenplum Database can be found in the gpcontrib/orafce directory in the Greenplum Database open source project. The source includes the Orafce 3.6.1 release and additional commits to 3af70a28f6.

The following functions are available by default in Greenplum Database and do not require installing Oracle Compatibility Functions:

For information about using the Oracle Compatibility Functions, refer to the documentation at the Orafce project home at https://github.com/orafce/orafce. The source files for the documentation in the Orafce project are also included in the Greenplum Database open source project.

Installing Oracle Compatibility Functions

Note: Always use the Oracle Compatibility Functions module included with your Greenplum Database version. Before upgrading to a new Greenplum Database version, uninstall the compatibility functions from each of your databases, and then, when the upgrade is complete, reinstall the compatibility functions from the new Greenplum Database release. See the Greenplum Database release notes for upgrade prerequisites and procedures.

Install the Oracle Compatibility Functions in each of your databases using the CREATE EXTENSION SQL command.

$ psql -d db_name -c 'CREATE EXTENSION orafce;'
Note: Some Oracle Compatibility Functions reside in the oracle schema. To access them set the search path for the database to include the oracle schema name. For example, this command sets the default search path for a database to include the oracle schema:
ALTER DATABASE db_name SET search_path = "$user", public, oracle;

To uninstall Oracle Compatibility Functions, drop the orafce extension using the DROP EXTENSION SQL command.

$ psql -d db_name -c 'DROP EXTENSION orafce;'

If you are uninstalling the compatibility functions from a Greenplum Database 5.x release, use this command instead:

$ psql -d db_name -f $GPHOME/share/postgresql/contrib/uninstall_orafunc.sql

Oracle and Greenplum Implementation Differences

There are some differences in the implementation of the compatibility functions in Greenplum Database from the Oracle implementation. If you use validation scripts, the output may not be exactly the same as in Oracle. Some of the differences are as follows:

  • Oracle performs a decimal round off, Greenplum Database does not:
    • 2.00 becomes 2 in Oracle
    • 2.00 remains 2.00 in Greenplum Database
  • The provided Oracle compatibility functions handle implicit type conversions differently. For example, using the decode function:
    decode(expression, value, return [,value, return]...
                [, default])

    Oracle automatically converts expression and each value to the data type of the first value before comparing. Oracle 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);
    (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 Oracle, but not in Greenplum Database:
    • 9223372036854775 displays as 9.2234E+15 in Oracle
    • 9223372036854775 remains 9223372036854775 in Greenplum Database
  • The default date and timestamp format in Oracle is different than the default format in Greenplum Database. 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;

    Greenplum Database returns the row, but Oracle returns no rows.

    Note: The correct syntax in Oracle to return the row is:
    SELECT DECODE(to_char(date1, 'YYYY-MM-DD'), '2001-11-11', 
                  '2001-01-01') FROM TEST

Following are differences between using the Oracle Compatibility Functions with PostgreSQL and Greenplum Database.

  • The decode() function is removed from the Greenplum Database Oracle Compatibility Functions. The Greenplum Database parser converts decode() - function calls to case statements.
  • The functions in the Oracle Compatibility Functions dbms_pipe package execute only on the Greenplum Database master host.
  • The functions in the Oracle Compatibility Functions dbms_alert package are not implemented for Greenplum Database.
  • The upgrade scripts in the Orafce project do not work with Greenplum Database.