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@gmail.com> wrote:

> Hi
>
> Please explore oracle_fdw.
>
> Regards,
> Muhammad Ikram
>
>
> On Fri, Aug 30, 2024 at 12:09 PM Shweta Rahate <rahateshweta20@gmail.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
>
>