Hi Guys,
I ran into some errors after migration to 11.2.0.4 version.
In the older version 11.2.0.2 , i got 'ORA-00604:error occured at recursive SQL level 1 CURSOR expression not allowed' error whenever the application tried to execute many inline cursor expressions for a particular SQL
like
select seq_no,CURSOR(select ....from....), CURSOR(select ....from....)from .........
i was suggested to upgrade to 11.2.0.4 and so i did..
and now...ORA-00604:error occured at recursive SQL level 1 ORA-01006 Bind variable does not exist' for the below SQL.
Does it got something to do with bind varible peeking(just heard this word while googling :-P)
i was able to run the same query in QA successfully.(which was already 11.2.0.4) ..
additional note :
we ran into a smilar error in the past when one of our DBAs tried implementing a SQL profile suggested by optimizer and everythng broke and bind variable error resulted. the response was from DBA was
"when the job executed, it started the main query, which spawned numerous sub-queries because of the cursor. Each of those subqueries where taking a modest about of time. I don’t remember the details but I think it was about a minute each.
I ran the sql tuning on the query inside the cursor thinking if we reduced the query time of that, it would reduce the query time of the entire thing substantially"
and further proceeded to remove the profile and asked us to restructure the SQL without much of inline cursor expressions(which was not doable)
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_DATE<Y.LS301_UPDT_DATE --Updated for CR_121
AND W.LS400_ORDR_KEY=i_n_Order_Key
AND W.LS150_DATA_LOC_TYPE_CODE=i_s_Data_Loc
AND Y.LS300_CLA_SEQ_NO=X.LS300_CLA_SEQ_NO
AND
X.LS190_CLA_SOURCE_CODE<>Pk_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;sorry for the book guys...tried to give as much info as i can... :-)