none
How to execute Oracle SP in SQL Server?

    Question

  • Hey,

     

    Question - how to execute Oracle (9i, 10g) stored procedure from Sql Server 2008?

     

    Only comments i've found about this subject is to write a wrapper function in Oracle for given stored procedure and then use openquery with "select function_name from dual" in Sql Server. Is this the only possible way?

     

    Regards,

      Martin.

    Wednesday, September 17, 2008 7:52 AM

Answers

  • That's great. Both EXEC and OPENQUERY just pass through statements (while OPENQUERY expects a result; if you just need to invoke the Oracle procedure, EXEC is enough too). Therefore the syntax passed to EXEC or OPENQUERY is mostly provider specific and can vary largely.

     

    Thanks.

    Liu An [MSFT]

    Friday, October 31, 2008 7:21 AM

All replies

  • Can you clarify your question a bit? Did you mean to execute a stored procedure in an Oracle database? If so, you can add the oracel database as a linked server to sql server 2008. Then you can execute the stored procedure with four part names. Please see details from http://msdn.microsoft.com/en-us/library/ms188279.aspx.

    • Proposed as answer by DFurrow61 Wednesday, May 09, 2012 5:47 PM
    Thursday, September 18, 2008 12:00 AM
  • Yes, i want to make a stored procedure in Sql Server 2008 that executes an Oracle stored procedure that resides in Oracle database.

     

    And yes, i've made a linked server and i can query Oracle tables just fine, i can call Oracle functions as well. But i cannot figure out how to execute Oracle stored procedures.

     

    I've found couple of links:

    http://markmal.blogspot.com/2008/01/it-is-really-pain-if-you-need-to-call.html

    http://www.objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx

     

    Those state that it's not as straight forward as just executing sp with four part names. But the thing is, their solutions aren't working for me either.

     

    I get errors like:

    OLE DB provider "OraOLEDB.Oracle" for linked server "XXX" returned message "Unspecified error".
    Msg 7323, Level 16, State 2, Line 2
    An error occurred while submitting the query text to OLE DB provider "OraOLEDB.Oracle" for linked server "XXX".

     

    or

     

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object "{CALL schema.proc_name({resultset 25, OUTPUT})}". The OLE DB provider "OraOLEDB.Oracle" for linked server "XXX" indicates that either the object has no columns or the current user does not have permissions on that object.

     

    Regards,

      Martin.

    Thursday, September 18, 2008 7:08 AM
  • Hi Martin. Did you find an answer to your question. I'm having the same problem.
    Thanks,
    Florin
    Tuesday, September 23, 2008 8:03 AM
  • Code Snippet

    Set NoCount On

    Declare @OrderID Int

    Exec ('Call DATALIB.GETNEXTORDER (?)', @OrderID OUTPUT) AT DB2400

     

     

     

     

    where DB2400 is the Oracle linked server, and OrderID an output parameter.

    Monday, October 13, 2008 3:18 PM
  • Martin,

     

    I am having the same issue, but more with calling a UDF Function that is in Oracle.  In my SQL Server I have also created a linked server to the oracle database.  I was wondering if you could let me know what settings you have on your linked server to enable calling functions?

     

    Thanks,

    Guy

     

    Friday, October 24, 2008 12:26 PM
  •  

    Hello! I have the exact same problem as you Martin: I get the "unspecified error" whenever I try to exec any function or stored procedure from Oracle on linked server.

     

    Any ideas anyone?

     

    Thanks in advance,

    Radu

    Monday, October 27, 2008 7:31 AM
  •  Radu Mocanita wrote:

     

    Hello! I have the exact same problem as you Martin: I get the "unspecified error" whenever I try to exec any function or stored procedure from Oracle on linked server.

     

    Any ideas anyone?

     

    Thanks in advance,

    Radu

     

    What command are you passing to openquery? Most likely there is something wrong with the command. You may use RowsetViewer to try and test the command with more information and more interactively.

     

    Thanks.

    Liu An [MSFT]

    Friday, October 31, 2008 2:27 AM
  • Well, I wasn't using OpenQuery. Acording to MSDN documentation, OpenQuery can't be used for executing SP. (http://msdn.microsoft.com/en-us/library/ms188427.aspx)

     

    But, I finnaly figured it out, using :

     

    declare @result varchar(255)

    exec ('BEGIN

    ?:= your_SP_Name(''arg1'', ''arg2'', etc);

    END;

    ',@result OUTPUT) at your_LinkedServerName;

    select @result;

     

    As you cand probably guess, the first question mark is a placeholder for the argument @result. That was the problem, I just couldn't guess that sintax.

     

    Thanks for answering Liu An!

     

    • Proposed as answer by MS_SRK Monday, April 23, 2012 8:13 AM
    Friday, October 31, 2008 6:54 AM
  • That's great. Both EXEC and OPENQUERY just pass through statements (while OPENQUERY expects a result; if you just need to invoke the Oracle procedure, EXEC is enough too). Therefore the syntax passed to EXEC or OPENQUERY is mostly provider specific and can vary largely.

     

    Thanks.

    Liu An [MSFT]

    Friday, October 31, 2008 7:21 AM
  • Hi all,

    I try to follow as per propose script, but having problem as shown below.

    "
    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near 'at'.
    "

    Below is my sql script.

    --> below select script to confirm link server 'Apoktest' connected or not and it can return output.
    select *
    from
    openquery (Apoktest,'select * from seiban_accum')

    --> below is propose script which try to execute but having problem. 'apok_housekeeping' oracle store procedure without any parameter input and without any problem schedule at oracle server.
    exec ('BEGIN
    apok_housekeeping;
    END;
    ') at Apoktest


    FYI, sql server 2000 and oracle database 11i.


    Could anyone assist.


    Thanks.

    Friday, August 21, 2009 8:58 AM
  • Yeah i keep gettin this as well.

    Could anyone assist ?

     

    execute ('oracle_sp_name;') at LINKED_SERVER_NAME

     

    gives me :

    Line 1: Incorrect syntax near 'AT'.

     

    i dont know where i've gone wrong.

    Monday, October 10, 2011 12:12 AM
  • I was having the same problem. 

    What helped was reading some of the above tips plus the link below.  The main thing was changing the linked server properties.  I changed RPC to True. 

    Server Objects -> Linked Sever -> ServerName (right click on server name), click on Properties, click on Server Options, Change RPC and RPC Out to True, and click OK.  Be sure to refresh by right clicking on the ServerName, Click on Refresh.  Not sure if I should do both but I did and it works.

    I got this from: http://www.bidn.com/blogs/BradSchacht/ssis/617/server-%E2%80%98servername%E2%80%99-is-not-configured-for-rpc

     

    From MS SQL Server...  

             Execute ('Call OracleStoreProcedure()') at LINKED_SERVER_NAME 

             (remember to add parenthesis'  () after store procedure name if you are not passing any variables.)  I didn't add a semi colon ";" in my call.

     

     

    • Proposed as answer by GreenMtnSun2 Wednesday, May 16, 2012 4:53 PM
    Wednesday, November 23, 2011 7:22 PM