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')