Dblink From PostgresPlus To Oracle

Hi,

Dblink_Ora_* functions are useful for getting oracle connection into PostgresPlus Advanced Server. i.e, we can retrive oracle data from postgresplus. Please find the steps how to proceed.


Step 1
======
For doing dblink_ora* setup, we need a OCI(Oralce Client Interface which is nothing but libpq in Postgresql)file. If you have oracle 11*, then no need to download this file from any where. if not, we need to donwload this file from oracle site.

Filename Required is : "libclntsh.so" in Linux
      "OCI.DLL" in Windows

In Oracle 11g, you will get this directly from $ORACLE_HOME/lib. In this example, we have created a symlink for this from "/lib64" to "$ORCLE_HOME/lib".

ln -s /home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so /lib64/libclntsh.so

Step 2
======
[root@localhost PGBAR]# chmod -R 766 /home/oracle/

Because, we are accessing this "libclntsh.so" as a enterprisedb user. Hence, we have given the complete permissions to /home/oracle.

Step 3
======
in postgresql.conf from PPAS, please add the below entries.

edb=# show oracle_home ;
 oracle_home 
-------------
 /lib64

Because, we have created a symlink for this "libclntsh.so" at "/lib64".

Step 4
======
Log into as a enterprisedb user, and set the OCI required ENVIRONMENT Variables.

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib64

Step 5
======
Restart the PostgresPlus for taking this effect.

Step 6
======
Make sure your's ORACLE listener works.

edb=# select dblink_ora_connect('oralink','localhost','orcl','scott','tiger',1521,false);
 dblink_ora_connect 
--------------------
 OK
(1 row)

Step 7
======
edb=# select * from dblink_ora_record('oralink','SELECT dbms_metadata.get_ddl(''TABLE'',''DINESH'',''SCOTT'',''9'',''ORACLE'',''DDL'') FROM DUAL') AS T(TABLEDEF TEXT);
                               tabledef                                
-----------------------------------------------------------------------
                                                                      +
   CREATE TABLE "SCOTT"."DINESH"                                      +
    (    "T" NUMBER(*,0)                                              +
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING+
   TABLESPACE "USERS"                                                 +


For more information, please refer the below doc.

/opt/PostgresPlus/9.1AS/doc/README-dblink_ora_setup.txt

--Dinesh