ITPub博客

首页 > 数据库 > Oracle > [20180808]Null value to Dynamic SQL.txt

[20180808]Null value to Dynamic SQL.txt

原创 Oracle 作者:lfree 时间:2018-08-10 09:27:54 0 删除 编辑

[20180808]Null value to Dynamic SQL.txt


SCOTT@book> @ &r/ver1

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


SCOTT@book> create table t1( id number,name varchar2(10));

Table created.


insert into t1 values (1,'a');

insert into t1 values (2,'b');

commit ;


begin

execute immediate 'update t1 set name = :1' using null;

end;

/


execute immediate 'update t1 set name = :1' using null;

                                                  *

ERROR at line 2:

ORA-06550: line 2, column 51:

PLS-00457: expressions have to be of SQL types

ORA-06550: line 2, column 1:

PL/SQL: Statement ignored


--//主要原因是这里的NULL不知道什么类型,只要定义类型就ok了.

--//修改如下:


begin

execute immediate 'update t1 set name = :1' using cast(null as varchar2(10));

end;

/

execute immediate 'update t1 set name = :1' using cast(null as varchar2(10));

                                                                       *

ERROR at line 2:

ORA-06550: line 2, column 72:

PLS-00103: Encountered the symbol "(" when expecting one of the following:

. ) @ %


--//依旧不行.视乎在这里不能使用括号指定长度.

begin

execute immediate 'update t1 set name = :1' using cast(null as varchar2);

end;

/


--//这样ok!

SCOTT@book> select * from t1;


        ID NAME

---------- --------------------

         1

         2

SCOTT@book> rollback;

Rollback complete.


--//当然也可以这样写:

declare

v_name varchar2(10);

begin

v_name := null;

execute immediate 'update t1 set name = :1' using v_name;

end;

/


SCOTT@book> select * from t1;

        ID NAME

---------- --------------------

         1

         2


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2199613/,如需转载,请注明出处,否则将追究法律责任。

上一篇: [20180810]gpnptool.txt
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2242
  • 访问量
    5979486