Hi Guys...I am new to PLSQL..the below code doesnt seem to work when used with union operator..i have a nested cursor "components" which differ in the first query and the second query..
i pass this as a ref cursor to java.
code below (its an excerpt from the stored procedure):
OPEN o_cur_1058_Cla_Chng_Req FOR
SELECT A.LS900_1058_SEQ_NO,
A.LS910_CLA_CHANGE_REQ_SEQ_NO,
A.LS903_CHANGE_TYPE_SEQ_NO,
B.LS903_CHANGE_TYPE_DESC,
A.LS910_REASON,
A.LS910_CLA_UPDATED_TO_SPEC,
CURSOR
(SELECT A.LS910_CHANGE_FROM_CLA_NUM,
A.LS910_CHANGE_FROM_CLA_SEQ_NO,
A.LS910_CHANGE_FROM_VER_NO,
E.LS301_CLA_DESC,
CURSOR (
SELECT G.LS302_EDL_NO
FROM LSDB302_CLA_EDL_NO G
WHERE G.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND G.LS301_VERSION_NO = E.LS301_VERSION_NO
) EDLNO,
CURSOR (
SELECT H.LS303_REF_EDL_NO
FROM LSDB303_CLA_REF_EDL_NO H
WHERE H.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND H.LS301_VERSION_NO = E.LS301_VERSION_NO
) refEDLNO,
CURSOR (
SELECT M.LS202_SUBSEC_SEQ_NO,
NVL(M.LS304_PART_OF_CLA_NUMBER,M.LS304_SUBSEC_NO) LS304_PART_OF_CLA_NUMBER,
M.LS304_PART_OF_CLA_SEQ_NO,
M.LS304_PART_OF_LEAD_CMP_GRP
FROM LSDB304_CLA_PART_OF M
WHERE M.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND M.LS301_VERSION_NO = E.LS301_VERSION_NO
) PartOF,
CURSOR (
SELECT I.LS306_TBL_DATA_COL_1,
I.LS306_TBL_DATA_COL_2,
I.LS306_TBL_DATA_COL_3,
I.LS306_TBL_DATA_COL_4,
I.LS306_TBL_DATA_COL_5,
I.LS306_HEADER_FLAG
FROM LSDB306_CLA_TBL_DATA I
WHERE I.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND I.LS301_VERSION_NO = E.LS301_VERSION_NO
ORDER BY I.LS306_ORDER_OF_TABLE_DATA ASC
) TABLE_DATE,
E.LS301_DWO_NUMBER, E.LS301_PART_NUMBER, E.LS301_PRICE_BOOK_NUMBER,
CURSOR (
SELECT J.LS060_STD_EQP_SEQ_NO,
J.LS060_STD_EQP_DESC
FROM LSDB060_STD_EQP J
WHERE J.LS060_STD_EQP_SEQ_NO = E.LS060_STD_EQP_SEQ_NO
) STD_EQUIP,
E.LS301_ENGG_DATA_COMMENTS, E.LS301_CLA_REASON,
CURSOR(SELECT K.LS140_COMP_NAME,K.LS140_COMP_SEQ_NO,I.LS130_COMP_GRP_NAME
FROM LSDB140_COMP K,LSDB305_CLA_COMP L,LSDB130_COMP_GRP I
WHERE K.LS140_COMP_SEQ_NO = L.LS140_COMP_SEQ_NO
AND L.LS300_CLA_SEQ_NO = D.LS300_CLA_SEQ_NO
AND I.LS130_COMP_GRP_SEQ_NO = K.LS130_COMP_GRP_SEQ_NO) COMPONENTS
FROM LSDB300_CLA D, LSDB301_CLA_VER E
WHERE D.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND E.LS300_CLA_SEQ_NO = A.LS910_CHANGE_FROM_CLA_SEQ_NO
AND E.LS301_VERSION_NO = A.LS910_CHANGE_FROM_VER_NO ) CHANGE_FROM,
CURSOR
(SELECT R.LS910_CHANGE_TO_CLA_NUM,
R.LS910_CHANGE_TO_CLA_SEQ_NO,
R.LS910_CHANGE_TO_VER_NO,
R.LS910_CHANGE_TO_CLA_DESC,
I.LS130_COMP_GRP_NAME,
Q.LS926_COMP_NAME,
CURSOR (
SELECT K.LS911_EDL_NO
FROM LSDB911_1058_CLAUSE_EDL K
WHERE K.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) EDLNO,
CURSOR (
SELECT L.LS912_REF_EDL_NO
FROM LSDB912_1058_CLAUSE_REF_EDL L
WHERE L.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) refEDLNO,
CURSOR (
SELECT M.LS202_SUBSEC_SEQ_NO,
M.LS913_PART_OF_CLA_NO,
M.LS913_PART_OF_CLA_SEQ_NO,
M.LS913_PART_OF_LEAD_CMP_GRP
FROM LSDB913_1058_CLAUSE_PART_OF M
WHERE M.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) PartOF,
CURSOR (
SELECT N.LS915_TBL_DATA_COL_1,
N.LS915_TBL_DATA_COL_2,
N.LS915_TBL_DATA_COL_3,
N.LS915_TBL_DATA_COL_4,
N.LS915_TBL_DATA_COL_5,
N.LS915_HEADER_FLAG
FROM LSDB915_1058_CLAUSE_TBL_DATA N
WHERE N.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
ORDER BY N.LS915_ORDER_OF_TABLE_DATA ASC
) TABLE_DATE,
R.LS910_DWO_NO, R.LS910_PART_NO, R.LS910_PRICE_BOOK_NO,
CURSOR (
SELECT O.LS060_STD_EQP_SEQ_NO,
O.LS060_STD_EQP_DESC
FROM LSDB060_STD_EQP O
WHERE O.LS060_STD_EQP_SEQ_NO = R.LS060_STD_EQP_SEQ_NO
) STD_EQUIP,
R.LS910_ENG_DATA_COMMENTS,
CURSOR(SELECT P.LS140_COMP_NAME,
P.LS140_COMP_SEQ_NO,
I.LS130_COMP_GRP_NAME
FROM LSDB140_COMP P,LSDB914_1058_CLAUSE_COMP Q,LSDB130_COMP_GRP I
WHERE P.LS140_COMP_SEQ_NO = Q.LS140_COMP_SEQ_NO
AND I.LS130_COMP_GRP_SEQ_NO = P.LS130_COMP_GRP_SEQ_NO
AND Q.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) COMPONENTS
FROM LSDB910_1058_CLA_CHANGE_REQ R,LSDB926_COMP_TEMP Q,LSDB130_COMP_GRP I
WHERE R.LS910_CLA_CHANGE_REQ_SEQ_NO = A.LS910_CLA_CHANGE_REQ_SEQ_NO
AND R.LS900_1058_SEQ_NO = A.LS900_1058_SEQ_NO
AND Q.LS130_COMP_GRP_SEQ_NO = I.LS130_COMP_GRP_SEQ_NO(+)
AND R.LS910_NEW_COMP_SEQ_NO = Q.LS926_COMP_SEQ_NO(+)) CHANGE_TO
FROM LSDB910_1058_CLA_CHANGE_REQ A, LSDB903_1058_CLA_CHNG_TYPE B, LSDB900_1058_DETAILS C
WHERE A.LS910_CLA_CHANGE_REQ_SEQ_NO = NVL(i_n_Cla_Chng_Req_Seq,A.LS910_CLA_CHANGE_REQ_SEQ_NO)
AND A.LS903_CHANGE_TYPE_SEQ_NO = B.LS903_CHANGE_TYPE_SEQ_NO
AND A.LS900_1058_SEQ_NO = C.LS900_1058_SEQ_NO
AND A.LS900_1058_SEQ_NO = i_n_1058SeqNo
AND A.LS903_CHANGE_TYPE_SEQ_NO <> 2
UNION
SELECT A.LS900_1058_SEQ_NO,
A.LS910_CLA_CHANGE_REQ_SEQ_NO,
A.LS903_CHANGE_TYPE_SEQ_NO,
B.LS903_CHANGE_TYPE_DESC,
A.LS910_REASON,
A.LS910_CLA_UPDATED_TO_SPEC,
CURSOR
(SELECT A.LS910_CHANGE_FROM_CLA_NUM,
A.LS910_CHANGE_FROM_CLA_SEQ_NO,
A.LS910_CHANGE_FROM_VER_NO,
E.LS301_CLA_DESC,
CURSOR (
SELECT G.LS302_EDL_NO
FROM LSDB302_CLA_EDL_NO G
WHERE G.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND G.LS301_VERSION_NO = E.LS301_VERSION_NO
) EDLNO,
CURSOR (
SELECT H.LS303_REF_EDL_NO
FROM LSDB303_CLA_REF_EDL_NO H
WHERE H.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND H.LS301_VERSION_NO = E.LS301_VERSION_NO
) refEDLNO,
CURSOR (
SELECT M.LS202_SUBSEC_SEQ_NO,
NVL(M.LS304_PART_OF_CLA_NUMBER,M.LS304_SUBSEC_NO) LS304_PART_OF_CLA_NUMBER,
M.LS304_PART_OF_CLA_SEQ_NO,
M.LS304_PART_OF_LEAD_CMP_GRP
FROM LSDB304_CLA_PART_OF M
WHERE M.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND M.LS301_VERSION_NO = E.LS301_VERSION_NO
) PartOF,
CURSOR (
SELECT I.LS306_TBL_DATA_COL_1,
I.LS306_TBL_DATA_COL_2,
I.LS306_TBL_DATA_COL_3,
I.LS306_TBL_DATA_COL_4,
I.LS306_TBL_DATA_COL_5,
I.LS306_HEADER_FLAG
FROM LSDB306_CLA_TBL_DATA I
WHERE I.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND I.LS301_VERSION_NO = E.LS301_VERSION_NO
ORDER BY I.LS306_ORDER_OF_TABLE_DATA ASC
) TABLE_DATE,
E.LS301_DWO_NUMBER, E.LS301_PART_NUMBER, E.LS301_PRICE_BOOK_NUMBER,
CURSOR (
SELECT J.LS060_STD_EQP_SEQ_NO,
J.LS060_STD_EQP_DESC
FROM LSDB060_STD_EQP J
WHERE J.LS060_STD_EQP_SEQ_NO = E.LS060_STD_EQP_SEQ_NO
) STD_EQUIP,
E.LS301_ENGG_DATA_COMMENTS, E.LS301_CLA_REASON,
CURSOR(SELECT K.LS140_COMP_NAME,K.LS140_COMP_SEQ_NO,I.LS130_COMP_GRP_NAME
FROM LSDB140_COMP K,LSDB305_CLA_COMP L,LSDB130_COMP_GRP I
WHERE K.LS140_COMP_SEQ_NO = L.LS140_COMP_SEQ_NO
AND L.LS300_CLA_SEQ_NO = D.LS300_CLA_SEQ_NO
AND I.LS130_COMP_GRP_SEQ_NO = K.LS130_COMP_GRP_SEQ_NO) COMPONENTS
FROM LSDB300_CLA D, LSDB301_CLA_VER E
WHERE D.LS300_CLA_SEQ_NO = E.LS300_CLA_SEQ_NO
AND E.LS300_CLA_SEQ_NO = A.LS910_CHANGE_FROM_CLA_SEQ_NO
AND E.LS301_VERSION_NO = A.LS910_CHANGE_FROM_VER_NO ) CHANGE_FROM,
CURSOR
(SELECT R.LS910_CHANGE_TO_CLA_NUM,
R.LS910_CHANGE_TO_CLA_SEQ_NO,
R.LS910_CHANGE_TO_VER_NO,
R.LS910_CHANGE_TO_CLA_DESC,
I.LS130_COMP_GRP_NAME,
Q.LS926_COMP_NAME,
CURSOR (
SELECT K.LS911_EDL_NO
FROM LSDB911_1058_CLAUSE_EDL K
WHERE K.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) EDLNO,
CURSOR (
SELECT L.LS912_REF_EDL_NO
FROM LSDB912_1058_CLAUSE_REF_EDL L
WHERE L.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) refEDLNO,
CURSOR (
SELECT M.LS202_SUBSEC_SEQ_NO,
M.LS913_PART_OF_CLA_NO,
M.LS913_PART_OF_CLA_SEQ_NO,
M.LS913_PART_OF_LEAD_CMP_GRP
FROM LSDB913_1058_CLAUSE_PART_OF M
WHERE M.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
) PartOF,
CURSOR (
SELECT N.LS915_TBL_DATA_COL_1,
N.LS915_TBL_DATA_COL_2,
N.LS915_TBL_DATA_COL_3,
N.LS915_TBL_DATA_COL_4,
N.LS915_TBL_DATA_COL_5,
N.LS915_HEADER_FLAG
FROM LSDB915_1058_CLAUSE_TBL_DATA N
WHERE N.LS910_CLA_CHANGE_REQ_SEQ_NO = R.LS910_CLA_CHANGE_REQ_SEQ_NO
ORDER BY N.LS915_ORDER_OF_TABLE_DATA ASC
) TABLE_DATE,
R.LS910_DWO_NO, R.LS910_PART_NO, R.LS910_PRICE_BOOK_NO,
CURSOR (
SELECT O.LS060_STD_EQP_SEQ_NO,
O.LS060_STD_EQP_DESC
FROM LSDB060_STD_EQP O
WHERE O.LS060_STD_EQP_SEQ_NO = R.LS060_STD_EQP_SEQ_NO
) STD_EQUIP,
R.LS910_ENG_DATA_COMMENTS,
CURSOR(SELECT P.LS140_COMP_NAME,
P.LS140_COMP_SEQ_NO,
I.LS130_COMP_GRP_NAME
FROM LSDB140_COMP P,LSDB305_CLA_COMP Q,LSDB130_COMP_GRP I
WHERE P.LS140_COMP_SEQ_NO = Q.LS140_COMP_SEQ_NO
AND I.LS130_COMP_GRP_SEQ_NO = P.LS130_COMP_GRP_SEQ_NO
AND Q.LS300_CLA_SEQ_NO = A.LS910_CHANGE_FROM_CLA_SEQ_NO
)COMPONENTS
FROM LSDB910_1058_CLA_CHANGE_REQ R,LSDB926_COMP_TEMP Q,LSDB130_COMP_GRP I
WHERE R.LS910_CLA_CHANGE_REQ_SEQ_NO = A.LS910_CLA_CHANGE_REQ_SEQ_NO
AND R.LS900_1058_SEQ_NO = A.LS900_1058_SEQ_NO
AND Q.LS130_COMP_GRP_SEQ_NO = I.LS130_COMP_GRP_SEQ_NO(+)
AND R.LS910_NEW_COMP_SEQ_NO = Q.LS926_COMP_SEQ_NO(+) ) CHANGE_TO
FROM LSDB910_1058_CLA_CHANGE_REQ A, LSDB903_1058_CLA_CHNG_TYPE B, LSDB900_1058_DETAILS C
WHERE A.LS910_CLA_CHANGE_REQ_SEQ_NO = NVL(i_n_Cla_Chng_Req_Seq,A.LS910_CLA_CHANGE_REQ_SEQ_NO)
AND A.LS903_CHANGE_TYPE_SEQ_NO = B.LS903_CHANGE_TYPE_SEQ_NO
AND A.LS900_1058_SEQ_NO = C.LS900_1058_SEQ_NO
AND A.LS900_1058_SEQ_NO = i_n_1058SeqNo
AND A.LS903_CHANGE_TYPE_SEQ_NO= 2
ORDER BY LS910_CLA_CHANGE_REQ_SEQ_NO;
"Doesnt work" *doesnt* tell us anything.
- Maybe your database is down
- maybe you are not connected
- maybe you are connected as the wrong user
- maybe you are on the wrong database
- maybe your session timed out
- maybe resource manager is blocking you
- maybe...
- maybe ...
- maybe....
The first thing to solve a problem is to be able to describe what the problem is accurately.
Recast it like this and run it in SQL Plus
<code>
SQL> variable rc refcursor
SQL> begin
open :rc for
SELECT A.LS900_1058_SEQ_NO,
A.LS910_CLA_CHANGE_REQ_SEQ_NO,
A.LS903_CHANGE_TYPE_SEQ_NO,
B.LS903_CHANGE_TYPE_DESC,
...
...
SQL> end;
SQL> /
because then you will get
a) the error (which you decided you wouldnt share with us)
b) the line at which the error is present.