Re: REF CURSOR
Date: Tue, 17 Jun 2008 07:19:55 -0700 (PDT)
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,
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?
John Received on Tue Jun 17 2008 - 09:19:55 CDT