Oracle中VARCHAR2列与参数比较报错ORA-00904的解决求助

ORA-00904错误核心原因是变量替换后缺少单引号，Oracle将userABC误识别为列名而非字符串值：

• 执行DEFINE USERNAME = 'userABC';时，Oracle会自动剥离外层单引号，变量实际值为userABC（不带引号）。
• 替换后LOWER(&USERNAME)变为LOWER(userABC)，Oracle认为userABC是表中列名，但你的表不存在该列，因此报错。

另外时间戳部分存在冗余转换：你已用TO_TIMESTAMP_TZ定义变量，无需在WHERE子句中重复调用该函数，否则会引发类型不匹配问题。

DEFINE USERNAME = 'userABC';
DEFINE LOWER_TIMESTAMP = TO_TIMESTAMP_TZ('15-MAR-23 02.15.27.603000000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR');
DEFINE UPPER_TIMESTAMP = TO_TIMESTAMP_TZ('15-MAR-23 02.25.27.603000000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR');
--SELECT &LOWER_TIMESTAMP AS LOWER_TIMESTAMP, &UPPER_TIMESTAMP AS UPPER_TIMESTAMP FROM DUAL;

SELECT
    REQUEST_NUMBER,
    USERNAME,
    USERACTION,
    MESSAGE_TYPE,
    COMPONENT_NAME,
    TIMESTAMP,
    MODULE_NAME,
    PROCESS_NAME,
    VERSION,
    TASK,
    RESPONSE_CODE,
    RESPONSE_MESSAGE,
    AUDIT_MESSAGE
FROM
    MYAPPLICATION_AUDIT
WHERE
    LOWER(USERNAME) = LOWER('&USERNAME')  -- 给&USERNAME添加单引号，确保识别为字符串
    AND TO_TIMESTAMP_TZ(TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF AM TZR') >= &LOWER_TIMESTAMP  -- 直接使用预转换后的变量
    AND TO_TIMESTAMP_TZ(TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF AM TZR') <= &UPPER_TIMESTAMP
ORDER BY
    TIMESTAMP ASC;

• 如果TIMESTAMP列本身是TIMESTAMP WITH TIME ZONE类型，可去掉TO_TIMESTAMP_TZ转换，直接写TIMESTAMP >= &LOWER_TIMESTAMP，提升查询性能。
• 更安全的做法是使用绑定变量替代DEFINE，避免SQL注入风险，示例：

VAR USERNAME VARCHAR2(50);
VAR LOWER_TIMESTAMP TIMESTAMP WITH TIME ZONE;
VAR UPPER_TIMESTAMP TIMESTAMP WITH TIME ZONE;

EXEC :USERNAME := 'userABC';
EXEC :LOWER_TIMESTAMP := TO_TIMESTAMP_TZ('15-MAR-23 02.15.27.603000000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR');
EXEC :UPPER_TIMESTAMP := TO_TIMESTAMP_TZ('15-MAR-23 02.25.27.603000000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR');

SELECT
    REQUEST_NUMBER,
    USERNAME,
    USERACTION,
    MESSAGE_TYPE,
    COMPONENT_NAME,
    TIMESTAMP,
    MODULE_NAME,
    PROCESS_NAME,
    VERSION,
    TASK,
    RESPONSE_CODE,
    RESPONSE_MESSAGE,
    AUDIT_MESSAGE
FROM
    MYAPPLICATION_AUDIT
WHERE
    LOWER(USERNAME) = LOWER(:USERNAME)
    AND TO_TIMESTAMP_TZ(TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF AM TZR') >= :LOWER_TIMESTAMP
    AND TO_TIMESTAMP_TZ(TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF AM TZR') <= :UPPER_TIMESTAMP
ORDER BY
    TIMESTAMP ASC;

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