Calling an Oracle stored procedure in SQL Server

Tags:
ANSI norm
ANSI SQL
Oracle stored procedures
SQL Server stored procedures
I need to call an Oracle stored procedure from SQL Server. The stored procedure is written in ANSI SQL. ORCL is my linked server. I am calling ORCL.USER.PROCEDURE P1, P2, P3 and I am getting this error: Could not execute procedure 'PROCEDURE_NAME' on remote server 'ORCL'. [OLE/DB provider returned message: One or more errors occurred during processing of command.] [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

This is the syntax that I would probably try from the start:

SELECT * FROM OPENQUERY(<linked server name>, '{CALL }')

What I’ve seen (I have not tried this myself) is that you can also use OPENQUERY to call an Oracle function which can then call the stored procedure, or you can turn the stored procedure into a function. Your connection from SQL Server would then look something like this:

SELECT * FROM OPENQUERY(<linked server name>, 'SELECT FROM dual')——————————-

From the OPENQUERY official documentation:

OPENQUERY does not accept variables for its arguments.

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name. For example:

EXEC SeattleSales.master.dbo.xp_msver

Any
call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is
evaluated separately and independently from any call to these functions
used as the target of the update, even if identical arguments are
supplied to the two calls.
In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other.


Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Keep in mind, that as mentioned in the answer above, OPENQUERY doesn't accept variables for its arguments.
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following