We have a cursor that selects 1 field (ID) and then runs a complex insert statement where it inserts that ID along with other information from a more complex SELECT.
If we limit the number of rows returned from the cursor's select statement to about 76, the procedure executes fine within about 2 seconds. As soon as we increase the number of rows returned to 77 or higher, the procedure just hangs with no response.
The code is below. I just don't understand how it can be perfectly fine up to 76 rows and then the next row would cause it to hang.
The entire code is:
DECLARE
type r_cursor is REF CURSOR;
c1 r_cursor;
v_prjid srm_projects.id%type;
BEGIN
OPEN c1 FOR
SELECT id
FROM ( SELECT DISTINCT 1 seq,
id
FROM srm_projects
WHERE unique_name IN ( SELECT DISTINCT bhi_bot_bas_proj
FROM ODF_CA_INCIDENT )
UNION
SELECT 2 seq,
id
FROM ODF_CA_PROJECT
WHERE PARTITION_CODE = 'BOT_IT_PARTITION' ) PRJLIST
WHERE ROWNUM < 76 and id not in (5028787)
ORDER BY id,seq;
EXECUTE IMMEDIATE ' TRUNCATE TABLE BHI_PROJECT_BaselineActual
sETC ';
LOOP
FETCH c1 INTO v_prjid;
EXIT WHEN c1%NOTFOUND;
INSERT INTO BHI_PROJECT_BaselineActual
sETC
( SELECT *
FROM ( SELECT 0 projsub,
v_prjid PID,
srm_projects.id prj_id,
NULL SubprojCount,
srm_projects.unique_name,
srm_projects.NAME prj_name,
(BASEREC.USAGE_SUM / 3600) / PRJ_HPD_FACTOR_FCT() baseline,
( SELECT actsum
FROM prj_project_actsum_v
WHERE id = SRM_PROJECTS.id ) actuals,
( SELECT estsum
FROM prj_project_estsum_v
WHERE id = SRM_PROJECTS.id ) etc
FROM odf_ca_project ,
PRJ_PROJECTS ,
SRM_PROJECTS ,
PRJ_PROJECTS PR1
LEFT JOIN PRJ_BASELINE_DETAILS BASEREC
ON ( BASEREC.BASELINE_ID = PR1.BASELINE_ID
AND BASEREC.OBJECT_TYPE = 'PROJECT' )
WHERE srm_projects.id = prj_projects.prid
AND srm_projects.id = odf_ca_project.id
AND SRM_PROJECTS.ID = PR1.PRID
AND srm_projects.id =v_prjid
UNION
SELECT 1 projsub,
v_prjid PID,
srm_projects.id prj_id,
( SELECT COUNT(*)
FROM BHI_SubDep_Parent_v
WHERE parent_id = srm_projects.id ) SubProjCount,
srm_projects.unique_name,
srm_projects.NAME prj_name,
NVL(( SELECT Usage_Sum / 3600
FROM PRJ_BASELINE_DETAILS ,
PRJ_PROJECTS
WHERE PRJ_BASELINE_DETAILS.BASEL
INE_ID = PRJ_PROJECTS.baseline_id
AND PRID = srm_projects.id
AND Object_type = 'PROJECT' ), 0) + NVL(( SELECT SUM(NVL(Usage_Sum, 0) / 3600)
FROM PRJ_BASELINE_DETAILS ,
PRJ_PROJECTS
WHERE PRJ_BASELINE_DETAILS.BASEL
INE_ID = PRJ_PROJECTS.baseline_id
AND Object_type = 'PROJECT'
AND PRID IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id = srm_projects.id ) ), 0) + NVL(( SELECT SUM(NVL(Usage_Sum, 0) / 3600)
FROM PRJ_BASELINE_DETAILS ,
PRJ_PROJECTS
WHERE PRJ_BASELINE_DETAILS.BASEL
INE_ID = PRJ_PROJECTS.baseline_id
AND Object_type = 'PROJECT'
AND PRID IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id = srm_projects.id ) ) ), 0) Baseline,
(NVL(( SELECT SUM(NVL(actsum, 0))
FROM prj_project_actsum_v
WHERE id = srm_projects.id ) + NVL(( SELECT SUM(NVL(actsum, 0))
FROM prj_project_actsum_v act,
BHI_SubDep_Parent_v ppv
WHERE act.id = ppv.id
AND ppv.parent_id = srm_projects.id ), 0) + NVL(( SELECT SUM(NVL(actsum, 0))
FROM prj_project_actsum_v act,
BHI_SubDep_Parent_v ppv
WHERE act.id = ppv.id
AND ppv.parent_id IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id = srm_projects.id ) ), 0), 0) + NVL(( SELECT NVL(ITL_GET_ACTUAL_EFFORT_
FCT(OBJECT
_ID), 0)
FROM ITL_OBJECT_ASSOCIATIONS Assoc,
IMM_INCIDENTS IMM,
ODF_CA_INCIDENT ODF,
PRTASK TSK
WHERE Assoc.OBJECT_ID = IMM.ID
AND IMM.ID = ODF.ID
AND PK_ID = TSK.PRID
AND prprojectid = srm_projects.id ), 0) + NVL(( SELECT NVL(ITL_GET_ACTUAL_EFFORT_
FCT(OBJECT
_ID), 0)
FROM ITL_OBJECT_ASSOCIATIONS Assoc,
IMM_INCIDENTS IMM,
ODF_CA_INCIDENT ODF,
PRTASK TSK
WHERE Assoc.OBJECT_ID = IMM.ID
AND IMM.ID = ODF.ID
AND PK_ID = TSK.PRID
AND prprojectid IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id = srm_projects.id ) ), 0)) actuals,
(NVL(( SELECT NVL(estsum, 0)
FROM prj_project_estsum_v
WHERE id = srm_projects.id ), 0) + NVL(( SELECT SUM(NVL(estsum, 0))
FROM prj_project_estsum_v act,
BHI_SubDep_Parent_v ppv
WHERE act.id = ppv.id
AND ppv.parent_id = srm_projects.id ), 0) + NVL(( SELECT SUM(NVL(estsum, 0))
FROM prj_project_estsum_v act,
BHI_SubDep_Parent_v ppv
WHERE act.id = ppv.id
AND ppv.parent_id IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id = srm_projects.id ) ), 0)) etc
FROM odf_ca_project ,
PRJ_PROJECTS ,
SRM_PROJECTS ,
PRJ_PROJECTS PR1
LEFT JOIN PRJ_BASELINE_DETAILS BASEREC
ON ( BASEREC.BASELINE_ID = PR1.BASELINE_ID
AND BASEREC.OBJECT_TYPE = 'PROJECT' )
WHERE srm_projects.id = prj_projects.prid
AND srm_projects.id = odf_ca_project.id
AND SRM_PROJECTS.ID = PR1.PRID
AND srm_projects.id IN ( SELECT id
FROM BHI_SubDep_Parent_v
WHERE parent_id =v_prjid )
UNION
SELECT 2 projsub,
v_prjid PID,
assoc.id prj_id,
NULL SubProjCount,
NVL(BHI_hcc_ticket_no, '') Unique_name,
imm.SUBJECT prj_name,
NULL baseline,
(NVL(( SELECT SUM(NVL(actuals, 0))
FROM BHI_PROJECT_BaselineActual
sETC
WHERE PID = ( SELECT id
FROM srm_projects
WHERE unique_name = odf.bhi_bot_bas_proj ) ), 0) + NVL(ITL_GET_ACTUAL_EFFORT_
FCT(OBJECT
_ID), 0)) Actuals,
NULL etc
FROM ITL_OBJECT_ASSOCIATIONS Assoc,
IMM_INCIDENTS IMM,
ODF_CA_INCIDENT ODF,
PRTASK TSK
WHERE Assoc.OBJECT_ID = IMM.ID
AND IMM.ID = ODF.ID
AND PK_ID = TSK.PRID
AND prprojectid =v_prjid ) prj_sum
UNION
SELECT 2 projsub,
v_prjid PID,
assoc.id prj_id,
NULL SubProjCount,
NVL(BHI_hcc_ticket_no, '') Unique_name,
imm.SUBJECT prj_name,
NULL baseline,
(NVL(( SELECT SUM(NVL(actuals, 0))
FROM BHI_PROJECT_BaselineActual
sETC
WHERE PID = ( SELECT id
FROM srm_projects
WHERE unique_name = odf.bhi_bot_bas_proj ) ), 0) + NVL(ITL_GET_ACTUAL_EFFORT_
FCT(OBJECT
_ID), 0)) Actuals,
NULL etc
FROM ITL_OBJECT_ASSOCIATIONS Assoc,
IMM_INCIDENTS IMM,
ODF_CA_INCIDENT ODF
WHERE Assoc.OBJECT_ID = IMM.ID
AND IMM.ID = ODF.ID
AND Assoc.pk_id =v_prjid );
--PRINT @PRJID
DBMS_OUTPUT.PUT_LINE( to_char(systimestamp, 'HH24:MI:SS.FF6') || ' : ' || to_char(v_prjid));
END LOOP;
CLOSE c1;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLER
RM);
END;
Start Free Trial
