Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sk3UY-007BPm-NP for pgsql-novice@arkaria.postgresql.org; Fri, 30 Aug 2024 15:24:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sk3UV-0069A5-FR for pgsql-novice@arkaria.postgresql.org; Fri, 30 Aug 2024 15:24:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sk3UU-00699x-Td for pgsql-novice@lists.postgresql.org; Fri, 30 Aug 2024 15:24:47 +0000 Received: from mail-ed1-x52d.google.com ([2a00:1450:4864:20::52d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sk3US-002CFA-HR for pgsql-novice@lists.postgresql.org; Fri, 30 Aug 2024 15:24:46 +0000 Received: by mail-ed1-x52d.google.com with SMTP id 4fb4d7f45d1cf-5becfd14353so1927622a12.1 for ; Fri, 30 Aug 2024 08:24:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725031483; x=1725636283; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=w9HvKgC2rQsitKV7Mt2guY1OtR3/OtiDyMpFRTzDvxI=; b=E5U/AUjO9xB21ACfgZyidXHh0/F+qiW+pYSkqrFIWgssUS3aMH9kB9dfUcmckDSqGj YV469ipRLH+mKN5uZwgW9edZHiA3vJRoxxe3WqxYOjhsWv3PjjThu/DfrBREVvigSngt f3swflOWmSy3+wqRSL7feYuEGT+T6ND5iGmixFz6cUJPJ37cwaFX/obuWDbBJ5qG5Eb1 vVnQy0+zuw13HtUHfgbVBej22dqB44kvVBy4yT7+bEDsR88SKt4VzBaz1gildp9fkZHr uGEUy8pU5E7M5NlhWgB2m82iWffuNlq+t4iwgvQsTj7DPdDehsHRxCn3veZYt1BJm3Cj uqaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725031483; x=1725636283; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=w9HvKgC2rQsitKV7Mt2guY1OtR3/OtiDyMpFRTzDvxI=; b=BCXjE4xa86qE29IR6gUo/DGMz7+wV5zLUnv7K/n/KV995wyi38qCYvSR3M0YbyCb97 BWGH/LF8QoOb8ikohRBA/UpbIyMUdCS12nibc9kGWtwQX27QxHpHryBeNtpTkzDGy3cJ 4wPV02XxEhG3MkneAYapeAXVoo+gwDIxvcHJtYiS3VF4ouTMtnQzHTvR964eUcwt9bv2 DCckmjs9ObPPtYxsv7UfkIKtfnV8/3RJsufj0yfB5JoApbJN3ZH63Af8uocg+8VTL7wk 85KE0Wb0clnMfuerZqw7z018avbAWK5QVclEM/ePNnWuya+DXPgRStDUkblWmLv3jCFU Cpmw== X-Forwarded-Encrypted: i=1; AJvYcCWGfnANaoyS5T2gdB6g0DvjHgYtxYYEIcgArV9niDwFTvBXowSG5S7Obm5XxfN7r+XzZeIpto79wFP/VOs=@lists.postgresql.org X-Gm-Message-State: AOJu0YzC2SUU9s8u8HUKBomRJOaxeBZW136hl75Mt9j4KsuAWQKuIVCY VwIc0wN19znsN28sWDlYx5q6bWEdih4Q2O+80AFYx8SW1RsDJWGqRR2ierxv4ggBvz1bX3L2r8D 9wq17h//6rwEFGp2Vam2UlMDK3NOiXlvN75E= X-Google-Smtp-Source: AGHT+IFjncUp0po0aBUEq80gZCdWRrJbzeXyAu6nvMJnl06I/UdTrlbtS16zrmQ2GiqxWep6tGXsHnSrofgQtSu1QS8= X-Received: by 2002:a05:6402:4583:b0:5c0:8af6:7451 with SMTP id 4fb4d7f45d1cf-5c21ed52c63mr5376894a12.21.1725031482587; Fri, 30 Aug 2024 08:24:42 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Umair Shahid Date: Fri, 30 Aug 2024 20:24:31 +0500 Message-ID: Subject: Re: Calling oracle function from PostgreSQL To: Muhammad Ikram Cc: Shweta Rahate , pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000bf00750620e830ae" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bf00750620e830ae Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=99s 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--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=E2=80=99s an example of how you might do this: SELECT oracle_function(column1, column2)FROM oracle_tableWHERE some_condition; Documentation Links - oracle_fdw GitHub Repository - PostgreSQL Foreign Data Wrapper Documentation 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=E2=80=AFPM Muhammad Ikram = wrote: > Hi > > Please explore oracle_fdw. > > Regards, > Muhammad Ikram > > > On Fri, Aug 30, 2024 at 12:09=E2=80=AFPM Shweta Rahate > wrote: > >> Hi All, >> >> In my application there is a requirement to call the oracle function fro= m >> 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 > > --000000000000bf00750620e830ae Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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=C2=A0oracle_fdw. Here=E2=80=99s = a step-by-step guide to achieve this:


Step 1: Install oracle_fdw

  • Install Oracle Client Libra= ries: Ensure that the Oracle client libraries are installed on your = PostgreSQL server. You can download these from the Oracle website.

    • =

  • Install oracle_fdw: You can install=C2=A0oracle_fdw=C2=A0u= sing your package manager or by compiling it from source.

    For example, on Debian-based systems:

    sudo apt-get install postg=
resql-<version>-oracle-fdw

    Or, = to compile from source:

    git clone https://github.com/laurenz/oracle_fdw.gitcd oracle_fdwmak=
esudo make install

    • Step 2: Configure oracle_fdw

    1. Create the Extension: In y= our PostgreSQL database, create the=C2=A0oracle_fdw=C2=A0extension.

      CREATE EXTENSION orac=
le_fdw;

    2. Create a Foreign Server: Define a foreign server that connects to your Oracle database.

      CREATE SE= RVER oracle_serverFOREIGN DATA WRAPPER oracle_fdwOPTIONS (dbserver '//o= racle_host:1521/oracle_service_name');

    3. Create a User Mapping: Map a PostgreSQL user to an Oracle us= er.

      CREATE USER MAPPING FOR postgresSERVER oracle_serverOPTIONS (user 'or=
acle_user', password 'oracle_password');
    =

    Step 3: Creat= e 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 dataty=
pe,    ...)SERVER oracle_serverOPTIONS (schema 'oracle_schema', tab=
le 'oracle_table');

    Step 4: Call Oracle Function
    1. Call the Oracle Function: You can now call = the Oracle function using the foreign table. Here=E2=80=99s an example of h= ow you might do this:
      SELECT oracle_function(column1, column2)FROM oracle_tableWH=
ERE some_condition;

    Documentation Links

    Next Steps

    =
    1. Test the Connection: Ensure that the connecti= on between PostgreSQL and Oracle is working correctly by querying the forei= gn table.
    2. Handle Data Types: Pay attention to data type compatibili= ty between PostgreSQL and Oracle.
    3. Optimize Performance: Consider pe= rformance implications and optimize queries as needed.
    IMPORTANT: This is the output=C2=A0fro= m Elethena, the AI chatbot specializing in PostgreSQL at=C2=A0https://stormatics.tech/.=C2=A0

    =
    Thanks!=C2=A0

    - Umair

    On Fri, Aug = 30, 2024 at 12:18=E2=80=AFPM Muhammad Ikram <mmikram@gmail.com> wrote:
    Hi

    Please ex= plore oracle_fdw.=C2=A0

    Regards,
    Muhamma= d Ikram


    On Fri, Aug 30, 2024 at 12:09=E2=80=AFPM Shwe= ta Rahate <rahateshweta20@gmail.com> wrote:
    Hi All,

    In my application there is a requirement to call the o= racle function from PostgreSQL db.

    The oracle function should take the input from Postgres db and r= eturns the output.
    Please suggest a way to achieve= =C2=A0this.=C2=A0



    Regards,
    Shweta


    --
    Muhammad Ikram

    --000000000000bf00750620e830ae--