从SQL Server调用Oracle存储过程如何传递REF CURSOR类型参数

• 参数数量不匹配：目标存储过程SP_DETAILS共包含4个参数，你调用时仅传递了后3个输入参数，缺失了第一个IN OUT类型的SYS_REFCURSOR参数
• 调用语法不符合Oracle匿名块的输出参数接收规则，默认EXEC AT写法无法直接处理Oracle返回的REF CURSOR类型结果

方案1：使用DBMS_SQL直接返回游标结果（Oracle 12c及以上版本适用，无需预先定义返回列结构） #

直接在EXEC AT的匿名块中声明游标变量传入存储过程，调用DBMS_SQL.RETURN_RESULT自动将游标结果返回给SQL Server：

EXECUTE ('
DECLARE
  v_ref_cur SYS_REFCURSOR;
BEGIN
  -- 参数顺序严格匹配存储过程定义：1.游标 2.P_QUALIFIER 3.P_PORTFOLIO 4.P_DATE
  SP_DETAILS(v_ref_cur, NULL, NULL, NULL);
  DBMS_SQL.RETURN_RESULT(v_ref_cur);
END;
') AT Operadora;

如果需要给输入参数传非NULL值，直接替换对应位置的NULL即可，例如传入P_QUALIFIER为'A'、P_DATE为指定时间戳：

EXECUTE ('
DECLARE
  v_ref_cur SYS_REFCURSOR;
BEGIN
  SP_DETAILS(v_ref_cur, ''A'', ''PORTFOLIO_01'', TO_TIMESTAMP(''2024-01-01'', ''YYYY-MM-DD''));
  DBMS_SQL.RETURN_RESULT(v_ref_cur);
END;
') AT Operadora;

方案2：使用OPENQUERY接收结果（兼容所有Oracle版本，可直接插入本地表） #

如果需要将返回结果存入SQL Server本地表，或者使用的Oracle版本低于12c，可以用OPENQUERY读取返回结果：

-- 如需存入本地表，取消下行注释即可
-- SELECT * INTO 本地结果表名
SELECT * FROM OPENQUERY(Operadora, '
DECLARE
  v_ref_cur SYS_REFCURSOR;
BEGIN
  SP_DETAILS(v_ref_cur, NULL, NULL, NULL);
  DBMS_SQL.RETURN_RESULT(v_ref_cur);
END;
')

• 链接服务器必须使用Oracle官方提供的Oracle Provider for OLE DB驱动，微软自带的MSDAORA驱动已停止维护，对REF CURSOR支持存在缺陷
• 链接服务器属性中需开启*允许进程内（Allow inprocess）*选项，否则会出现游标返回异常
• 传参时需严格匹配参数顺序和数据类型，时间戳类型需用Oracle的TO_TIMESTAMP函数做显式转换

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