动态SQL选择返回恐惧的ORA-00933:SQL命令未正确结束

Question

我正在执行以下动态语句并接收ORA-00933：SQL命令未正确结束：

FUNCTION IsValidArchive RETURN BOOLEAN
IS        
    x   BOOLEAN := TRUE;
    isValidArc_sql VARCHAR2(2000);
BEGIN
    isValidArc_sql := 'SELECT COUNT(*), SUM(AMOUNT) ' ||
                      'FROM ' || srcSchemaTable ||
                      ' WHERE TRUNC( ' || parm_rec.SRC_DATE_COLUMN || ' ) < ADD_MONTHS( ' ||
                                                      'ADD_MONTHS ( TRUNC (  NVL ( TO_DATE ( :parm_rec.SYS_OFFSET ) , SYSDATE) - ( :parm_rec.DAY_OFFSET )), ' ||
                                                      '( :parm_rec.MON_OFFSET * :kNEGATIVE ) ), ' ||
                                                      '( :parm_rec.YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ); ';                                                                                                                                               
    EXECUTE IMMEDIATE isValidArc_sql
    INTO arc_cnt, arc_amt
    USING parm_rec.SYS_OFFSET, parm_rec.DAY_OFFSET, parm_rec.MON_OFFSET, kNEGATIVE, parm_rec.YR_OFFSET, kANNUM;
    IF arc_cnt > 0 AND arc_amt > 0 
        THEN x := TRUE;
    ELSE 
        x := FALSE;
    END IF;
    DBMS_OUTPUT.PUT_LINE( 'IsValidArchive: ' || CHR(10) ||
                          'Record Count - '  || arc_cnt || CHR(10) ||
                          'Total Amount - '  || arc_amt || CHR(13) );
    RETURN x;        
END IsValidArchive;

如果我删除该代码将执行的Where子句，这使我相信这可能是一个绑定变量问题。话虽如此，我在程序的另一部分中使用该条款(减去一个绑定变量(，而没有错误。

我的绑定变量都在适当的值中填充，并且语句中的文字也看起来正确。

我的研究表明，这种语法是正确的。我觉得问题在于绑定变量的位置，但解决方案尚未揭示自己。此代码在Execute即时语句中出现错误。

如果有人可以指出我在这里做错了什么，我会非常感激。

谢谢。

update

感谢Kris和Alex指出我的代码中的缺陷，以下是校正的代码：

FUNCTION IsValidArchive RETURN BOOLEAN
IS        
    x   BOOLEAN := TRUE;
BEGIN
    isValidArc_sql := 'SELECT COUNT(*), SUM(AMOUNT) ' ||
                      'FROM ' || srcSchemaTable ||
                      ' WHERE TRUNC( createdate ) < ADD_MONTHS( ' ||
                                                      'ADD_MONTHS ( TRUNC (  NVL  ( :SYS_OFFSET  , SYSDATE ) - ( :DAY_OFFSET )), ' ||
                                                      '( :MON_OFFSET * :kNEGATIVE ) ), ' ||
                                                      '( :YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ) ';                                                                                                                                               
    EXECUTE IMMEDIATE isValidArc_sql
    INTO arc_cnt, arc_amt
    USING parm_rec.SYS_OFFSET, parm_rec.DAY_OFFSET, parm_rec.MON_OFFSET, kNEGATIVE, parm_rec.YR_OFFSET, kANNUM, kNEGATIVE;
    IF arc_cnt > 0 AND arc_amt > 0
        THEN x := TRUE;
    ELSE 
        x := FALSE;
    END IF;
    DBMS_OUTPUT.PUT_LINE( 'IsValidArchive: ' || CHR(10) ||
                          'Record Count - '  || arc_cnt || CHR(10) ||
                          'Total Amount - '  || arc_amt || CHR(13) );
    RETURN x;        
END IsValidArchive;

Answer 1

正如@krisrice指出的那样，您不应在动态语句中具有尾随的半隆。这是sql*plus等中的客户端定义的语句分隔符和/或终结器，但这是一个不同的上下文 - 您只能在动态调用中有一个语句，因此它没有含义。

然后，您的绑定略有错误。您似乎认为您需要绑定变量名称才能匹配using子句中变量的名称，因此您拥有诸如:parm_rec.SYS_OFFSET之类的东西。这里的绑定变量名称实际上只是parm_rec部分。请记住，生成的语句是在SQL上下文中执行的，而不是PL/SQL上下文，因此该周期被解释为schema.object.column模式的一部分，当第一部分(第一部分( - 之前的第一部分是没有意义的 - 是一个变量。

:parm_rec绑定变量名称与parm_rec PL/SQL记录名称无关。您可以同样有效地使用了:b0.SYS_OFSET，这可能会使混乱更加清晰。:b0不是对象或参考，因此是ORA-22806。

因此，将:record.field样式的引用更改为简单标识符，这些标识符仍然可以与匹配变量有一定的名义关系，例如：

    isValidArc_sql := 'SELECT COUNT(*), SUM(AMOUNT) ' ||
                      'FROM ' || srcSchemaTable ||
                      ' WHERE TRUNC( ' || parm_rec.SRC_DATE_COLUMN || ' ) < ADD_MONTHS( ' ||
                                                      'ADD_MONTHS ( TRUNC (  NVL ( TO_DATE ( :SYS_OFFSET ) , SYSDATE) - ( :DAY_OFFSET )), ' ||
                                                      '( :MON_OFFSET * :kNEGATIVE ) ), ' ||
                                                      '( :YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ) ';                                                                                                                                               
    EXECUTE IMMEDIATE isValidArc_sql
    INTO arc_cnt, arc_amt
    USING parm_rec.SYS_OFFSET, parm_rec.DAY_OFFSET, parm_rec.MON_OFFSET, kNEGATIVE, parm_rec.YR_OFFSET, kANNUM, kNEGATIVE;

我还添加了缺少的绑定变量，最后重复kNEGATIVE，因为这些变量是由位置而不是名称引用的。(大概您已经自己修复了；您是在评论中引起的ORA-01008(。

---

大概这些绑定值和所有其他变量都来自软件包级变量，因为它们在函数中未定义，如果没有这种情况，这是很好的。(大概会使您的包裹说明，这有时可能会有些痛苦(。看起来至少arc_cnt和arc_amt可能是本地的，因此需要声明吗？也许还有其他或全部；这取决于我们看不到的其他代码。

顺便说一句，在不指定格式掩码的情况下使用to_date()并不是一个好主意，因为它将依靠客户端的NLS设置。(如果该记录字段已经是日期类型，则不应完全转换它。(