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?

Thanks!

John

Mtek <mtek (AT) mtekusa (DOT) com> wrote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

Mtek <mtek (AT) mtekusa (DOT) com> wrote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

Mtek <mtek (AT) mtekusa (DOT) com> wrote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

Mtek <mtek (AT) mtekusa (DOT) com> wrote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

On Jun 17, 9:14 am, t... (AT) panix (DOT) com (Dan Blum) wrote:
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

On Jun 17, 9:14 am, t... (AT) panix (DOT) com (Dan Blum) wrote:
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

On Jun 17, 9:14 am, t... (AT) panix (DOT) com (Dan Blum) wrote:
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

On Jun 17, 9:14 am, t... (AT) panix (DOT) com (Dan Blum) wrote:
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

Mtek <mtek (AT) mtekusa (DOT) com> wrote:
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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."