Accessing Postgresql / MySQL / MS SQL Server from Oracle.

Gone are the days , where a business has only one DBMS to maintain its operations.

Most of the times , you are dealing with multiple DBMS in your organization. 

The vendors provide some means of connecting to distributed databases . For instance ,

Oracle provides "database link"  ( via transparent gateway), MS SQL Server provides "Linked Server" mechanism to connect to heterogeneous databases and other data sources.

Here , we will look at connecting to PostgreSQL from Oracle by using transparent ODBC gateway .  This process is applicable any ODBC complaint data sources . 

Oracle does provides transparent gateways for MS SQL Server , DB2 . But those needs to be licensed.

As of today , ODBC transparent gateway is not separately licensed . Please check with your rep for the licensing details.

Here are the steps.

Step 1 :

Configure ODBC connection and a create a system DSN for PostgreSQL

Let us call it as "pgsales”

Note : This needs to be configured as "System DSN"  , not as User DSN.

Step2 :

Create a file under %ORACLE_HOME%\hs\admin in the form initpgsalesodbc.ora . The naming convention is “init” followed by  desired name . In my case , I have it as  initpgsalesodbc.ora. 

Set the HS_FDS_CONNECT_INFO to the DSN name that was configured in ODBC setup . 

I would recommend to keep the trace level to 'ON' for the initial setup . Once the setup is complete ; connectivity is established ; we can turn off the trace .  

Oracle provides sample file . We can use clone this file to suit our needs.  

Here are the contents of initpgsalesodbc.ora.

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = pgsales

HS_FDS_TRACE_LEVEL =  ON

#

# Environment variables required for the non-Oracle system

#

#set =

Step 3 :

Add a entry in listener.ora to register this data source.   The SID name must match with the init file created in the above step.

      (SID_DESC =

              (SID_NAME = pgsalesodbc)

              (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)

              (PROGRAM = dg4odbc)     

    )

Step 4: 

Add entry in tnsnames.ora . The SID  in connect_data should match SID defined in the listerner.ora and the file in hs\admin. 

pgsqllink  =

  (DESCRIPTION=

 (enable=broken)

    (ADDRESS=(PROTOCOL=tcp)(HOST=ZAHIRSER)(PORT=1521))

    (CONNECT_DATA=(SID= pgsalesodbc))

    (HS=OK)

  )

Step 5:

Restart the listener.You should see to make sure that the listener is servicing the services we created.

Service "pgsalesodbc" has 1 instance(s).

  Instance "pgsalesodbc", status UNKNOWN, has 1 handler(s) for this service...

Step 6: 

Create a database link .  

In the following example , LINK2PGSQL is our db link , 'zahir' is the user id in the postgresql database and "passwd1234" is the password . 

 

SQL> CREATE DATABASE LINK LINK2PGSQL   CONNECT TO "zahir" IDENTIFIED BY "passwd1234" USING 'pgsqllink'  ;

 Database link created.

 

To test the connectivity , issue a select statement as below . Please note , for Postgresql databases  , you will need to enclose the table/view names in quotes.

 

SQL> select * from "t"@LINK2PGSQL;

c

--

A

B

C

With this feature , we can manipulate the data stored in heterogeneous databases . 

Comments Welcome .

 

How to execute Oracle Scheduler' Job from SQL Server.

In our work place , we use multiple DBMS to manage our data . Sometimes , we may need trigger a  job in one  DBMS  from another DBMS . If the these jobs are in the same DBMS / Environment , then these steps are straightforward . 

In some cases , we use Job Schedulers such as Autosys , Robot , etc to schedule the dependent jobs across various environments.  This will involve installing clients  / agents in all the environments . For some practical reasons  ( due to budgetary and regulatory concerns )  , this may not be feasible at times .

Sometimes , it is better to trigger the job in another DBMS from the host environment  . 

Let us say , we have a SQL Server as the host DBMS and we would like to trigger a Oracle ( Scheduler's ) Job.  

Here are the steps to achieve that . 

a) Create a linked server

b) Enable RPC out

c) Use Execute command to trigger the job. 

 Let us say , we have a job called 'LOAD_SALES_MONTHLY' in Oracle Database ( configured as 'ORACLE_LINKDB' in SQL Server ) .

The syntax for executing the oracle job from SQLPLUS

 exec dbms_scheduler.run_job('LOAD_SALES_MONTHLY')

The syntax for executing the above mentioned job from SQL Server is
 

Execute ( 'call dbms_scheduler.run_job(''LOAD_SALES_MONTHLY'')') AT ORACLE_LINKDB.

Note that , I am using "CALL" command to execute Oracle's Job.
 
 For steps a) and b) , please refer to one of my previous post (
http://mfzahirdba.blogspot.com/2012/04/execute-in-sql-server-2008.html) 



Note :
You can use the same syntax to execute Oracle's stored procedure / package .

Comments welcome.