从SQL Server调用Oracle存储过程传递UDTT报标量变量错误如何解决

Must declare the scalar variable "@CaseRec"报错的核心原因是：MSSQL 链接服务器的EXEC ... AT [链接服务器名]远程调用语法，仅支持传递标量类型参数，你定义的用户自定义表类型（UDTT）属于非标量复合类型，无法被直接序列化传递到Oracle端，因此被识别为未声明的非法变量。

以下方案按实现成本从低到高排序，均不需要修改Oracle侧无法调整的UpdateCase存储过程：

方案1：拆分属性后在Oracle端动态构造Object（最优） #

利用Oracle对象类型可通过[类型名](字段1,字段2...)直接构造的特性，先把UDTT中的每个字段拆分为MSSQL侧的标量变量，再在调用语句中动态构造目标Object传入存储过程即可。
举个示例，假设你的CRASH.CaseSubType包含case_id、case_desc、occur_time三个字段，修改后的代码参考如下：

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

-- 新增：拆分UDTT字段为标量变量，根据你实际的UDTT结构调整字段
DECLARE @CaseId INT, @CaseDesc NVARCHAR(100), @OccurTime DATETIME
-- 从UDTT中提取对应字段值，根据你的实际赋值逻辑调整
SELECT TOP 1 @CaseId = case_id, @CaseDesc = case_desc, @OccurTime = occur_time FROM @CaseRec

BEGIN
    -- 调用时先在Oracle侧构造CaseSubType对象再传入存储过程
    EXECUTE('BEGIN UpdateCase(CRASH.CaseSubType(?,?,?),?,?,?); END;',
             @CaseId,
             @CaseDesc,
             @OccurTime,
             @User,
             @ErrorState,
             @ErrorMessage) AT ALIS
END

方案2：序列化后在Oracle端解析构造Object #

如果对象字段过多拆分繁琐，可以将整个UDTT序列化为JSON/XML字符串（标量类型可直接通过链接服务器传递），再在Oracle侧的调用语句中解析字符串构造目标Object：

• 要求Oracle版本为12c及以上（支持原生JSON解析），低版本可改用XML格式实现
• 不需要修改Oracle侧原有存储逻辑

方案3：CLR存储过程中转（兼容性最高） #

如果以上动态构造的方式不符合你的场景，可开发MSSQL CLR存储过程实现：

1. 在C#代码中同时连接MSSQL和Oracle数据库
2. 读取MSSQL侧UDTT的数据，通过ODP.NET驱动直接构造匹配Oracle结构的自定义类型对象
3. 调用Oracle的UpdateCase存储过程传入构造好的对象
   注：该方案需要开启MSSQL的CLR执行权限，部分安全管控严格的生产环境可能不支持

内容的提问来源于stack exchange，提问作者ZPappalau