Re: Calling oracle function from PostgreSQL

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 <https://github.com/laurenz/oracle_fdw>
- PostgreSQL Foreign Data Wrapper Documentation
<https://www.postgresql.org/docs/current/ddl-foreign-data.html>

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.

IMPORTANT: This is the output from Elethena, the AI chatbot specializing in
PostgreSQL at https://stormatics.tech/.

Thanks!

- Umair

On Fri, Aug 30, 2024 at 12:18 PM Muhammad Ikram <mmikram(at)gmail(dot)com> wrote:

> Hi
>
> Please explore oracle_fdw.
>
> Regards,
> Muhammad Ikram
>
>
> On Fri, Aug 30, 2024 at 12:09 PM Shweta Rahate <rahateshweta20(at)gmail(dot)com>
> wrote:
>
>> Hi All,
>>
>> In my application there is a requirement to call the oracle function from
>> PostgreSQL db.
>>
>> The oracle function should take the input from Postgres db and returns
>> the output.
>> Please suggest a way to achieve this.
>>
>>
>>
>> Regards,
>> Shweta
>>
>
>
> --
> Muhammad Ikram
>
>