Ittichai: Created page with "

 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..."

2012-09-27T19:16:26Z

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..."

New page

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

</pre>

[[Category: Oracle]]

Bind Variables in Dynamic SQL - Revision history

Ittichai: Created page with "
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..."