3

how do we execute an Oracle Stored Procedure from SQL Server 2005? As part of a SQL Server scripts we need to execute an Oracle 10g Stored Procedure and download data to SQL Server 2005

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Yves
  • 12,059
  • 15
  • 53
  • 57

1 Answers1

2

I believe you can still use SELECT * FROM OPENQUERY(ORA_SERVER, 'EXECUTE sp;') to acomplish this.

John Gietzen
  • 48,783
  • 32
  • 145
  • 190
  • John one quick question. I run the above scripts and it prompt me this error message: Cannot process the object "EXECUTE xxxx(SYSDATE - 40, SYSDATE );". The OLE DB provider "MSDAORA" for linked server "lumd" indicates that either the object has no columns or the current user does not have permissions on that object. How do I link to Oracle Server from SQL Server? – Yves Oct 30 '09 at 20:03
  • Can you post the whole query? I would be inclined to believe that the error message is telling the truth. ;) – John Gietzen Oct 30 '09 at 20:07
  • Also, what happens when you execute the query using Oracle SQL Developer? – John Gietzen Oct 30 '09 at 20:08
  • Here: SELECT * FROM OPENQUERY(lumd, 'execute REP.T2t.collect_all(SYSDATE - 40, SYSDATE );') – Yves Oct 30 '09 at 20:19
  • Can you confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I am having problems executing stored procedures with arguments using the OPENQUERY Function. – Yves Oct 30 '09 at 20:34
  • 1
    I'm not an oracle guru, but if you can `select 1 "Result" from dual` on success and `select 0 "Result" from dual` on failure, you can just check that. – John Gietzen Oct 30 '09 at 21:22