I have a Procedure called UpdateCase in an oracle database that I can not change. One of the parameters is an Object type. My goal is to call that stored procedure from MSSQL. We have created the linked server and a User Defined Table Type that mirrors the Object type in the oracle database. Here is the code to call the Procedure.
ALTER PROCEDURE [dbo].[spDMVUpdateCase]
@CaseNumber INT = NULL,
@User VARCHAR(50) = NULL
AS
DECLARE @ErrorMessage NVARCHAR(200)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @CaseRec AS CRASH.CaseSubType
BEGIN
EXECUTE('Call UpdateCase(?,?,?,?)',
@CaseRec,
@User,
@ErrorState,
@ErrorMessage) AT ALIS
END
When I try to execute this code I get 'Must declare the scalar variable "@CaseRec".'
Is there any way to send a non-scalar variable to an oracle procedure from MSSQL?
Hope this is the right category to post in if not please point in the right direction.