[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