Re: Calling oracle function from PostgreSQL - Mailing list pgsql-novice

To call an Oracle function from a PostgreSQL database, you can use a Foreign Data Wrapper (FDW) to connect PostgreSQL to Oracle. One of the most commonly used FDWs for this purpose is oracle_fdw. Here’s a step-by-step guide to achieve this:

Step 1: Install oracle_fdw

1. Install Oracle Client Libraries: Ensure that the Oracle client libraries are installed on your PostgreSQL server. You can download these from the Oracle website.

2. Install oracle_fdw: You can install oracle_fdw using your package manager or by compiling it from source.

   For example, on Debian-based systems:

   sudo apt-get install postgresql-<version>-oracle-fdw

   Or, to compile from source:

   git clone https://github.com/laurenz/oracle_fdw.gitcd oracle_fdwmakesudo make install

Step 2: Configure oracle_fdw

1. Create the Extension: In your PostgreSQL database, create the oracle_fdw extension.

   CREATE EXTENSION oracle_fdw;

2. Create a Foreign Server: Define a foreign server that connects to your Oracle database.

   CREATE SERVER oracle_serverFOREIGN DATA WRAPPER oracle_fdwOPTIONS (dbserver '//oracle_host:1521/oracle_service_name');

3. Create a User Mapping: Map a PostgreSQL user to an Oracle user.

   CREATE USER MAPPING FOR postgresSERVER oracle_serverOPTIONS (user 'oracle_user', password 'oracle_password');

Step 3: Create Foreign Table

1. Create a Foreign Table: Define a foreign table in PostgreSQL that maps to the Oracle table or view.

   CREATE FOREIGN TABLE oracle_table (    column1 datatype,    column2 datatype,    ...)SERVER oracle_serverOPTIONS (schema 'oracle_schema', table 'oracle_table');

Step 4: Call Oracle Function

1. Call the Oracle Function: You can now call the Oracle function using the foreign table. Here’s an example of how you might do this:

   SELECT oracle_function(column1, column2)FROM oracle_tableWHERE some_condition;

Documentation Links

• oracle_fdw GitHub Repository
• PostgreSQL Foreign Data Wrapper Documentation

Next Steps

1. Test the Connection: Ensure that the connection between PostgreSQL and Oracle is working correctly by querying the foreign table.
2. Handle Data Types: Pay attention to data type compatibility between PostgreSQL and Oracle.
3. Optimize Performance: Consider performance implications and optimize queries as needed.