Oracle 从11.2.0.2迁移到11.2.0.4后，sql的Bind变量不存在错误

嗨，伙计们，
迁移到11.2.0.4版本后，我遇到了一些错误。
在旧版本11.2.0.2中，每当应用程序尝试为特定SQL执行许多内联游标表达式时，我都会收到 “ORA-00604: 递归SQL级别1游标表达式不允许出现错误” 错误
喜欢
选择seq_no，光标 (选择。... 从 ....)，光标 (选择。... 从 ....) 从 .........
我被建议升级到11.2.0.4，所以我做到了。
现在... ORA-00604: 在递归SQL级别1发生错误，ORA-01006以下SQL的Bind变量不存在。


它与bind varible窥视有关吗 (在谷歌搜索时刚刚听到这个词:-P)

我能够在QA中成功运行相同的查询。(已经是11.2.0.4) ..

附加说明:

过去，当我们的一个dba尝试实现优化器建议的SQL配置文件时，我们遇到了一个smilar错误，并且导致了everythng中断并绑定变量错误。回应来自DBA

“当作业执行时，它启动了主查询，由于游标，它产生了许多子查询。这些子查询中的每一个都需要适度的时间。我不记得细节了，但我认为大约是一分钟。

我在游标内部的查询上运行了sql调优，认为如果我们减少查询时间，它将大大减少整个事物的查询时间。”

并进一步删除了配置文件，并要求我们在没有太多内联游标表达式的情况下重组SQL (这是不可行的)

PROCEDURE rtetget
(
    i_n_Sub_SEC_SEQ_NO        IN LSDB202_MDL_SUBSEC.LS202_SUBSEC_SEQ_NO %TYPE
    ,i_n_SEC_SEQ_NO            IN LSDB201_MDL_SEC.LS201_SEC_SEQ_NO%TYPE
    ,i_n_Order_Key            IN LSDB400_ORDR.LS400_ORDR_KEY %TYPE
    ,i_s_Data_Loc            IN LSDB400_ORDR.LS150_DATA_LOC_TYPE_CODE %TYPE
    ,i_n_Rev_Input             IN NUMBER
    ,i_n_Spec                IN LSDB090_FILE_TYPE.LS090_FILE_TYPE_CODE%TYPE
    ,i_s_UserId                  IN LSDB010_EMD_USERS.LS010_USER_ID%TYPE
    ,o_cur_Disp                OUT OUT_CURSOR
    ,o_n_LSDBErrorID         OUT NUMBER
    ,o_s_OracleCode             OUT VARCHAR2
    ,o_s_ErrorMessage          OUT VARCHAR2
)
IS
 l_cla_desc_chng_flag      VARCHAR2(1):=NULL;
 l_s_Sys_Marker_Desc       VARCHAR2(100):=NULL;
 l_s_Dyn_Clause_Flag        VARCHAR2(1) :=NULL;

