Oracle appears to be selective about where it uses this.
>From the docs:
----------------------------------------------------
CURSOR_SHARING determines what kind of SQL statements can share the same
cursors. EXACT causes only identical SQL statements to share a cursor.
FORCE forces statements that may differ in some literals, but are otherwise
identical, to share a cursor, unless the literals affect the meaning of the
statement.
------------------------------------------------------
I could not find any examples of what they meant by 'literals affect the
meaning of the statement"
You may want to check MetaLink, there's quite a bit about it there. Seems
there's a generic platform bug with CURSOR_SHARING that won't be fixed
til 8.1.7.2 in June.
Jared
On Thursday 26 April 2001 20:25, Jay Mehta wrote:
> We are also experimenting with CURSOR_SHARING to reduce excessive parsing
> in the application, and made few observations. It appears that Oracle
> doesn't replace literals with system generated bind variables if SQL
> statement has both literals and bind variables, as shown here:
>
> SELECT RV_VALUE
> FROM
> REF_CODES
> WHERE RV_DOMAIN = 'YESNO'
> AND RV_ABBREVIATION = RTRIM(:b1)
>
> Jay
>
> -----Original Message-----
> Sent: Tuesday, April 24, 2001 5:38 PM
> To: Multiple recipients of list ORACLE-L
>
>
> We have noticed an interesting side "effect" of using CURSOR_SHARING=force.
> When using SQL (simple INSERT, UPDATE, SELECT...), and you check v$SQLAREA
> you see that yes, Oracle indeed replaced hard-coded values with bind
> variables
>
> TRY: SELECT DEPTNO, LOC from DEPT where LOC='Boston';
> select sql_text from v$sqlarea where sql_text like 'SELECT DEPTNO';
>
> BUT this does not work for parameters to procedures or functions.
> TRY:
> create procedure upd_dept ( in_deptno number, in_loc varchar2)
> begin
> update dept set loc = in_loc;
> end;
> exec upd_dept ( 20, 'BOSTON');
> select sql_text from v$sqlarea where sql_text like '%upd_dept%';
>
> Thus we need to change calling our table APIs from :
>
> upd_dept( 20, 'BOSTON');
> to
> define my_deptno := 20;
> define my_location := 'BOSTON';
> upd_dept( :my_deptno, :my_location);
>
> To use bind variables. Thus making extensive use of table APIs will have
> multiple copies
> of SQL in shared pool UNLESS done this way and CURSOR_SHARING has no
> effect. According to Oracle, it is how it is supposed to work, but we were
> not expecting the behaviour.
>
> Just another one of those pleasant surprises from Oracle :-)
>
> Babette
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
>
>
>
> ***************************************************************************
>* This electronic message contains information from CTIS, Inc., which may be
> company sensitive, proprietary, privileged or otherwise protected from
> disclosure. The information is intended to be used solely by the recipients
> named above. If you are not an intended recipient, be aware that any
> review, disclosure, copying, distribution or use of this
> transmission or its contents is prohibited. If you have received this
> transmission in error, please notify us immediately at [EMAIL PROTECTED]
> ***************************************************************************
>*
----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:
----------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
