On Jun 17, 9:14 am, t...@[EMAIL PROTECTED]
(Dan Blum) wrote:
> Mtek <m...@[EMAIL PROTECTED]
> 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...@[EMAIL PROTECTED]
> "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
|
