使用sys_context的代码在两数据库实例中结果不同的原因排查

我有一段使用sys_context的PL/SQL代码，在两个数据完全相同的数据库实例中运行，返回了不同结果。

示例代码 #

alter session set current_schema = <schemaX>;

DECLARE

   v_schema_name     varchar2(128);
   v_constraint_name varchar2(128);
   
BEGIN

    dbms_output.put_line('---------------------------------------------------------');
    dbms_output.put_line('------------ Restricting using sys_context --------------');
    dbms_output.put_line('---------------------------------------------------------');


    BEGIN
        dbms_output.put_line(sys_context('USERENV','CURRENT_SCHEMA'));
           SELECT constraint_name
            INTO v_constraint_name
            FROM all_constraints
            WHERE table_name        = <table name>
            AND owner               = sys_context('USERENV','CURRENT_SCHEMA')
            ;
            
            dbms_output.put_line(v_constraint_name);
    EXCEPTION
        when others then
            dbms_output.put_line('Error raised : '||sqlerrm);
     
    END;
    
END;

运行结果 #

实例1结果：

---------------------------------------------------------
------------ Restricting using sys_context --------------
---------------------------------------------------------
AE9_COMPANY_TRN
Error raised : ORA-01403: no data found

实例2结果（预期结果）：

---------------------------------------------------------
------------ Restricting using sys_context --------------
---------------------------------------------------------
AE9_COMPANY_TRN
Error raised : ORA-01422: exact fetch returns more than requested number of rows

请问为什么实例1会返回不同的结果？

虽然两个实例数据一致，但出现这种差异通常和权限、大小写匹配、会话环境、数据库参数这几个核心点有关，逐个排查即可定位：

1. 当前用户对目标对象的访问权限不同 #

ALL_CONSTRAINTS视图仅展示当前用户有权限访问的对象约束。如果实例1中运行代码的用户没有被授予访问<schemaX>下目标表的权限，查询就会返回空结果，触发ORA-01403；而实例2中用户拥有对应权限，能查到多条约束，触发ORA-01422。

可以在两个实例中分别执行以下语句验证：

SELECT COUNT(*) 
FROM all_constraints 
WHERE table_name = '<table name>' 
AND owner = 'AE9_COMPANY_TRN';

若实例1返回0，实例2返回大于1的数值，即可确认是权限问题。

2. 表名或用户名的大小写匹配问题 #

Oracle默认大小写不敏感，但如果创建对象时用双引号指定了大小写，查询时必须严格匹配。检查两个实例中：

• 目标表的实际名称（是否带大小写格式）
• AE9_COMPANY_TRN这个schema的名称是否存在大小写差异

在实例1中执行以下语句查看表的真实名称：

SELECT table_name 
FROM dba_tables 
WHERE owner = 'AE9_COMPANY_TRN' 
AND UPPER(table_name) = UPPER('<table name>');

如果返回的表名是带引号的大小写格式（比如"MyTable"），原查询中table_name = <table name>的写法就无法匹配，导致无数据返回。

3. CURRENT_SCHEMA的会话生效异常 #

虽然执行了alter session set current_schema = <schemaX>，但某些环境下（比如工具执行时的会话隔离），这条语句可能没真正作用到后续的PL/SQL块中。可以在PL/SQL块里新增验证逻辑：

SELECT sys_context('USERENV','CURRENT_SCHEMA') INTO v_schema_name FROM dual;
dbms_output.put_line('Verified current schema: ' || v_schema_name);

如果实例1中输出的v_schema_name不是AE9_COMPANY_TRN，说明会话设置未生效，导致owner条件不匹配。

4. 数据库参数差异 #

检查两个实例的NLS_COMP和NLS_SORT参数，这两个参数会影响字符串比较规则。如果实例1的参数设置为区分大小写，而实例2不区分，也会导致查询条件不匹配。

执行以下语句查看参数：

SELECT parameter, value FROM v$nls_parameters WHERE parameter IN ('NLS_COMP', 'NLS_SORT');

若两个实例参数值不同，调整为一致后再测试即可。

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