Re: REF CURSOR

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 17 Jun 2008 07:19:55 -0700 (PDT)
Message-ID: <cea35969-d274-48f0-a57d-b2640d0a2457@26g2000hsk.googlegroups.com>

On Jun 17, 9:14 am, t..._at_panix.com (Dan Blum) wrote:
> Mtek <m..._at_mtekusa.com> wrote:
> > Hi,
> > I may be answering my own question, but I want to make sure.
> > Say I have a ref cursor that has a dynamic where clase, and in
> > addition a bind variable that changes each time through the loop:
> > OPEN cust_ref FOR
> > 'SELECT customer_name, customer_address
> > FROM customer
> > WHERE customer_id = :v_customer_id'
> > USING v_customer_id;
> > My question is, since v_customer_id is a parameter, do I need to close
> > and open the cursor each time that value changes? Or can I just
> > change the value and fetch the next record?
>
> You need to close and open the cursor.
>
> Actually, I suspect that what you really need to do is not use a cursor here.
> Are there multiple records per customer_id? If not, I see no need for an
> explicit cursor.
>
> --
> _______________________________________________________________________
> Dan Blum t..._at_panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."

Well, the where clause will vary a bit. And, there could be multiple records. That was just an example. The actual select is this:

  SELECT ol.product_id, co.order_id, co.customer_id, ol.order_item_id, p.code, z.hsc_assignment,

         DECODE(z.salesman_id,NULL,9888,z.salesman_id) salesman_id, z.comments
  FROM customer_order co, order_line ol, product p, zmt_order_info z

  WHERE  ol.order_id = co.order_id
    AND  ol.product_id = p.product_id
    AND  ol.order_item_id = z.order_item_id(+)

    AND p.type = 3
    AND (co.date_entered > TO_DATE(p_start_date,'MMDDYYYY') OR co.order_id = p_order_id);

Here, the p_order_id may or may not be defined. If it is defined, I want to use both the date AND the p_order_id. If the p_order_id is NOT defined, then I only want to use the date.

So, I thought I'd use a ref cursor to define the where clause.....maybe I do not need it and I can do something else?

Thanks!

John Received on Tue Jun 17 2008 - 09:19:55 CDT