BEGIN
    /************************************************************
       |  Variable Initialization done here.
     |************************************************************/

    o_n_LSDBErrorID    := Pk_Lsdb_Constants.con_n_Zero;
    o_s_OracleCode        := Pk_Lsdb_Constants.con_n_Zero;
    o_s_ErrorMessage   := Pk_Lsdb_Constants.con_n_Space;

 OPEN o_cur_Disp FOR

    SELECT G.LS300_CLA_SEQ_NO,G.LS301_VERSION_NO,G.LS202_SUBSEC_SEQ_NO
          ,G.LS406_CPY_IND,G.MODEL_INDICATOR
          ,(SELECT NVL(
                (SELECT Distinct Pk_Lsdb_Constants.con_s_flag_YES
                 FROM   LSDB418_ORDR_DEL_CLA_STAGING Z
                 WHERE  Z.LS300_CLA_SEQ_NO = G.LS300_CLA_SEQ_NO
                 AND    Z.LS400_ORDR_NO = (SELECT DISTINCT LS400_ORDR_NO
                                             FROM  LSDB400_ORDR
                                            WHERE  LS400_ORDR_KEY =
i_n_Order_Key)),Pk_Lsdb_Constants.con_s_flag_NO)
                FROM DUAL) CLA_EXISTS_FLAG
            ,CURSOR(
                     SELECT
DECODE(Q.LS080_SPEC_STATUS_CODE,1,'DRFT',2,'PRPSL',3,'BSLNE','REV.'||Q.LS408_SPEC_REV_CODE)
||
                            DECODE(Q.LS408_SPEC_SUBLVL_REV_CODE,NULL,
                            ' ','.'||Q.LS408_SPEC_SUBLVL_REV_CODE)
                             REVISION_NUM
                     FROM LSDB408_ORDR_CLA_REV Q
                     WHERE Q.LS406_CLA_NUM = G.LS414_CLA_NO
                       AND Q.LS300_CLA_SEQ_NO=G.LS300_CLA_SEQ_NO
                       AND Q.LS400_ORDR_KEY=i_n_Order_Key
                       AND Q.LS150_DATA_LOC_TYPE_CODE=i_s_Data_Loc
                       AND Q.LS408_CLA_DESC_CHNG_FLAG=NVL(l_cla_desc_chng_flag,Q.LS408_CLA_DESC_CHNG_FLAG)
                       AND DECODE(Q.LS080_SPEC_STATUS_CODE,
                                        1,'DRFT',
                                        2,'PRPSL',
                                        3,'BSLNE',

Q.LS408_SPEC_REV_CODE)||'.'||Q.LS408_SPEC_SUBLVL_REV_CODE=
                           CASE i_n_Rev_Input
                             WHEN 1 THEN NULL
                             WHEN 2 THEN (SELECT DECODE(V.LS080_SPEC_STATUS_CODE,
                                                         1,'DRFT',
                                                         2,'PRPSL',
                                                         3,'BSLNE',

V.LS408_SPEC_REV_CODE)||'.'||V.LS408_SPEC_SUBLVL_REV_CODE
                                            FROM LSDB408_ORDR_CLA_REV v, LSDB400_ORDR w
                                           WHERE V.LS406_CLA_NUM = G.LS414_CLA_NO
                                             AND V.LS300_CLA_SEQ_NO=G.LS300_CLA_SEQ_NO
                                             AND V.LS400_ORDR_KEY=i_n_Order_Key
                                             AND V.LS150_DATA_LOC_TYPE_CODE='S'
                                             AND V.LS080_SPEC_STATUS_CODE =W.LS080_SPEC_STATUS_CODE
                                             AND
NVL(V.LS408_SPEC_REV_CODE,0)=NVL(W.LS400_SPEC_REV_CODE,0)
                                             AND NVL(V.LS408_SPEC_SUBLVL_REV_CODE,0)=
                                                 NVL(W.LS400_SPEC_SUBLVL_REV_CODE,0)
                                             AND
V.LS150_DATA_LOC_TYPE_CODE=W.LS150_DATA_LOC_TYPE_CODE
                                             AND V.LS400_ORDR_KEY=W.LS400_ORDR_KEY)
                             WHEN 3 THEN
                                 DECODE(Q.LS080_SPEC_STATUS_CODE,
                                                1,'DRFT',
                                                2,'PRPSL',
                                                3,'BSLNE',

Q.LS408_SPEC_REV_CODE)||'.'||Q.LS408_SPEC_SUBLVL_REV_CODE
                            --Added For CR_92
                            WHEN 4 THEN
                                 Q.LS408_SPEC_REV_CODE||'.'||Q.LS408_SPEC_SUBLVL_REV_CODE
                            --Ends here
                           END
                        ORDER BY  Q.LS080_SPEC_STATUS_CODE,REVISION_NUM
                   )REVISION_NUMBER,
                  
            CURSOR(
                    SELECT DECODE(Q.LS080_SPEC_STATUS_CODE,1,'DRFT',2,'PRPSL',3,'BSLNE','REV.'||Q.LS408_SPEC_REV_CODE)
                              ||DECODE(Q.LS408_SPEC_SUBLVL_REV_CODE,NULL,' ','.'||Q.LS408_SPEC_SUBLVL_REV_CODE)
                           REVISION_NUM
                    FROM LSDB408_ORDR_CLA_REV Q
                    WHERE Q.LS406_CLA_NUM = G.LS414_CLA_NO
                    AND Q.LS300_CLA_SEQ_NO=G.LS300_CLA_SEQ_NO
                    AND Q.LS400_ORDR_KEY=i_n_Order_Key
                    AND Q.LS150_DATA_LOC_TYPE_CODE=i_s_Data_Loc
                    AND Q.LS408_CLA_DESC_CHNG_FLAG=NVL(l_cla_desc_chng_flag,Q.LS408_CLA_DESC_CHNG_FLAG)
                    AND DECODE(Q.LS080_SPEC_STATUS_CODE,
                               1,'DRFT',
                               2,'PRPSL',
                               3,'BSLNE',Q.LS408_SPEC_REV_CODE)||'.'||Q.LS408_SPEC_SUBLVL_REV_CODE
                                =(SELECT DECODE(V.LS080_SPEC_STATUS_CODE,1,'DRFT',2,'PRPSL',3,'BSLNE',
                                                V.LS408_SPEC_REV_CODE)||'.'||V.LS408_SPEC_SUBLVL_REV_CODE
                                           FROM LSDB408_ORDR_CLA_REV v, LSDB400_ORDR w
                                            WHERE V.LS406_CLA_NUM =G.LS414_CLA_NO
                                            AND V.LS300_CLA_SEQ_NO=G.LS300_CLA_SEQ_NO
                                            AND V.LS400_ORDR_KEY=i_n_Order_Key
                                            AND V.LS150_DATA_LOC_TYPE_CODE='S'
                                            AND V.LS080_SPEC_STATUS_CODE =W.LS080_SPEC_STATUS_CODE
                                            AND NVL(V.LS408_SPEC_REV_CODE,0)=NVL(W.LS400_SPEC_REV_CODE,0)
                                            AND NVL(V.LS408_SPEC_SUBLVL_REV_CODE,0)
                                                =NVL(W.LS400_SPEC_SUBLVL_REV_CODE,0)
                                            AND V.LS150_DATA_LOC_TYPE_CODE=W.LS150_DATA_LOC_TYPE_CODE
                                            AND V.LS400_ORDR_KEY=W.LS400_ORDR_KEY)
                    ORDER BY  Q.LS080_SPEC_STATUS_CODE,REVISION_NUM
                   ) LATEST_REVISION_MARKER
           
        
    FROM LSDB307_CLA_HRCHY H,
                  (
                   SELECT
A.LS300_CLA_SEQ_NO,A.LS301_VERSION_NO,A.LS406_ORDR_BY_CODE,X.LS202_SUBSEC_CODE
                         ,X.LS202_SUBSEC_SEQ_NO,A.LS193_CLA_HRCHY_SEQ_NO

,DECODE(A.LS301_VERSION_NO,A.LS406_CPY_FROM_VERSION_NO,PK_LSDB_CONSTANTS.con_s_flag_NO
                         ,PK_LSDB_CONSTANTS.con_s_flag_YES) LS406_CPY_IND
                         ,NVL(
                            (SELECT DISTINCT Pk_Lsdb_Constants.con_s_flag_YES MODELINDICATOR
                                 FROM LSDB406_ORDR_CLA W,LSDB301_CLA_VER Y
                                 ,LSDB300_CLA X
                             WHERE W.LS300_CLA_SEQ_NO=Y.LS300_CLA_SEQ_NO
                                   AND W.LS301_VERSION_NO<>Y.LS301_VERSION_NO
                                   AND Y.LS301_DEFAULT_FLAG=Pk_Lsdb_Constants.con_s_flag_YES
                                   AND W.LS300_CLA_SEQ_NO=A.LS300_CLA_SEQ_NO
                                   AND W.LS406_VERSION_UPDT_DATEPk_Lsdb_Constants.con_s_Cla_Source_Delete
                          ),Pk_Lsdb_Constants.con_s_flag_NO) MODEL_INDICATOR
                         ,A.LS406_CLA_NUM LS414_CLA_NO
                         ,B.LS301_PRICE_BOOK_NUMBER
                        ,B.LS301_CLA_DESC
                         ,B.LS301_DWO_NUMBER,B.LS301_PART_NUMBER,B.LS301_ENGG_DATA_COMMENTS
                       ,Y.LS170_IMG_SEQ_NO,
                        W.LS415_IMG_NAME,
                        W.LS415_IMG_DESC
                         ,DECODE(P.LS300_CLA_SEQ_NO,NULL,PK_LSDB_CONSTANTS.con_s_flag_NO
                         ,DECODE(
                                (SELECT  PK_LSDB_CONSTANTS.con_s_flag_YES
                                   FROM LSDB417_ORDR_DEL_CLA B
                                  WHERE b.LS300_CLA_SEQ_NO=(
                                    SELECT  A.LS300_PARENT_CLA_SEQ_NO
                                      FROM LSDB307_CLA_HRCHY A
                                     WHERE A.LS300_CHILD_CLA_SEQ_NO=P.LS300_CLA_SEQ_NO)
                                   AND b.LS400_ORDR_KEY=i_n_Order_Key)

,NULL,PK_LSDB_CONSTANTS.con_s_flag_YES,PK_LSDB_CONSTANTS.con_s_flag_NO)
                         )  DEL_IND
                       ,C.LS190_CLA_SOURCE_CODE
                       ,B.LS301_DELETE_FLAG
                       ,A.LS406_USR_MARKER
                       ,A.LS406_SYS_MARKER
                       ,A.LS406_CLA_DEL_FLAG
                       --Modified For CR_92 System Marker has three Values Y, N, H
                       ,DECODE(A.LS406_SYS_MARKER,PK_LSDB_CONSTANTS.con_s_flag_NO
                                   ,NULL,l_s_Sys_Marker_Desc) SYS_MARKER_DESC
                       ,C.LS300_LEAD_COMP_GRP_SEQ_NO
                   
                       ,A.LS406_SALES_SYS_MARKER
                    
                       ,C.LS300_CHAR_GRP_FLAG
                        ,NVL(
                            (SELECT PK_LSDB_CONSTANTS.con_s_flag_YES
                             FROM    LSDB429_ORDR_CLA_EDL_STAGING A
                             WHERE    A.LS300_CLA_SEQ_NO = C.LS300_CLA_SEQ_NO
                             AND    A.LS400_ORDR_KEY = i_n_Order_Key
                             ),PK_LSDB_CONSTANTS.con_s_flag_NO) EDLINDICATOR
               
                        ,A.LS406_CLA_RENUMBRD_FLAG
                
                        ,A.LS406_USR_MARKED_REASON
              

                  FROM  LSDB406_ORDR_CLA A
                       ,LSDB301_CLA_VER B
                       ,LSDB300_CLA C
                       ,LSDB202_MDL_SUBSEC X
                       ,LSDB416_ORDR_CLA_APNDX_IMG Y
                       ,LSDB415_ORDR_APNDX_IMG W
                       ,LSDB417_ORDR_DEL_CLA P
             
                 WHERE A.LS400_ORDR_KEY=i_n_Order_Key
                   AND A.LS150_DATA_LOC_TYPE_CODE=i_s_Data_Loc
                   AND A.LS300_CLA_SEQ_NO=B.LS300_CLA_SEQ_NO
                   AND A.LS301_VERSION_NO=B.LS301_VERSION_NO
                   AND X.LS202_SUBSEC_SEQ_NO=C.LS202_SUBSEC_SEQ_NO
                   AND X.LS201_SEC_SEQ_NO =i_n_SEC_SEQ_NO
                   AND C.LS202_SUBSEC_SEQ_NO=NVL(i_n_Sub_SEC_SEQ_NO,C.LS202_SUBSEC_SEQ_NO)
                   AND B.LS300_CLA_SEQ_NO=C.LS300_CLA_SEQ_NO
                   AND A.LS300_CLA_SEQ_NO=Y.LS300_CLA_SEQ_NO(+)
                   AND A.LS301_VERSION_NO=Y.LS301_VERSION_NO(+)
                   AND A.LS400_ORDR_KEY=Y.LS400_ORDR_KEY(+)
                   AND A.LS150_DATA_LOC_TYPE_CODE=Y.LS150_DATA_LOC_TYPE_CODE(+)
                   AND Y.LS400_ORDR_KEY=W.LS400_ORDR_KEY(+)
                   AND Y.LS150_DATA_LOC_TYPE_CODE=W.LS150_DATA_LOC_TYPE_CODE(+)
                   AND DECODE(Y.LS170_IMG_SEQ_NO,NULL,1,W.LS170_IMG_SEQ_NO)
                      =DECODE(Y.LS170_IMG_SEQ_NO,NULL,1,Y.LS170_IMG_SEQ_NO)
                   AND A.LS300_CLA_SEQ_NO=P.LS300_CLA_SEQ_NO(+)
                   AND A.LS301_VERSION_NO=P.LS301_VERSION_NO(+)
                   AND A.LS400_ORDR_KEY=P.LS400_ORDR_KEY(+)
                
                   AND DECODE(l_s_Dyn_Clause_Flag,PK_LSDB_CONSTANTS.con_s_flag_NO,
                        'A',A.LS406_CLA_DEL_FLAG) = DECODE(l_s_Dyn_Clause_Flag,PK_LSDB_CONSTANTS.con_s_flag_NO,
                                                       'A', PK_LSDB_CONSTANTS.con_s_flag_NO)
                 

            )G
    WHERE H.LS300_CHILD_CLA_SEQ_NO=G.LS300_CLA_SEQ_NO
    START WITH H.LS300_PARENT_CLA_SEQ_NO IS NULL
    CONNECT BY H.LS300_PARENT_CLA_SEQ_NO=PRIOR H.LS300_CHILD_CLA_SEQ_NO
    ORDER SIBLINGS BY G.LS202_SUBSEC_CODE,G.LS193_CLA_HRCHY_SEQ_NO,G.LS406_ORDR_BY_CODE;
复制

对不起，书中的家伙... 试图提供尽可能多的信息... :-)

良好的... 这是一个强大的大sql :-)

我建议尝试大块地解决这个问题

1) 删除 * 每个 * 游标表达式，查看sql是否可以在简单的测试工具中工作，即

可变rc refcursor
变量out1数
等等
exec rtetget([parms]，: rc，: out1，: out2，: out3)

2) 如果可行，则一次选择性地将每个光标表达式加回一个，然后重复测试线束。

缩小范围，以便找到错误的确切原因。

从那里工作。