procedure cursor return variables and loop over query

Question

I have a question about this procedure. What I am trying to do is to pass in one variable which is the ID, then set a veraible out and return a query / cursor. I got some help on this yesterday, but something is not right and it won't compile. Can someone help me sort this out? Here is what I have so far.

PROCEDURE SEEKER (pMonkeyID IN VARCHAR2, vMarkCounter OUT Number, seeker_cur OUT TYPES.ref_cursor)
AS
BEGIN

CURSOR seeker_cur IS
    Select monkey_doc_approved, monkey_doc_vaulted
    from monkeyApps 
    where MonkeyID = pMonkeyID
    and monkey_doc_type = 'Banana' 
    order by monkey_doc_approved_timestamp,monkey_doc_type,monkey_doc_approved desc

vMarkCounter number:=0;
BEGIN
  FOR i IN seeker_cur 
  LOOP
    vMarkCounter := vMarkCounter+1;
  END LOOP;
END;

END SEEKER;

I am not sure I am setting my cursor for returning right, and I am not sure I am looping correctly to set my monkeyMarker. the cursor needs to return as well as the marker because I deal with some front end logic with both.

Thanks, Frank

good catch that is one problem...It still won't compile. Choking on the cursor seeker_cur is part...Will this actually return the cursor or do I need to implicitly set the cursor? — Frank Tudor
– Frank Tudor, Commented Feb 6, 2012 at 15:02
get rid of the first BEGIN. The first part is the declaration section — tbone
– tbone, Commented Feb 6, 2012 at 15:50

A.B.Cade · Accepted Answer · 2012-02-06 17:26:50Z

1

I think that getting both the records and the number of them is wrong. But if that's what you want the you can do it like this:

PROCEDURE SEEKER (pMonkeyID IN VARCHAR2, vMarkCounter OUT Number, seeker_cur OUT TYPES.ref_cursor)
AS

BEGIN

    OPEN seeker_cur for Select monkey_doc_approved, monkey_doc_vaulted 
                         from monkeyApps where MonkeyID = pMonkeyID  and monkey_doc_type = 'Banana' order by monkey_doc_approved_timestamp,monkey_doc_type,monkey_doc_approved desc;

    Select count(*) INTO vMarkCounter  from (Select monkey_doc_approved, monkey_doc_vaulted 
                         from monkeyApps where MonkeyID = pMonkeyID  and monkey_doc_type = 'Banana');


END SEEKER;

But again, check if you really need to count the rows before you even fetched them ...

edited Feb 6, 2012 at 17:26

answered Feb 6, 2012 at 15:54

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vincent Malgrat Over a year ago

Don't use dynamic SQL when static SQL works. Static SQL is safer and faster. Use OPEN seeker_cur FOR SELECT... and SELECT COUNT(*) INTO vMarkCounter...

Vincent Malgrat Over a year ago

+1 :) although nitpicking, COUNT only accepts one argument. Also OPEN c FOR (SELECT ...) doesn't compile, you need to remove the parentheses.

A.B.Cade Over a year ago

Thanks again, i hope there are no compilation errors now 9I can't chech them). The inner select isn't needed but it's better

Ben · Accepted Answer · 2012-02-06 21:00:50Z

0

I'll agree with AB Cade I don't understand why you need the records and the count.There should, however, be little to no need to loop though a cursor in PL\SQL; you can use bulk collect instead, which returns what you want.

If you don't have that many records, say less than 50k I would remove the limit 10000 part of the bulk collect; you can just use t_seeker.count, which'll give you your answer.

PROCEDURE SEEKER ( pMonkeyID IN VARCHAR2
                 , vMarkCounter OUT Number
                 , c_seeker OUT TYPES.ref_cursor) IS

  cursor c_seeker is
    select monkey_doc_approved, monkey_doc_vaulted
      from monkeyApps 
     where MonkeyID = pMonkeyID
       and monkey_doc_type = 'Banana' 
     order by monkey_doc_approved_timestamp
            , monkey_doc_type
            , monkey_doc_approved desc
           ;

  type t__seeker is table of c_seeker%rowtype index by binary_integer;
  t_seeker t__seeker;

  vMarkCounter number := 0;

begin

  open c_seeker;
  loop

     fetch c_seeker bulk collect into t_seeker limit 10000;

     vMarkCounter := vMarkCounter + t_seeker.count;

  end loop;

  -- OP want's to return a ref_cursor.
  --close c_seeker;

end seeker;
/

edited Feb 6, 2012 at 21:00

answered Feb 6, 2012 at 17:50

Ben

53.1k36 gold badges133 silver badges156 bronze badges

4 Comments

Frank Tudor Over a year ago

I am not sure if everything is right here. For example I and trying to pull the ref cursor of seeker_cur. Where is that being set in the proc, also I have a c_seeker, t_seeker and a t__seeker (I don't understand this).

Frank Tudor Over a year ago

I think I have discovered something else or maybe it is my only problem. I am passing in vMarkCounter, then in the proc I am setting it or manipulating it. Could this cause oracle to error? I do the vmarkCounter, should I not declare it? If I don't will it bpass the value back to me and I can just pick this up from my front side application? Thoughts?

Ben Over a year ago

@FrankTudor, if you're parsing vmarkcounter in then why is it just an out? I coded it this way to make it more efficient to do your select and count. I personally wouldn't return a ref_cursor with either my method or yours. In both you've already selected all the data so why would you want to do it again? Mine put's it in a user defined type so you can return an object, which is stored in memory, which already contains all your data... I wouldn't suggest you do anything you're not comfortable with or don't understand though. It'll make it much harder to maintain.

Frank Tudor Over a year ago

I understand I completely refactored the procedure. Thanks for the great code help!

Collectives™ on Stack Overflow

procedure cursor return variables and loop over query

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related