Mtek <mtek@[EMAIL PROTECTED]
> wrote:
> 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?
If you are using dynamic SQL and need to loop through the results then you
probably do need to open an explicit cursor. And you will need to close
and
re-open it when the statement changes in any way.
If you were just changing the bind variable values, you could avoid some
of
the overhead by using DBMS_SQL, but that will not help if the conditions
actually
change.
--
_______________________________________________________________________
Dan Blum tool@[EMAIL PROTECTED]
"I wouldn't have believed it myself if I hadn't just made it up."
|
