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:
- decode (See Oracle and Greenplum Implementation Differences for more information.)
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
Install the Oracle Compatibility Functions in each of your databases using the CREATE EXTENSION SQL command.
$ psql -d db_name -c 'CREATE EXTENSION orafce;'
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
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); 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 Oracle, but not in
- 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
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.