Home » SQL & PL/SQL » SQL & PL/SQL » Question on CURSOR re-use (Oracle 9i - 9.2.0.3 UNIX)
Question on CURSOR re-use [message #345797] Thu, 04 September 2008 18:47 Go to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
As DBA's at my firm disabled viewing of any v$ system view useful for tuning, I was wondering if anyone knew off hand if
the following change to a cursor would negate's it from
being re-used (ie, Oracle will require a hard parse on every execution).

Right now we have a process which takes advantage of cursor variables. For a quick example to reference, lets say:

...

CURSOR C1(p1  varchar2, p2  varchar2) IS 
SELECT COUNT(*) FROM T_SOME_TABLE T 
WHERE  T.A_FIELD =  P1  OR
       T.A_FIELD2 = P2;
...  


The cursor resides in a stored procedure within a package.
I now need to add a function into the cursor SQL which resides
in the same package.

The function will perform sql and return a value. Let's use
this as example:

 

FUNCTION fn_lookup_data(p_key varchar2) RETURN integer IS
lvRet  Integer := 0;
BEGIN  


SELECT COUNT(*) INTO lvRet 
FROM T_SOME_XREF_TBL T 
WHERE T.A_FIELD = p_key;

RETURN lvRet;
END;


Now the original cursor will be modified to use the function as:


CURSOR C1(p1  varchar2, p2  varchar2) IS 
SELECT COUNT(*) FROM T_SOME_TABLE T 
WHERE  T.A_FIELD =  P1  OR
       T.A_FIELD2 = P2  AND 
     DECODE(fn_lookup_data(T.A_FIELD3),1,1,0) = 1; 


Does using a user function in the sql cause the cursor
to be hard for every execution or is there no affect on cursor sharing?

I provided the examples to outline these factors if they
are involved:
(1) The function performs SQL
(2) The function is non-deterministic
(3) The use of the function will be inside a DECODE and
add literals into the cursor
(4) The function exists in the same package as the procedure
which processes the cursor that implements it


I do not know off-hand if we have CURSOR_SHARING set to
EXACT or SIMILAR.

Regards,
Harry
Re: Question on CURSOR re-use [message #345989 is a reply to message #345797] Fri, 05 September 2008 08:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No - using a userdefined function in a cursor will not cause it to need a hard parse every time it is opened.
Re: Question on CURSOR re-use [message #346099 is a reply to message #345989] Sat, 06 September 2008 00:15 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Thank you much!
Previous Topic: Can i connect to telnet(send commands) from a procedure?
Next Topic: Select the Multi Rows of Column in single Row.
Goto Forum:
  


Current Time: Tue Dec 06 02:56:02 CST 2016

Total time taken to generate the page: 0.05558 seconds