cursor_sharing=force影响出报表

July 15th, 2009 | Categories: Boring | Tags:
Leave a comment | Trackback

cursor_sharing=force的时候，当SELECT中有lietral string做查询列的时候,因为要被替换成:SYS_01等系统绑定变量，可能由于是:SYS_01有默认的string buffer,输出报表的时候列长度就改变了。

SQL> select 123 num from dual;
NUM
———-
123
SQL> select ‘123′ num from dual;
NUM
—
123

SQL> alter session set cursor_sharing=force;
Session altered.

SQL> select ‘456′ num from dual;
NUM
——————————–
456

SQL> select 456 num from dual;
NUM
———-
456

不过，可以用cast和format来限制

SQL> select cast(’abc’ as char(3)) from dual;
CAS
—
abc
SQL> col NUM format a3
SQL> select ‘789′ num from dual;
NUM
—
789

也有肯能导致一些细微的错误。

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. Yong Huang
    July 16th, 2009 at 22:52
    Quote | #1

    Interesting. Also, you can use cursor_sharing_exact hint to temporarily change back to no bind variable replacement:

    alter session set cursor_sharing=force;
    select /*+ cursor_sharing=force */ ‘456′ num from dual;

    The result will show 3 dash characters instead of 32 under the column heading, because ‘456′ won’t be expanded to :”SYS_B_0″ as you explained.