Is there any way to call/ execute Oracle Function via Postgres?

Question

I have a requirement to migrate Oracle DB to Postgres, in which I am able to do so. By using FDW, I am able to access Oracle data at Postgres. Now there is a requirement to call/execute a function of Oracle via Postgres.

Is there any way to do so?

I have Found this link, for the same: https://github.com/laurenz/oracle_fdw/issues/187

Can any one provide me with a sample or an example?

Regards,

You could probably do this fairly easily by wrapping the function call in a [`plpythonu`](https://www.postgresql.org/docs/current/static/plpython.html) or [`plperlu`](https://www.postgresql.org/docs/current/static/plperl.html) function. Alternatively, you could write some script which takes its arguments from stdin and invoke it via [`COPY TO PROGRAM`](https://www.postgresql.org/docs/current/static/sql-copy.html). You'll need superuser permissions to do any of these things. And unlike Laurenz's FDW approach, you lose transaction integrity on the Oracle side. But no changes required to Oracle. — Nick Barnes, Sep 22 '17 at 08:48

score 3 · Answer 1 · answered Sep 22 '17 at 08:39

Let's use this simple Oracle function for test purposes:

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

Then an Oracle table that we can use with oracle_fdw:

CREATE TABLE call_double(inp NUMBER, outp NUMBER);

INSERT INTO call_double VALUES (1, 1);

COMMIT;

Now we create a BEFORE trigger like this:

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

Now we can create and use a foreign table in PostgreSQL:

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

UPDATE call_double SET inp = 12 RETURNING outp;

┌──────┐
│ outp │
├──────┤
│   24 │
└──────┘
(1 row)

Not exactly pretty, but it does the trick.

Is there any way to call/ execute Oracle Function via Postgres?

1 Answers1