BIND VARIABLES
			    --------------
			Written by JP Vijaykumar
			Date       Nov 22nd 2008
create table temp_jp(empno number,ename varchar2(20),hiredate date);
create sequence temp_jp_seq start with 1 increment by 1 nomaxvalue;


declare
v_sql varchar2(2000);
begin
v_sql:='insert into temp_jp values (:1,:2,:3)';
execute immediate v_sql using temp_jp_seq.nextval,'ram',sysdate;
end;  
/
execute immediate v_sql using temp_jp_seq.nextval,'ram',sysdate;
                                          *
ERROR at line 5:
ORA-06550: line 5, column 43:
PLS-00357: Table,View Or Sequence reference 'TEMP_JP_SEQ.NEXTVAL' not allowed
in this context
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored


Can not insert sequence value into table using bind variables
Load temp_jp_seq.nextval into a variable, insert table using bind variables.

declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='select temp_jp_seq.nextval from dual';
execute immediate v_sql into v_num;
v_sql:='insert into temp_jp values (:1,:2,:3)';
execute immediate v_sql using v_num,'ram',to_date('01-02-2008','mm-dd-yyyy');
end;
/

PL/SQL procedure successfully completed.


select * from temp_jp;

     EMPNO ENAME                HIREDATE
---------- -------------------- ---------
         1 ram                  02-JAN-08


declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='insert into temp_jp values (:1,:2,:3)';
execute immediate v_sql using 1,'ram',null;
end; 
/
execute immediate v_sql using 1,'ram',null;
                                      *
ERROR at line 6:
ORA-06550: line 6, column 39:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored


Can not insert a null value into a table using bind variable.
The workaround for inserting nulls into a table are:

declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='select temp_jp_seq.nextval from dual';
execute immediate v_sql into v_num;
v_sql:='insert into temp_jp(empno, ename) values (:1,:2)';
execute immediate v_sql using v_num,'ram';
end;  
/

PL/SQL procedure successfully completed.


select * from temp_jp;

     EMPNO ENAME                HIREDATE
---------- -------------------- ---------
         1 ram                  02-JAN-08
         2 ram


declare
v_sql varchar2(2000);
v_val  varchar2(20):=null;
v_num  number;
begin
v_sql:='select temp_jp_seq.nextval from dual';
execute immediate v_sql into v_num;
v_sql:='insert into temp_jp values (:1,:2,:3)';
execute immediate v_sql using v_num,'ram',v_val;
end;  
/

PL/SQL procedure successfully completed.

select * from temp_jp;

     EMPNO ENAME                HIREDATE
---------- -------------------- ---------
         1 ram                  02-JAN-08
         2 ram
         3 ram



declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='select temp_jp_seq.nextval from dual';
execute immediate v_sql into v_num;
v_sql:='insert into temp_jp values (:1,:2,:3)';
execute immediate v_sql using v_num,'ram','';
end; 
/

PL/SQL procedure successfully completed.

select * from temp_jp;

     EMPNO ENAME                HIREDATE
---------- -------------------- ---------
         1 ram                  02-JAN-08
         2 ram
         3 ram
         4 ram


Some commonly performed operations using bind variables:

set serverout on size 1000000
declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='select count(1) from temp_jp where hiredate is null';
execute immediate v_sql into v_num;
dbms_output.put_line(v_num);
end;   
/
3

PL/SQL procedure successfully completed.


declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='select count(1) from temp_jp where empno > :b1';
execute immediate v_sql into v_num using 1;
dbms_output.put_line(v_num);
end; 
/
3

PL/SQL procedure successfully completed.


declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='update temp_jp set hiredate = sysdate where empno > :b1';
execute immediate v_sql using 1;
end; 
/

PL/SQL procedure successfully completed.

select * from temp_jp;

     EMPNO ENAME                HIREDATE
---------- -------------------- ---------
         1 ram                  02-JAN-08
         2 ram                  22-NOV-08
         3 ram                  22-NOV-08
         4 ram                  22-NOV-08


declare
v_sql varchar2(2000);
v_num  number;
begin
v_sql:='delete from temp_jp where empno = :b1';
execute immediate v_sql using 4;
end; 
/

PL/SQL procedure successfully completed.

select * from temp_jp;

     EMPNO ENAME                HIREDATE
---------- -------------------- ---------
         1 ram                  02-JAN-08
         2 ram                  22-NOV-08
         3 ram                  22-NOV-08