1. Using orafce
1.1 installation
Install to Postgresql (linux)
1
2
3
4
5
6
7
8cd /tmp \
&& wget https://codeload.github.com/orafce/orafce/tar.gz/refs/tags/VERSION_4_14_4 -O orafce.tar.gz \
&& tar xvzf orafce.tar.gz \
&& mv /tmp/orafce-VERSION_4_14_4 /usr/share/postgresql/17/extension/
cd /usr/share/postgresql/17/extension/orafce-VERSION_4_14_4 \
&& make \
&& make install- Dockerfile of Postgresql-17 with orafce 4.14.4
1.2 Postgresql Database Configuration
- Create extension
1 | create extension orafce |
- Config the pg instance
To use the functions provided in oracle schema, need to add the oracle schema in search_path, and put it ahead of pg_catalog
Config in postgresql.conf
1
search_path = '"$user", public, oracle, pg_catalog'
or config in session
1
SET search_path = "$user", public, oracle, pg_catalog;
2. Simple Description
Orafce provides a series of objects, including functions, datatypes, and tables to emulate oracle database behaviours in postgresql.
It provides object defaultly in PUBLIC schema of pg database, which are complentary to Postgresql’s built-in objects.
And orafce also has some implementations having same signiture with pg built-ins. To avoid interupting the database, orafce put these ones in a seperated “oracle” schema, user has to explitly config the database to let these implementation override pg built-ins.
The functions in “oracle” schema:
- Data type
- DATE
- Function
- LENGTH
- SUBSTR
- LPAD, RPAD
- LTRIM, RTRIM, BTRIM,
- TO_DATE
- SYSDATE
- DBTIMEZONE
- SESSIONTIMEZONE
- TO_CHAR(date/time value)
- Operator
- Datetime operator
3. Orafce Documentation
This documentation describes the environment settings and functionality offered for features that are compatible with Oracle databases.
Chapter 1 Overview
Chapter 2 Notes on Using orafce
Chapter 3 Data Types
Chapter 4 Queries
Chapter 5 SQL Function Reference
Chapter 6 Package Reference
Chapter 7 Transaction behavior
4. Oracle to PostgreSQL Migration Guide
This document explains the actions required for migrating an Oracle database to PostgreSQL and provides notes on migration.