Use of Bind Variables in Dynamic SQLs

One need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL. It, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. We need to avoid the hard parse when it is submitted:

"create or replace procedure dsal(p_empno in number)
as
begin
execute immediate 'update emp set sal = sal*2 where empno = '||p_empno; commit;
end;

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:
create or replace procedure dsal(p_empno in number) as
begin
execute immediate 'update emp set sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/"