REF CURSOR

#21

On Jun 17, 10:30 am, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

Mtek <m... (AT) mtekusa (DOT) com> wrote:
On Jun 17, 9:14 am, t... (AT) panix (DOT) com (Dan Blum) wrote:
Mtek <m... (AT) mtekusa (DOT) 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 (DOT) 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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

Of course, the other option is to have a couple of cursors, and pass
parameters so I do not have to close and open them.....

#22

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

<snip>

Quote:

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

Of course, the other option is to have a couple of cursors, and pass
parameters so I do not have to close and open them.....

You could do that, but whether it's worth it or not depends on how often you will
be opening and closing the same statements, and how many different statements you
need. If you are going to be running the same statements (with different parameters)
thousands of times, and performance is important, you can definitely save some
overhead by using DBMS_SQL (it does much less parsing that way, which reduces
latching). However, using DBMS_SQL requires more coding than just opening cursors.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#23

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

<snip>

Quote:

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

Of course, the other option is to have a couple of cursors, and pass
parameters so I do not have to close and open them.....

You could do that, but whether it's worth it or not depends on how often you will
be opening and closing the same statements, and how many different statements you
need. If you are going to be running the same statements (with different parameters)
thousands of times, and performance is important, you can definitely save some
overhead by using DBMS_SQL (it does much less parsing that way, which reduces
latching). However, using DBMS_SQL requires more coding than just opening cursors.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#24

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

<snip>

Quote:

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

Of course, the other option is to have a couple of cursors, and pass
parameters so I do not have to close and open them.....

You could do that, but whether it's worth it or not depends on how often you will
be opening and closing the same statements, and how many different statements you
need. If you are going to be running the same statements (with different parameters)
thousands of times, and performance is important, you can definitely save some
overhead by using DBMS_SQL (it does much less parsing that way, which reduces
latching). However, using DBMS_SQL requires more coding than just opening cursors.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#25

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

<snip>

Quote:

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

Of course, the other option is to have a couple of cursors, and pass
parameters so I do not have to close and open them.....

You could do that, but whether it's worth it or not depends on how often you will
be opening and closing the same statements, and how many different statements you
need. If you are going to be running the same statements (with different parameters)
thousands of times, and performance is important, you can definitely save some
overhead by using DBMS_SQL (it does much less parsing that way, which reduces
latching). However, using DBMS_SQL requires more coding than just opening cursors.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

REF CURSOR

comp.databases.oracle.misc comp.databases.oracle.misc

Re: REF CURSOR - 06-17-2008 , 10:51 AM

Re: REF CURSOR - 06-17-2008 , 11:36 AM

Re: REF CURSOR - 06-17-2008 , 11:36 AM

Re: REF CURSOR - 06-17-2008 , 11:36 AM

Re: REF CURSOR - 06-17-2008 , 11:36 AM