Solved

Oracle Cursor Hangs After Certain Number of Rows (about 76 in this case)

Posted on 2008-06-18
10
1,632 Views
Last Modified: 2013-12-19
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_BaselineActualsETC ';

 
   LOOP
     
        FETCH c1 INTO v_prjid;
        EXIT WHEN c1%NOTFOUND;
       
     
         INSERT INTO BHI_PROJECT_BaselineActualsETC
           ( 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.BASELINE_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.BASELINE_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.BASELINE_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_BaselineActualsETC
                                    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_BaselineActualsETC
                             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(SQLERRM);




END;


0
Comment
Question by:robrubin
  • 7
  • 3
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 21818404
Is the problem in the fetch of row 77 from the cursor or is in the insert of row 77 after you've fetched it?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21818418
I would change your cursor to remove the subquery distinct on odf_ca_incident and also change the union to be union all, that will remove to extra sort operations that do extra work but provide no functionality.

SELECT id
     FROM ( SELECT DISTINCT 1 seq,
                            id
            FROM srm_projects
              WHERE unique_name IN ( SELECT bhi_bot_bas_proj
                                     FROM ODF_CA_INCIDENT  )
            UNION ALL
            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;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21818436
similarly, I would remove the last union on your insert's select statement as it adds nothing except work because the query it union's to already has the exact query same query unioned into it.
plus, change all the remaining UNION to UNION ALL  since each query is distinct already,  doing a UNION merely adds an internal sort and distincting effort that won't do anything.

I'm sure there are other improvements too, but these are the most immediate and obvious and should help regardless of what happens with your cursor at row 77 and beyond
/* Formatted on 2008/06/18 20:34 (Formatter Plus v4.8.8) */

INSERT INTO bhi_project_baselineactualsetc

   (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.baseline_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.baseline_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.baseline_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_baselineactualsetc

                             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_baselineactualsetc

                     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);

Open in new window

0
 

Author Comment

by:robrubin
ID: 21818470
To answer sdstuber,  I changed the WHERE ROWNUM < 76  to WHERE ROWNUM < 500 and then before the big INSERT, I added an INSERT into a temp table to insert the ID and SYSDATE to see how many records get processed.  It stops after the 79th project ID is entered.  

When it hangs like this, the table BHI_PROJECT_BaselineActualsETC which is truncated before each procedure run, contains no data.    Do I need to do a COMMIT at the end of the INSERT so that it commits the insert after each iteration through the cursor loop?   I am wondering also if its a memory issue.

If I take the big INSERT statement and run it by itself with a hard-coded project id, it works even if I put in a bogus project id.  The BHI_PROJECT_BaselineActualsETC shows no data, but it doesn't complain about that.    
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21819058
when you say stops, you mean it hangs?  On which record?  when fetching record 79,  or when trying to insert the data pulled for the id in the 79th row?

Did you make any of the changes I suggested above?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:robrubin
ID: 21819078
I will try them tomorrow when I am back at the customer.  
I can't really tell where it hangs.  I'm assuming since only 79 records are being added to the temp table that it is hanging on the fetch since no data is in the actual table where data is being inserted, which I think may be because we don't have a commit after the insert.  If I put a commit after the insert would that give me a better understanding on where the issue is?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21819117
it might, but I wouldn't do that.

instead, use dbms_application_info.set_client_info  to give realtime (non-transactional) updates to v$session.

-- before the fetch put this
dbms_application_info.set_client_info (TEST: Before fetch');

-- after the fetch put this
dbms_application_info.set_client_info ('TEST:After fetch, id=' || v_prjid );

-- after the insert put this
dbms_application_info.set_client_info('TEST:After insert of id:' || v_prjid);

-- after the cursor is closed
dbms_application_info.set_client_info(null);

when your application hangs, login with another session and query v$session

select client_info from v$session where client_info like 'TEST:%'



0
 

Author Comment

by:robrubin
ID: 21822237
So, the problem seems to be that in hangs on the INSERT.  Is there anyway to force a timeout or bypass the record if it hangs while trying to process the insert?   Ultimately, we will have to rewrite that long query, but I am on a short term assignment here and I just need to get it to work and I don't feel like spending all day trying to figure out which projects are causing the insert to hang.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21823826
you can create a profile for the user with limits..

CPU per call
or
Logical reads/call  

are probably the two fields you're looking for.

The suggestions I gave above for rewrites shouldn't take long to implement (cut and paste then recompile) and test







0
 
LVL 73

Accepted Solution

by:
sdstuber earned 125 total points
ID: 21823870
here's another version of the insert with a few other small changes.

I consolidated all of the summation subqueries that do child+parent+grandparent
as well as eliminated the extra useless union  I mentioned above and fixed the unions to be union all.

Test this out and hopefully it'll save you some time in rewriting the query
INSERT INTO bhi_project_baselineactualsetc

    (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 ALL

     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 SUM(NVL(usage_sum, 0) / 3600)

                    FROM prj_baseline_details, prj_projects

                   WHERE prj_baseline_details.baseline_id =

                                                      prj_projects.baseline_id

                     AND object_type = 'PROJECT'

                     AND (   prid = srm_projects.ID

                          OR prid IN(

                                 SELECT ID

                                   FROM bhi_subdep_parent_v

                                  WHERE parent_id = srm_projects.ID

                                     OR 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

                            OR 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

                            OR 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 ALL

     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_baselineactualsetc

                     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)

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now