Home » RDBMS Server » Networking and Gateways » Calling sql/server from PL/SQL
Calling sql/server from PL/SQL [message #645684] Fri, 11 December 2015 00:25 Go to next message
nkelleher
Messages: 8
Registered: November 2006
Location: South Africa
Junior Member
HI All

First time for me here.
I have been given access to a sql server procedure and need to call it from my Oracle database in pl/sql
I think the methodd I need to use is to define an external procedure.
Have found lots of documentation, this one I think the best

http://docstore.mik.ua/orelly/oracle/prog2/ch21_02.htm

My problem is that I am not familiar with libraries and do not now what should be defined in the library.

Information provided is below

Server IP Address : 111.111.11.111
User name : xxxxx
Password : xxxxx
Database Name: AAD
Procedure name : usp_get_order_status
Input Parameter Name : @in_vchWarehouseId ,@in_vchOrderNumber
Output Parameter Name : @out_vchMessage
Output Message : RELEASED, NOT RELEASED

Sample Code Format to test from SQL Server
USE [AAD]
GO

DECLARE @RC int
DECLARE @in_vchWarehouseId nvarchar(20)
DECLARE @in_vchOrderNumber nvarchar(50)
DECLARE @out_vchMessage nvarchar(500)

-- TODO: Set parameter values here.
SET @in_vchWarehouseId ='01'
SET @in_vchOrderNumber = '69831073'


EXECUTE @RC = [dbo].[usp_get_order_status]
@in_vchWarehouseId
,@in_vchOrderNumber
,@out_vchMessage OUTPUT

GO

Any assistence would help hugely

Nicki


Re: Calling sql/server from PL/SQL [message #645687 is a reply to message #645684] Fri, 11 December 2015 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What about heterogeneous gateway and a database link?

[Updated on: Fri, 11 December 2015 01:28]

Report message to a moderator

Re: Calling sql/server from PL/SQL [message #645689 is a reply to message #645687] Fri, 11 December 2015 03:47 Go to previous messageGo to next message
nkelleher
Messages: 8
Registered: November 2006
Location: South Africa
Junior Member
Thanks. Will look into that.
Someone else recommended a JAVA class built into a pl/sql call.
Re: Calling sql/server from PL/SQL [message #645693 is a reply to message #645689] Fri, 11 December 2015 09:42 Go to previous message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, but you have to anyway use a heterogeneous database link or an external call to reach the SQL Server function unless you can connect to the SQL server within the Java procedure which seems to me highly risky and anyway not transnational (which is also the case with external procedures).

Previous Topic: ORA-01017 OS Authentication Fails
Next Topic: Junifer Router basic commands
Goto Forum:
  


Current Time: Thu Aug 18 12:09:29 CDT 2022