Message-ID: <667ba77c9d834363c2d23dab61ab2f003f68be8c.camel@cybertec.at>
Subject: Re: Calling oracle function from PostgreSQL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Shweta Rahate <rahateshweta20@gmail.com>,
	pgsql-novice@lists.postgresql.org
Date: Mon, 02 Sep 2024 15:34:37 +0200
In-Reply-To: 
 <CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>
References: 
	<CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/667ba77c9d834363c2d23dab61ab2f003f68be8c.camel%40cybertec.at>
Precedence: bulk

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.
>=20
> The oracle function should take the input from Postgres db and returns th=
e output.
> Please suggest a way to achieve=C2=A0this.=C2=A0

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 :=3D 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 =3D 12 RETURNING outp;

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

Yours,
Laurenz Albe