[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