public inbox for [email protected]  

help / color / mirror / Atom feed

From: Umair Shahid <[email protected]>
To: Muhammad Ikram <[email protected]>
Cc: Shweta Rahate <[email protected]>
Cc: [email protected]
Subject: Re: Calling oracle function from PostgreSQL
Date: Fri, 30 Aug 2024 20:24:31 +0500
Message-ID: <CAM184Ach6Jok7Y2+nP2DA5sAzhw7w7hwfsgdqVkhdLRcmn95ug@mail.gmail.com> (raw)
In-Reply-To: <CAGeimVq1TJTeeRWX6nqUOuhr9vvMDkDC=X+NY7dYiPFWDoX+cg@mail.gmail.com>
References: <CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>
	<CAGeimVq1TJTeeRWX6nqUOuhr9vvMDkDC=X+NY7dYiPFWDoX+cg@mail.gmail.com>

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 <[email protected]> wrote:

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Calling oracle function from PostgreSQL
  In-Reply-To: <CAM184Ach6Jok7Y2+nP2DA5sAzhw7w7hwfsgdqVkhdLRcmn95ug@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox