Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Call Oracle procedure from SQL Server - error 'Must declare the scalar variable' when sending UDTT

User_9ND9AOct 18 2021

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.

Comments
Post Details
Added on Oct 18 2021
1 comment
418 views