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.