ORA-00904

Oracle November 22nd, 2006

程序中有一个字段需要限制一下条件，但又不需要传入变量，不料遭遇了这个错误：
ERRORMSG: ORA-00904: “APPROVED”: invalid identifier

我是这样解决的：

EXECUTE IMMEDIATE '
SELECT SUM(REQ_LINE_QUANTITY)
FROM PO_REQ_DISTRIBUTIONS_INQ_V
WHERE AUTHORIZATION_STATUS = :APPROVED
AND ORG_ID = :P_ORG_ID
AND PROJECT_ID = :P_PROJ_ID
AND ITEM_ID = :P_ITEM_ID'
INTO V_QTY
USING ' APPROVED ', P_ORG_ID, P_PROJ_ID, P_ITEM_ID;

2 Responses to “ORA-00904”

  1. dwr Says:
    February 12th, 2007 at 11:21 am

    能说得详细些吗？
    APPROVED
    P_ORG_ID
    P_PROJ_ID
    P_ITEM_ID
    之前的脚本是什么？
    INTO V_QTY
    USING ‘ APPROVED ‘, P_ORG_ID, P_PROJ_ID, P_ITEM_ID
    是什么意思啊？

  2. zeeno Says:
    February 13th, 2007 at 11:59 am

    这段SQL是放在一个function里的，我一开始是这样限制AUTHORIZATION_STATUS：
    AUTHORIZATION_STATUS=”APPROVED”

    因为execute immediate是用’(单引号)传入sql，所以我理所当然的认为里面放”(双引号)应该没问题。但实际上不是。

    execute immediate 的语法参考:
    http://www.missbb.net/2006/11/20/execute-immediate/
    意思是将SQL语句获取的结果传入变量V_QTY。

Leave a Reply