Invoking a function on Qracle Linked server

• Sirish

  SSC Veteran

  Points: 242

  More actions

  September 16, 2005 at 1:21 pm

  #95243

  Hi all,

  I was wondering if there is any way of executing a function which resides on ORACLE box. It is a linked server on our SQL server. Any help is appreciated.

  Thanks in advance,

  With Regards,

  Sirish

• September 19, 2005 at 8:00 am

  This was removed by the editor as SPAM

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  September 19, 2005 at 8:43 am

  #590969

  Yes, there is - it also depends upon what that function on Oracle does.

  An example (this uses the in-built function GREATEST from Oracle):

  SELECT * FROM OPENQUERY (TESTCASE, 'SELECT GREATEST(7, 11) FROM DUAL')

  Likewise, you can also use the pass-through queries for the UDFs that you might have created.  In addition, if you want to execute say some stored procedure on Oracle that creates/updates/deletes records on Oracle, a better way would be to have a dummy table on the Oracle schema, insert a record into that dummy table via the linked server and have a trigger on that dummy table that gets fired upon the insert action and calls that stored procedure which does it's work.