如何捕获链接服务器返回的Oracle ORA-20010特定错误信息

问题背景 #

在SQL Server中通过链接服务器调用Oracle存储过程，代码如下：

BEGIN TRY
    --execute oracle script     
    EXECUTE ('BEGIN DB.SP_SPName(?,?,?,?,?,?,?); end;', @p_STORE_ID,
                @p_CREATE_DATE, @p_CREATE_USER, @p_From_DATE, @p_To_DATE, @p_INCLUDE_SISTER, @p_Session) AT LinkServerName;
                
END TRY
BEGIN CATCH
    -- if error, abort execution 
    print('This is the Error : ' +ERROR_MESSAGE())
    select 4 as Result, ERROR_MESSAGE() as ErrorMsg
    return
END CATCH

执行后返回的错误信息包含Oracle的具体错误，但SQL Server的ERROR_MESSAGE()只返回包装后的提示，无法直接获取ORA-20010: Invalid store id这类原始错误：

OLE DB provider "OraOLEDB.Oracle" for linked server "LinkServerName" returned message "ORA-20010: Invalid store id
ORA-06512: at "DB.SP_SPName", line 104
ORA-06512: at line 1".
This is the Error : Could not execute statement on remote server 'LinkServerName'.

尝试过SET XACT_ABORT ON和在Oracle端嵌入TRY/CATCH块，但效果不佳，想知道当前可行的解决方案。

可行解决方案 #

1. 解析SQL Server错误信息文本提取ORA错误 #

SQL Server返回的错误信息里包含完整的Oracle错误内容，通过字符串函数可以定位并提取目标错误行：

BEGIN TRY
    EXECUTE ('BEGIN DB.SP_SPName(?,?,?,?,?,?,?); end;', @p_STORE_ID,
                @p_CREATE_DATE, @p_CREATE_USER, @p_From_DATE, @p_To_DATE, @p_INCLUDE_SISTER, @p_Session) AT LinkServerName;
END TRY
BEGIN CATCH
    DECLARE @FullErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    DECLARE @OracleError NVARCHAR(MAX);
    
    -- 定位ORA-开头的错误，提取到换行符前的内容
    SET @OracleError = SUBSTRING(
        @FullErrorMsg,
        CHARINDEX('ORA-', @FullErrorMsg),
        CHARINDEX(CHAR(10), @FullErrorMsg + CHAR(10), CHARINDEX('ORA-', @FullErrorMsg)) - CHARINDEX('ORA-', @FullErrorMsg)
    );

    -- 输出处理后的错误
    PRINT 'Oracle错误: ' + @OracleError;
    SELECT 4 AS Result, @OracleError AS ErrorMsg;
    RETURN;
END CATCH

说明：只要Oracle返回的错误格式稳定（ORA-开头，换行分隔），这个方法就能准确提取到目标错误。

2. 修改Oracle存储过程，用输出参数返回错误 #

这是最可靠的方案，避免抛出异常，改为通过输出参数把错误代码和信息传递给SQL Server：

第一步：修改Oracle存储过程

CREATE OR REPLACE PROCEDURE DB.SP_SPName(
    p_STORE_ID IN NUMBER, -- 根据实际类型调整
    p_CREATE_DATE IN DATE,
    p_CREATE_USER IN VARCHAR2,
    p_From_DATE IN DATE,
    p_To_DATE IN DATE,
    p_INCLUDE_SISTER IN NUMBER,
    p_Session IN VARCHAR2,
    p_Error_Code OUT NUMBER, -- 新增错误代码输出参数
    p_Error_Msg OUT VARCHAR2 -- 新增错误信息输出参数
) AS
BEGIN
    -- 初始化错误状态
    p_Error_Code := 0;
    p_Error_Msg := '执行成功';

    -- 原有业务逻辑，比如校验store id
    IF NOT EXISTS(SELECT 1 FROM STORES WHERE ID = p_STORE_ID) THEN
        p_Error_Code := 20010;
        p_Error_Msg := 'Invalid store id';
        RETURN; -- 提前返回，不抛出异常
    END IF;

    -- 其他业务逻辑...

EXCEPTION
    WHEN OTHERS THEN
        -- 捕获未预期的错误
        p_Error_Code := SQLCODE;
        p_Error_Msg := SQLERRM;
END;

第二步：SQL Server端调用并接收错误参数

DECLARE @p_Error_Code INT, @p_Error_Msg NVARCHAR(2000);

BEGIN TRY
    EXECUTE (
        'BEGIN DB.SP_SPName(:1,:2,:3,:4,:5,:6,:7,:8,:9); end;', 
        @p_STORE_ID, @p_CREATE_DATE, @p_CREATE_USER, @p_From_DATE, @p_To_DATE, 
        @p_INCLUDE_SISTER, @p_Session,
        @p_Error_Code OUTPUT, @p_Error_Msg OUTPUT
    ) AT LinkServerName;

    -- 判断是否有错误
    IF @p_Error_Code <> 0 THEN
        PRINT 'Oracle返回错误: ORA-' + CAST(@p_Error_Code AS NVARCHAR) + ': ' + @p_Error_Msg;
        SELECT 4 AS Result, 'ORA-' + CAST(@p_Error_Code AS NVARCHAR) + ': ' + @p_Error_Msg AS ErrorMsg;
        RETURN;
    END IF;
END TRY
BEGIN CATCH
    -- 处理链接服务器本身的错误（比如网络问题）
    SELECT 4 AS Result, ERROR_MESSAGE() AS ErrorMsg;
END CATCH

说明：这种方法完全不依赖错误文本解析，直接通过参数获取错误信息，稳定性最高。

3. 使用OPENQUERY替代EXECUTE AT #

OPENQUERY返回的错误信息有时更贴近原始Oracle错误，同样可以结合字符串解析：

DECLARE @SQL NVARCHAR(MAX);
-- 拼接参数时注意转义单引号，避免SQL注入
SET @SQL = 'BEGIN DB.SP_SPName(' + 
    QUOTENAME(@p_STORE_ID, '''') + ',' +
    QUOTENAME(CONVERT(VARCHAR, @p_CREATE_DATE, 120), '''') + ',' +
    QUOTENAME(@p_CREATE_USER, '''') + ',' +
    QUOTENAME(CONVERT(VARCHAR, @p_From_DATE, 120), '''') + ',' +
    QUOTENAME(CONVERT(VARCHAR, @p_To_DATE, 120), '''') + ',' +
    QUOTENAME(@p_INCLUDE_SISTER, '''') + ',' +
    QUOTENAME(@p_Session, '''') + '); end;';

BEGIN TRY
    SELECT * FROM OPENQUERY(LinkServerName, @SQL);
END TRY
BEGIN CATCH
    DECLARE @FullErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    DECLARE @OracleError NVARCHAR(MAX) = SUBSTRING(
        @FullErrorMsg,
        CHARINDEX('ORA-', @FullErrorMsg),
        CHARINDEX(CHAR(10), @FullErrorMsg + CHAR(10), CHARINDEX('ORA-', @FullErrorMsg)) - CHARINDEX('ORA-', @FullErrorMsg)
    );
    SELECT 4 AS Result, @OracleError AS ErrorMsg;
END CATCH

注意：参数拼接时必须处理单引号转义，比如用REPLACE(@p_CREATE_USER, '''', '''''')代替QUOTENAME，避免SQL注入风险。

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