Oracle PL/SQL - Using a NULL bind variable

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL SQL Statement
  4. Dynamic SQL and PL/SQL

Description

Using a NULL bind variable

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  w w . j av a2 s . com

SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.

SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> declare
  2       v_null_nr NUMBER:=NULL;
  3       v_where_tx VARCHAR2(2000):='deptNo=20';
  4       v_sql_tx VARCHAR2(2000);
  5  begin
  6       v_sql_tx:='update emp ' ||
  7                ' set sal=:1, comm=:1 ' ||
  8                'where '||v_where_tx;
  9       execute immediate v_sql_tx
 10                     using v_null_nr, v_null_nr;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>

Related Topic