Re: Calling oracle function from PostgreSQL

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