[20180808]Null value to Dynamic SQL.txt

[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