Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Sarayu.

Asked: April 19, 2007 - 5:30 am UTC

Last updated: May 31, 2012 - 12:02 am UTC

Version: 9.2.0.8

Viewed 1000+ times

You Asked

Hi tom,
good morning.

I had a problem regarding sqlplus report generation today

we have two databases. These two are supposed to be having similar settings.

(1) Version is same 9.2.0.8 Enterprised edition
(2) There are no login.sql and glogin.sql. dba's might not have configured.
(3) values in nls_database_parameters and nls_session_parameters are exactly same.


I logged into the machine where oracle is installed.

In database 1:

SQL> select ',' from dual;

'
-
,


In second database database 2:

SQL> select ',' from dual;

','
--------------------------------
,

May i know what kind of settings in sqlplus/oracle would change this behaviour to display same sql statement. One db shows only 1 character and second database is taking more than 30 characters. When i checked all sqlplus settings, i found linesize is 120 in one database and 132 in another database.

Thanks in advance.

Sarayu

and Tom said...

the dreaded 'cursor_sharing' parameter...


ops$tkyte%ORA10GR2> alter session set cursor_sharing = exact /* the default and only correct value! */ ;

Session altered.

ops$tkyte%ORA10GR2> select ',' from dual look_for_me_exact;

'
-
,

ops$tkyte%ORA10GR2> alter session set cursor_sharing = force ;

Session altered.

ops$tkyte%ORA10GR2> select ',' from dual look_for_me_force;

','
--------------------------------
,

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing = exact /* the default and only correct value! */ ;

Session altered.

ops$tkyte%ORA10GR2> select sql_text from v$sql where sql_text like '% from dual look\_for\_me\_%' escape '\';

SQL_TEXT
-------------------------------------------------------------------------------
select :"SYS_B_0" from dual look_for_me_force
select ',' from dual look_for_me_exact




see how that horrible cursor sharing = force setting took every literal out.


if you have to use cursor_sharing = force, you have a big nasty bug in your developed code that needs to be fixed for security reasons, performance reasons.

https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ah !

Michel CADOT, April 19, 2007 - 10:19 am UTC

Thanks Tom,

I failed to find the point on this one when he asked it on OraFaq.

Regards
Michel

Excellent Tom

Sarayu, April 20, 2007 - 2:56 am UTC

Tom,
we all felt that the issue is somewhere in sqlplus settings (May be we have to change the subject for this question).
Really surprised to see that cursor_sharing is impacting this. Thank you very much for educating us on this.
I learnt a wonderful lesson today. Thanks again.




Another way to solve it

www.ora-code.net, May 30, 2012 - 5:28 am UTC

You've solved my problem.
It is very strange behavior. It should be warned in the official Oracle documentation.

By the way, setting SET TRIMOUT ON also avoids the problem in any configuration CURSOR_SHARING.
Tom Kyte
May 30, 2012 - 7:41 am UTC

set trimout does NOTHING, what do you mean by that?

You still get your field sizes changed, you lose your scales, your precision.

all of my examples forever have had trimout on - cursor sharing =force/similar behaves the same.


It is an obvious side effect of cursor sharing=force/similar (meaning not everything can or should be documented). If you set it to force or similar - you are telling us "remove all literals". If the literals were what was telling us the LENGHT - like

select 'X' from dual;

the literal 'X' tells us 1 character - and you remove them (you told us to) and replace them with placeholders - what else can we do ??? :)

Has it changed?

A reader, May 30, 2012 - 7:24 pm UTC

Hi Tom,

I tried replicating this in Ora11gR2 and didn't see a difference after the alter session. Do you know if this is now changed?

[dev:] /home/oracle>sqlplus scott

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 31 08:17:56 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

scott@DEV> select ',' from dual;

'
-
,

scott@DEV> alter session set cursor_sharing = force ;


Session altered.

scott@DEV> scott@DEV> select ',' from dual;

'
-
,

Tom Kyte
May 31, 2012 - 12:02 am UTC

it just reused the same cursor handle, change the query a teeny tiny bit.

like I did in my original example

ops$tkyte%ORA11GR2> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

ops$tkyte%ORA11GR2> alter session set cursor_sharing = exact /* the default and only correct value! */ ;

Session altered.

ops$tkyte%ORA11GR2> select ',' from dual look_for_me_exact;

'
-
,

ops$tkyte%ORA11GR2> alter session set cursor_sharing = force ;

Session altered.

ops$tkyte%ORA11GR2> select ',' from dual look_for_me_force;

','
--------------------------------
,

ops$tkyte%ORA11GR2> alter session set cursor_sharing = exact /* the default and only correct value! */ ;

Session altered.

ops$tkyte%ORA11GR2> select sql_text from v$sql where sql_text like '% from dual look\_for\_me\_%' escape '\';

SQL_TEXT
-------------------------------------------------------------------------------
select :"SYS_B_0" from dual look_for_me_force
select ',' from dual look_for_me_exact

ops$tkyte%ORA11GR2> 

got it thankyou

A reader, May 31, 2012 - 1:20 am UTC

thanks tom, changed the SQL ever so slightly and see what you mean;