• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12226
  • Last Modified:

Count number of rows in a cursor

Hi,

I am new to Procedures and cursors, so would appreciate some advice on this.

In the following Procedure, the Select of the Cursor can return one or more rows.

CREATE OR REPLACE PROCEDURE COMPUTE_DELAY
( P_STEP_ID IN BPM_DEF_STEPS.ID%TYPE)
AS
  xPRED_ID         BPM_DEF_STEPS.ID%TYPE;
  xSUCC_ID         BPM_DEF_STEPS.ID%TYPE;

  CURSOR c_get_parent(pSTEP_ID BPM_DEF_STEPS.ID%TYPE) IS
    SELECT
      PREDSTEP.ID                                    PRED_ID,
      SUCCSTEP.ID                                   SUCC_ID
    FROM
       BPM_DEF_STEPS                          PREDSTEP,
       BPM_DEF_STEPS                                 SUCCSTEP,
       BPM_DEF_STEP_CONDITIONS        COND,
       BPM_DEF_STEP_TRANSITIONS      TRANS
    WHERE
        PREDSTEP.ID                                   = COND.STEP_ID
        AND COND.ID                                  = TRANS.STEP_CONDITION_ID
        AND TRANS.NEXT_STEP_ID           = SUCCSTEP.ID
        AND SUCCSTEP.ID                          = pSTEP_ID
    ORDER BY
        PRED_ID,   SUCC_ID;

BEGIN
  OPEN c_get_parent(P_STEP_ID);
      LOOP
        FETCH c_get_parent INTO
          xPRED_ID,                        -- xPred_ID = ID of PRED
          xSUCC_ID;                        -- xSucc_ID = ID of SUCC
   
        &

        END LOOP;
    CLOSE c_get_parent;
END COMPUTE_DELAY;

Within the main Loop, I need to know how many rows were returned by the Select.  I know that c_get_parent%ROWCOUNT after Fetch, can tell how many rows have been retrieved so far, but I need to know how to determine the total number of rows in c_get_parent before I proceed with the next statements.
0
cgosney42
Asked:
cgosney42
  • 5
  • 2
1 Solution
 
schwertnerCommented:
To do this you have to execute

v_count  integer;

.....
SELECT count(*) INTO v_count
FROM (
SELECT
      PREDSTEP.ID                                    PRED_ID,
      SUCCSTEP.ID                                   SUCC_ID
    FROM
       BPM_DEF_STEPS                          PREDSTEP,
       BPM_DEF_STEPS                                 SUCCSTEP,
       BPM_DEF_STEP_CONDITIONS        COND,
       BPM_DEF_STEP_TRANSITIONS      TRANS
    WHERE
        PREDSTEP.ID                                   = COND.STEP_ID
        AND COND.ID                                  = TRANS.STEP_CONDITION_ID
        AND TRANS.NEXT_STEP_ID           = SUCCSTEP.ID
        AND SUCCSTEP.ID                          = pSTEP_ID
    ORDER BY
        PRED_ID,   SUCC_ID);
0
 
Jinesh KamdarCommented:
... or you can use the FOR LOOP with the same cursor as below : -

...
v_count := 0;
...
BEGIN
FOR i IN c_get_parent LOOP
       v_count := v_count + 1;
END LOOP;

OPEN ...
0
 
Jinesh KamdarCommented:
Sorry, i missed the cursor arg. in the prev. post.

...
v_count := 0;
...
BEGIN
FOR i IN c_get_parent LOOP(p_step_id)
       v_count := v_count + 1;
END LOOP;

OPEN ...back to top
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
cgosney42Author Commented:
I was hoping for an easier solution to be honest.

Is it possible to fetch the values from the cursor twice?  Something like:

counter := 0;
FOR c IN c_get_parent
   LOOP
      FETCH c_get_parent INTO c ;
      EXIT WHEN c_get_parent%NOTFOUND ;
      counter := counter + 1 ;
END LOOP ;

Then to continue with ..

LOOP
        FETCH c_get_parent INTO
          xPRED_ID,                        -- xPred_ID = ID of PRED
          xSUCC_ID;                        -- xSucc_ID = ID of SUCC

?
0
 
Jinesh KamdarCommented:
Yes, you can :) but you need not fetch it again in the LOOP, FOR LOOP does an implicit fetch. Use it the way i showed.
0
 
cgosney42Author Commented:
You were too quick for me ... you posted your response while I was typing mine LOL

Anyway, with a slight change of syntax, this works :)  Thanks

  xPredCount := 0;

  FOR i IN c_get_parent(p_step_id) LOOP
    xPredCount := xPredCount + 1;
  END LOOP;
0
 
Jinesh KamdarCommented:
0
 
Jinesh KamdarCommented:
Oh sorry ... You were right ... Got it :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now