Install orafce for AlloyDB Omni

Select a documentation version:

This page describes how to manually add the orafce extension to an existing AlloyDB Omni installation. The orafce extension provides functions and operators that emulate a subset of functions and packages from the Oracle database. This extension simplifies migration of applications from Oracle to PostgreSQL-compatible databases such as AlloyDB Omni.

Before you begin

Install AlloyDB Omni on your system.

Add orafce to your AlloyDB Omni installation

To add the orafce extension to your AlloyDB Omni installation, follow these steps:

Find your installed AlloyDB Omni version labels:

Docker

 docker  
run  
--rm  
-it  
google/alloydbomni  
cat  
VERSION.txt

Podman

 podman  
run  
--rm  
-it  
google/alloydbomni  
cat  
VERSION.txt

The output is similar to the following:

 AlloyDB Omni version: 16.8.0

Take note of the AlloyDB Omni version number because you need it in the next step.

Build a custom Docker image that includes the orafce extension:

 $  
~/alloydb-omni-orafce

$  
 cd 
  
~/alloydb-omni-orafce

$  
cat  
<<EOF > 
Dockerfile
FROM  
google/alloydbomni:16.8.0-ubi
RUN  
 arch 
 = 
 $( 
uname  
-m ) 
RUN  
dnf  
install  
-y  
https://download.postgresql.org/pub/repos/yum/reporpms/EL-9- $( 
arch ) 
/pgdg-redhat-repo-latest.noarch.rpm
RUN  
dnf  
install  
-y  
orafce_16 # On RHEL-based systems, PostgreSQL extensions like Orafce and PostGIS are typically installed in /usr/pgsql-16/share/extension and /usr/pgsql-16/lib. 
 # This step creates symbolic links to those files in the paths expected by AlloyDB Omni, 
 # which lets AlloyDB Omni locate extension control files and shared libraries without duplicating data. 
RUN  
 \ 
  
 for 
  
file  
 in 
  
/usr/pgsql-16/share/extension/* ; 
  
 do 
  
 \ 
  
 target 
 = 
 "/usr/lib/postgresql/16/share/extension/ 
 $( 
realpath  
-m  
--relative-to = 
/usr/pgsql-16/share/extension/  
 " 
 $file 
 " 
 ) 
 " 
 ; 
  
 \ 
  
 if 
  
 [ 
  
!  
-e  
 " 
 $target 
 " 
  
 ] 
 ; 
  
 then 
  
 \ 
  
ln  
-s  
 " 
 $file 
 " 
  
 " 
 $target 
 " 
  
 || 
  
 ( 
 echo 
  
 "Failed to link \" 
 $file 
 \" to \" 
 $target 
 \", exiting." 
 && 
 exit 
  
 1 
 ) 
 ; 
  
 \ 
  
 else 
  
 \ 
  
 echo 
  
 " 
 $target 
 already exists" 
 ; 
  
 \ 
  
 fi 
 ; 
  
 \ 
 done 
 && 
 \ 
 for 
  
file  
 in 
  
/usr/pgsql-16/lib/* ; 
  
 do 
  
 \ 
  
 target 
 = 
 "/usr/lib/postgresql/16/lib/ 
 $( 
realpath  
-m  
--relative-to = 
/usr/pgsql-16/lib/  
 " 
 $file 
 " 
 ) 
 " 
 ; 
  
 \ 
  
 if 
  
 [ 
  
!  
-e  
 " 
 $target 
 " 
  
 ] 
 ; 
  
 then 
  
 \ 
  
ln  
-s  
 " 
 $file 
 " 
  
 " 
 $target 
 " 
  
 || 
  
 ( 
 echo 
  
 "Failed to link \" 
 $file 
 \" to \" 
 $target 
 \", exiting." 
 && 
 exit 
  
 1 
 ) 
 ; 
  
 \ 
  
 else 
  
 \ 
  
 echo 
  
 " 
 $target 
 already exists" 
 ; 
  
 \ 
  
 fi 
 ; 
  
 \ 
 done 
EOF

Create a new container with AlloyDB Omni named my-omni-orafce :

 docker  
build  
-t  
google/alloydbomni-with-orafce:latest
docker  
run  
--name  
my-omni-orafce  
-e  
 POSTGRES_PASSWORD 
 = 
 NEW_PASSWORD 
  
-d  
google/alloydbomni-with-orafce: OMNI_VERSION

Connect to your database with the orafce extension:

 docker  
 exec 
  
-it  
my-omni-orafce  
psql  
-h  
localhost  
-U  
postgres

Enable orafce:

  CREATE 
  
 EXTENSION 
  
 IF 
  
 NOT 
  
 EXISTS 
  
 ORAFCE 
 ;

Confirm that orafce is installed and enabled:
```
  SELECT 
  
 oracle 
 . 
 sysdate 
 (); 
 
```
The output is similar to the following:
```
 postgres=# SELECT oracle.sysdate();
sysdate
---------------------
2024-06-10 16:36:30
(1 row) 
```
Note: Due to some known issues with the orafce extension on AlloyDB Omni, we don't recommend that you use the dbms_utility.format_call_stack , dbms_output.get_line , and regexp_* functions.