Skip to main content
Version: 2.x

orafce

The orafce module provides Oracle compatibility SQL functions, data types, and packages in Apache Cloudberry. It implements a subset of the functions and packages of the Oracle RDBMS, which helps you port Oracle applications.

The module in Apache Cloudberry is a modified version of the open source Orafce extension. The source files are in the gpcontrib/orafce directory of the Apache Cloudberry source code and are based on Orafce 4.9 (4.9.2).

Installation​

orafce is not built by default. To build and install the module with Apache Cloudberry, specify the --enable-orafce option when you run configure:

./configure --enable-orafce [other options...]
make
make install

The module is an extension (version 4.9, not relocatable). Before you can use it, create the extension in each database where you want to use it:

CREATE EXTENSION orafce;

Schemas​

The extension creates its objects in the following schemas:

SchemaContent
oracleOracle compatible functions (for example oracle.substr(), oracle.add_months(), oracle.nvl2(), and oracle.sysdate()), the oracle.date domain, the oracle.varchar2 and oracle.nvarchar2 data types, and the oracle.dual view.
dbms_outputThe DBMS_OUTPUT package.
dbms_pipeThe DBMS_PIPE package.
dbms_utilityThe DBMS_UTILITY package.
dbms_assertThe DBMS_ASSERT package.
dbms_randomThe DBMS_RANDOM package.
dbms_sqlThe DBMS_SQL package.
utl_fileThe UTL_FILE package.
plvstr, plvchr, plvdate, plvsubst, plvlexThe PLVision string, character, date, substitution, and lexer packages.
plunitThe PLUnit assertion functions.

Usage​

To call the functions in the oracle schema without a schema qualifier, add the schema to the search path. For example, this command sets the default search path of a database:

ALTER DATABASE db_name SET search_path = "$user", public, oracle;

Some functions in the oracle schema have the same name as a built-in function, for example oracle.substr(). The built-in pg_catalog schema is searched first by default. To give precedence to the Oracle compatible version, either qualify the function name with the schema, or put oracle before pg_catalog in the search path:

SET search_path TO oracle, "$user", public, pg_catalog;

For example:

SET search_path TO public, oracle;
SELECT add_months(date '2003-08-01', 3);
add_months
------------
2003-11-01
(1 row)

You can use the oracle.varchar2 and oracle.nvarchar2 data types as the distribution key or as the partition key of a table:

CREATE TABLE t1 (str oracle.varchar2(30)) DISTRIBUTED BY (str);

Configuration parameters​

The module defines the following configuration parameters. You can set them in a session with SET.

ParameterDefaultDescription
orafce.nls_date_formatNot setEmulates the date output behavior of Oracle.
orafce.timezoneGMTThe time zone that the oracle.sysdate() function uses and that oracle.dbtimezone() returns.
orafce.varchar2_null_safe_concatfalseWhether the concatenation of varchar2 values is null safe.
orafce.sys_guid_sourceuuid_generate_v1The function that oracle.sys_guid() uses to make its result: uuid_generate_v1, uuid_generate_v1mc, or uuid_generate_v4 from the uuid-ossp extension, or the built-in gen_random_uuid. In this version, the value uuid_generate_v4 is stored as uuid_generate_v1.
orafce.using_substring_zero_width_in_substrwarning_oracleThe behavior of the substr() function when the substring length argument is zero. Valid values are warning_oracle, warning_orafce, oracle, and orafce.

Differences from the original Orafce extension​

The module in Apache Cloudberry differs from the original Orafce 4.9 extension as follows:

  • The oracle.decode() functions are not created. Apache Cloudberry has built-in support for decode(): the parser transforms a decode() call into a CASE expression, so you can use decode() without the orafce extension. For example:

    SELECT decode(2, 1, 'ABC', 2, 'DEF');
    case
    ------
    DEF
    (1 row)
  • The dbms_alert package is not available. The dbms_alert schema and its functions are not created, because the implementation cannot run in an MPP environment.

note

PostgreSQL 14, on which Apache Cloudberry is based, provides sinh(), cosh(), and tanh() as built-in functions. See Mathematical Functions and Operators in the PostgreSQL documentation. You do not need orafce to use them.

Additional documentation​

For detailed information about the individual functions and supporting objects in this module, see the following files in the gpcontrib/orafce directory of the Apache Cloudberry source code:

  • README.asciidoc
  • The documents in the doc/orafce_documentation directory
  • The Oracle to PostgreSQL migration notes in the doc/sql_migration directory