Hi,
I have a written a procedure as follows which is sending few records in sys_refcursor to fron tend(dot net here) and also updating those records inside the same block.
CREATE OR REPLACE TYPE LV_EXE_ASN_LIST as object
(
RULE_NAME VARCHAR2(250 BYTE),
ADVICE_ID NUMBER,
THREAD_ID NUMBER,
MESSAGE_ID NUMBER,
TEMPLATE_DTL_ID NUMBER
);
/
CREATE OR REPLACE TYPE TB_EXE_ASN_LIST as table of LV_EXE_ASN_LIST;
/
When I am writing the proc as follows I am getting ORA-24338: statement handle not executed
PROCEDURE a_proc (PI_RULE_ID IN NUMBER,
PI_ADVISOR_ID IN NUMBER,
PI_THREAD_ID IN NUMBER,
PO_ASN_LST OUT SYS_REFCURSOR,
PO_ERR_CODE OUT NUMBER,
PO_ERR_MSG OUT VARCHAR2 ) IS
TYPE LV_EXE_ASN_LIST1 IS RECORD
(
RULE_NAME A.RULE_NAME%TYPE,
thd_dtl_rec D%rowtype
);
TYPE LV_EXE_ASN_LIST2 IS RECORD
(
RULE_NAME A.RULE_NAME%TYPE,
ADVICE_ID D.ADVICE_ID%TYPE,
THREAD_ID D.THREAD_ID%TYPE,
MESSAGE_ID D.MESSAGE_ID%TYPE,
TEMPLATE_DTL_ID D.TEMPLATE_DTL_ID%TYPE
);
cursor PO_ASN_LST1 is
SELECT RULE_NAME,D.*
FROM A,
B,
C,
D TD
WHERE A.RULE_ID = B.RULE_ID
AND B.ADVISOR_ID = C.ADVISOR_ID
AND C.ADVICE_ID = D.ADVICE_ID
AND C.THREAD_ID = D.THREAD_ID
AND A.RULE_ID = PI_RULE_ID
AND B.ADVISOR_ID =PI_ADVISOR_ID
AND D.THREAD_ID = PI_THREAD_ID
AND RULE_STATUS = 'E'
AND ADVISOR_STATUS = 'E'
AND ADVICE_STATUS = 'R'
AND MESSEAGE_STATUS = 'R'
ORDER BY D.ADVICE_ID,D.THREAD_ID ;
TYPE TB_EXE_ASN_LIST1 IS table of PO_ASN_LST1%rowtypeLV_EXE_ASN_TY1 TB_EXE_ASN_LIST1
LV_EXE_ASN_TY TB_EXE_ASN_LIST:=TB_EXE_ASN_LIST();
BEGIN
PO_ERR_CODE :=0;
PO_ERR_MSG := NULL;
open PO_ASN_LST1;
fetch PO_ASN_LST1 bulk collect into LV_EXE_ASN_TY1;
close PO_ASN_LST1;
for indx in 1.. LV_EXE_ASN_TY1.count
loop
LV_EXE_ASN_TY.extend();
LV_EXE_ASN_TY(LV_EXE_ASN_TY.last):=LV_EXE_ASN_LIST(LV_EXE_ASN_TY1(indx).RULE_NAME,
LV_EXE_ASN_TY1(indx).ADVICE_ID,
LV_EXE_ASN_TY1(indx).THREAD_ID,
LV_EXE_ASN_TY1(indx).MESSAGE_ID,
LV_EXE_ASN_TY1(indx).TEMPLATE_DTL_ID);
end loop;
forall indx in 1.. LV_EXE_ASN_TY1.count
update D
set MESSEAGE_STATUS='I',
MESSEAGE_STATUS_DETAIL='INPROGRESS'
where MESSAGE_ID=LV_EXE_ASN_TY1(indx).MESSAGE_ID
and thread_id=PI_THREAD_ID
and ADVIce_ID=(select ADVIce_ID
from C
where thread_id=PI_THREAD_ID
and ADVISOR_ID=PI_ADVISOR_ID
and ADVICE_STATUS='R')
and MESSEAGE_STATUS='R';
update C
set ADVICE_STATUS='I'
where thread_id=PI_THREAD_ID
and ADVISOR_ID=PI_ADVISOR_ID
and ADVICE_STATUS='R';
commit;
OPEN PO_ASN_LST FOR
SELECT * from table(cast (LV_EXE_ASN_TY as TB_EXE_ASN_LIST));
EXCEPTION
WHEN OTHERS THEN
NULL;
PO_ERR_CODE :=1;
PO_ERR_MSG := SUBSTR(SQLERRM,1,100);
END a_proc ;
If I am writing the same proc as follows then I am not getting the error.
PROCEDURE a_proc (PI_RULE_ID IN NUMBER,
PI_ADVISOR_ID IN NUMBER,
PI_THREAD_ID IN NUMBER,
PO_ASN_LST OUT SYS_REFCURSOR,
PO_ERR_CODE OUT NUMBER,
PO_ERR_MSG OUT VARCHAR2 ) IS
TYPE LV_EXE_ASN_LIST1 IS RECORD
(
RULE_NAME A.RULE_NAME%TYPE,
thd_dtl_rec D%rowtype
);
TYPE LV_EXE_ASN_LIST2 IS RECORD
(
RULE_NAME A.RULE_NAME%TYPE,
ADVICE_ID D.ADVICE_ID%TYPE,
THREAD_ID D.THREAD_ID%TYPE,
MESSAGE_ID D.MESSAGE_ID%TYPE,
TEMPLATE_DTL_ID D.TEMPLATE_DTL_ID%TYPE
);
cursor PO_ASN_LST1 is
SELECT RULE_NAME,D.*
FROM A,
B,
C,
D TD
WHERE A.RULE_ID = B.RULE_ID
AND B.ADVISOR_ID = C.ADVISOR_ID
AND C.ADVICE_ID = D.ADVICE_ID
AND C.THREAD_ID = D.THREAD_ID
AND A.RULE_ID = PI_RULE_ID
AND B.ADVISOR_ID =PI_ADVISOR_ID
AND D.THREAD_ID = PI_THREAD_ID
AND RULE_STATUS = 'E'
AND ADVISOR_STATUS = 'E'
AND ADVICE_STATUS = 'R'
AND MESSEAGE_STATUS = 'R'
ORDER BY D.ADVICE_ID,D.THREAD_ID ;
TYPE TB_EXE_ASN_LIST1 IS table of PO_ASN_LST1%rowtypeLV_EXE_ASN_TY1 TB_EXE_ASN_LIST1
LV_EXE_ASN_TY TB_EXE_ASN_LIST:=TB_EXE_ASN_LIST();
BEGIN
PO_ERR_CODE :=0;
PO_ERR_MSG := NULL;
open PO_ASN_LST1;
fetch PO_ASN_LST1 bulk collect into LV_EXE_ASN_TY1;
close PO_ASN_LST1;
for indx in 1