Bind Variables in Dynamic SQL
Jump to navigation
Jump to search
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')