Re: REF CURSOR

On Jun 17, 10:30 am, t..._at_panix.com (Dan Blum) wrote:
> Mtek <m..._at_mtekusa.com> wrote:
> > 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?
>
> 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 t..._at_panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."

Ho hum, so much for a slick solution.....

Thanks!

John Received on Tue Jun 17 2008 - 10:50:22 CDT