[20240312]sqlplus define数据类型问题.txt

[20240312]sqlplus define数据类型问题.txt

--//编写sql脚本遇到的问题，通过例子说明。

1.环境：
SCOTT@book> @ ver1 111
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//定义参数1为111

2.问题再现：

SCOTT@book> define 1
DEFINE 1               = "111" (CHAR)
--//你可以发现参数1定义为char类型,也就是sqlplus即使你定义类型是数字,也是当作字符类型。

SCOTT@book> define a=1111111
SCOTT@book> define a
DEFINE A               = "1111111" (CHAR)
--//即使定义a=1111111,sqlplus还是把它当作字符类型。

SCOTT@book> column dbid new_value a;
SCOTT@book> select dbid from v$database;
      DBID
----------
1337401710

SCOTT@book> define a
DEFINE A               = 1337401710 (NUMBER)

--//通过这样的方式转换number类型。

4.继续测试：
SCOTT@book> spool b&a
--//没有报错!!
SCOTT@book> spool off

SCOTT@book> select inst_id dbid from gv$database;
      DBID
----------
         1

SCOTT@book> define a
DEFINE A               =          1 (NUMBER)

SCOTT@book> spool b&a
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

SCOTT@book> prompt b&a
b         1

SCOTT@book> select 'b&a' from dual ;
'B1'
-----------
b         1

--//可以发现前面存在空格。前面使用dbid没有错，因为长度已经超过9个字符。

SCOTT@book> select dbid from v$database;
      DBID
----------
1337401710

SCOTT@book> select 'b&a' from dual ;
'B133740171
-----------
b1337401710

--//我编写sql代码时，使用spool时遇到这个问题。
--//结果方法很简单转换成字符类型就可以了。

SCOTT@book> select to_char(inst_id) dbid from gv$database;
DBID
----------------------------------------
1

SCOTT@book> prompt b&a
b1


--//补充说明一下假设参数带双引号，要再使用双引号转义一下，例子：

> @ ver1  "use_concat(@""SEL$1"" 8 OR_PREDICATES(2))"
> define 1
DEFINE 1               = "use_concat(@"SEL$1" 8 OR_PREDICATES(2))" (CHAR)

--//单引号没有这个问题。
> @ ver1  "use_concat(@'SEL$1' 8 OR_PREDICATES(2))"
> define 1
DEFINE 1               = "use_concat(@'SEL$1' 8 OR_PREDICATES(2))" (CHAR)