Horton

Thread

  1. Calling oracle function from PostgreSQL

    Shweta Rahate <rahateshweta20@gmail.com> — 2024-08-30T07:08:45Z

    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

  2. Re: Calling oracle function from PostgreSQL

    Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-08-30T07:18:01Z

    Hi Shweta

There is no such feature either by Oracle  fdw or db links to call Oracle
functions in Postgres as per my knowledge.

Thanks & Regards
Kashif Zeeshan

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
>

  3. Re: Calling oracle function from PostgreSQL

    Muhammad Ikram <mmikram@gmail.com> — 2024-08-30T07:18:32Z

    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

  4. Re: Calling oracle function from PostgreSQL

    Umair Shahid <umair.shahid@gmail.com> — 2024-08-30T15:24:31Z

    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
>
>

  5. Re: Calling oracle function from PostgreSQL

    Greg Sabino Mullane <htamfids@gmail.com> — 2024-08-30T15:54:00Z

    On Fri, Aug 30, 2024 at 11:24 AM Umair Shahid <umair.shahid@gmail.com>
wrote:

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

Please don't do this. The mailing lists (and planet postgres entries)
should be human-answered. On a related note, the answer is not quite
correct, and the code samples will not work as written due to missing
spaces.

Cheers,
Greg

  6. Re: Calling oracle function from PostgreSQL

    Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-08-30T16:48:27Z

    On Fri, Aug 30, 2024 at 12:18 PM Muhammad Ikram <mmikram@gmail.com> wrote:

> Hi
>
> Please explore oracle_fdw.
>
Oracle FDW does not support calling Oracle Functions in Postgres, their
main purpose is accessing table data.

>
> 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
>
>

  7. Re: Calling oracle function from PostgreSQL

    TIM CHILD <tim.child@comcast.net> — 2024-08-30T23:12:10Z

    Shweta,
 
Another approach is to write  a Postgres UDF (User Defined Function) written in JavaScript using  the PLV8 add-on. https://github.com/plv8/plv8   https://github.com/plv8/plv8
You can then write a simple  JavaScript function that connects to the Oracle database and calls your  Oracle function return that result to your Postgres UDF.
 
Most Cloud providers support the installation of PLV8. You'll need ensure firewall and network rules allow outbound and inbound connections between the PostgreSQL server and the Oracle server.  For additional security you should create a special Oracle user with very restricted privileges, to only execute that function, and use that user in the JavaScript  function when you connect to Oracle. You'll also need to take care to not include the Oracle user name and password in your JavaScript code.. You'll have to master the  esbuild https://esbuild.github.io/ JavaScript bundler to include the Oracle JavaScript packages and dependencies.
 
This approach won't be efficient as you be establishing a new Oracle connection every time you call it from PostgreSQL. 
 
-Tim
 
 
 
 
 
 

> On 08/30/2024 12:08 AM PDT 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
>

  8. Re: Calling oracle function from PostgreSQL

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-09-02T11:30:00Z

    On Fri, 2024-08-30 at 11:54 -0400, Greg Sabino Mullane wrote:
> On Fri, Aug 30, 2024 at 11:24 AM Umair Shahid <umair.shahid@gmail.com> wrote:
> > IMPORTANT: This is the output from Elethena, the AI chatbot specializing in PostgreSQL at https://stormatics.tech/.
> 
> Please don't do this. The mailing lists (and planet postgres entries) should be human-answered.
> On a related note, the answer is not quite correct, and the code samples will not work as written
> due to missing spaces.

Yes, that answer is pretty much utter nonsense.

Yours,
Laurenz Albe

  9. Re: Calling oracle function from PostgreSQL

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-09-02T13:34:37Z

    On Fri, 2024-08-30 at 12:38 +0530, Shweta Rahate wrote:
> 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. 

There is no direct way to do this via oracle_fdw.

There are, however, a couple of hacks to do that; see the following example:

The Oracle function:

  CREATE OR REPLACE FUNCTION double(n NUMBER) RETURN NUMBER AS
  BEGIN
     RETURN n * 2;
  END;
  /

Then I can define an Oracle table with a single row and a trigger on it:

  CREATE TABLE call_double(inp NUMBER, outp NUMBER);

  INSERT INTO call_double VALUES (1, 1);

  COMMIT;

  CREATE TRIGGER double_trig BEFORE UPDATE ON call_double FOR EACH ROW
  BEGIN
     :NEW.outp := double(:NEW.inp);
  END;
  /

Now I can define a foreign table as follows:

  CREATE FOREIGN TABLE call_double(
     inp numeric OPTIONS (key 'true'),
     outp numeric)
  SERVER oracle OPTIONS (table 'CALL_DOUBLE');

And then the following UPDATE calls the function and returns the result:

  UPDATE call_double SET inp = 12 RETURNING outp;

That's ugly, but perhaps it is good enough as a workaround.

Yours,
Laurenz Albe

  10. Re: Calling oracle function from PostgreSQL

    Adam Brusselback <adambrusselback@gmail.com> — 2024-09-02T14:50:33Z

    Re: That table hack
Oh man is scary as can be (to me). I think I would go with another option
(maybe outside of the database) entirely rather than introducing that into
my codebase.

Onto the general need:
I've definitely had the need for foreign function calls between my (both
Postgres) databases (e.g. dwh server calling a function to get some info
from oltp server), and I had to resort to dblink for that. Would have been
very nice if the FDW interface had support for functions / stored
procedures as first class citizens as long as the fdw implementation (and
other endpoint) support functions / stored procedures.

Once could dream.

-Adam

On Mon, Sep 2, 2024 at 9:34 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> On Fri, 2024-08-30 at 12:38 +0530, Shweta Rahate wrote:
> > 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.
>
> There is no direct way to do this via oracle_fdw.
>
> There are, however, a couple of hacks to do that; see the following
> example:
>
> The Oracle function:
>
>   CREATE OR REPLACE FUNCTION double(n NUMBER) RETURN NUMBER AS
>   BEGIN
>      RETURN n * 2;
>   END;
>   /
>
> Then I can define an Oracle table with a single row and a trigger on it:
>
>   CREATE TABLE call_double(inp NUMBER, outp NUMBER);
>
>   INSERT INTO call_double VALUES (1, 1);
>
>   COMMIT;
>
>   CREATE TRIGGER double_trig BEFORE UPDATE ON call_double FOR EACH ROW
>   BEGIN
>      :NEW.outp := double(:NEW.inp);
>   END;
>   /
>
> Now I can define a foreign table as follows:
>
>   CREATE FOREIGN TABLE call_double(
>      inp numeric OPTIONS (key 'true'),
>      outp numeric)
>   SERVER oracle OPTIONS (table 'CALL_DOUBLE');
>
> And then the following UPDATE calls the function and returns the result:
>
>   UPDATE call_double SET inp = 12 RETURNING outp;
>
> That's ugly, but perhaps it is good enough as a workaround.
>
> Yours,
> Laurenz Albe
>
>
>