Re: REF CURSOR
Date: Tue, 17 Jun 2008 15:30:21 +0000 (UTC)
Message-ID: <g38lad$p99$1@reader2.panix.com>
Mtek <mtek_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 tool_at_panix.com "I wouldn't have believed it myself if I hadn't just made it up."Received on Tue Jun 17 2008 - 10:30:21 CDT