Bind Variables in Dynamic SQL

create or replace procedure pr_bind_test (p_date1 VARCHAR2)
as
   v_query  VARCHAR2(4000) := '';
   v_job    varchar2(50);
BEGIN
   -- Use "bind" variable
   v_query := 'select job from emp  where hiredate > to_date(:1, ''MM/DD/YYYY'')';
 
   dbms_output.put_line (v_query);
  
   execute immediate v_query into v_job using p_date1;
 
   dbms_output.put_line ('Job is ' || v_job);

   -- Use "literal" variable
   v_query := 'select job from emp  where hiredate > to_date(''' || p_date1 ||''', ''MM/DD/YYYY'')';
 
   execute immediate v_query into v_job;
 
   dbms_output.put_line ('Job is ' || v_job);
     
end pr_bind_test;
/
 
-- Executed 5 times with different dates
SQL> exec pr_bind_test('01/07/1983');
PL/SQL procedure successfully completed

SQL> exec pr_bind_test('01/08/1983');
PL/SQL procedure successfully completed

SQL> exec pr_bind_test('01/09/1983');
PL/SQL procedure successfully completed

SQL> exec pr_bind_test('01/10/1983');
PL/SQL procedure successfully completed

SQL> exec pr_bind_test('01/11/1983');
PL/SQL procedure successfully completed
 

SQL> SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE 'select job from emp  where hiredate%';
 
SQL_ID            SQL_TEXT
6k60h9j9rs0gn     select job from emp  where hiredate > to_date('01/10/1983', 'MM/DD/YYYY')
7brd8g41ma49g     select job from emp  where hiredate > to_date(:1, 'MM/DD/YYYY')
3crputj98y84f     select job from emp  where hiredate > to_date('01/08/1983', 'MM/DD/YYYY')
0d51yufy9ky51     select job from emp  where hiredate > to_date('01/07/1983', 'MM/DD/YYYY')
fbc33mykdm30q     select job from emp  where hiredate > to_date('01/11/1983', 'MM/DD/YYYY')
2mtrd4p1trwwx     select job from emp  where hiredate > to_date('01/09/1983', 'MM/DD/YYYY')