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.

EXECUTE in SQL Server 2008

Prior to SQL Server 2008 , the EXECUTE command could be used only in the local server .
Now , with SQL Server 2008 onwards , the EXECUTE command could be local as well as linked servers.

The prerequisite is that you would create a linked server ( of course ) and enable RPC OUT.

Here is an example .


C:>sqlcmd -S sql08 -W
1> use dbtest
2> go
Changed database context to 'dbtest'.

..........

.... ......

execute sp_serveroption 'ORACLE_LINKDB', 'RPC OUT' , True

I have created a linked server pointing to Oracle Database , in this case ORACLE_LINKDB.
Now , I can execute any oracle sql statements / PL SQL block ,
provided the user   have access to the objects.


1> Execute ( ' Select sysdate from dual')  AT ORACLE_LINKDB
2> go
SYSDATE
-------
2012-04-18 21:10:02.0000000
(1 rows affected)



1> Execute ( ' Select * from Control_Table where status =''running''')  AT ORACLE_LINKDB
2> go
JOBNAME     STATUS     STARTTIME   
-------     ------     ---------   
WLY_LOAD    running 2012-03-20  


In the following example , the user do not have any privileges on SCOTT schema .

1> Execute ( ' Select  *  from scott.emp')  AT ORACLE_LINKDB
2> go
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINKDB" returned message "ORA-00942: table or view does not exist".
Msg 7215, Level 17, State 1, Server SQL08, Line 1
Could not execute statement on remote server 'ORACLE_LINKDB'.


Comments welcome.