Difference between revisions of "Bind Variables in Dynamic SQL"
Jump to navigation
Jump to search
(Created page with "<pre> 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 := 'se...") |
(No difference)
|
Latest revision as of 13:16, 27 September 2012
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')
