4 Replies Latest reply: Dec 28, 2012 6:33 AM by APC RSS

    execute immediate with using clause

    886282
      Hi, If we can bind the oracle parameters into the execute immediate statement then, why Oracle is having Using clause to it.
      what is the use of this using clause.
        • 1. Re: execute immediate with using clause
          APC
          When you say "bind" I think you mean something other than what most people understand when they hear "bind" in the context of variables.

          Yes we can include variables in a dynamically executed string like this:
          execute immediate 'select * from emp where deptno = '|| v_deptno;
          However, this actually treats the variable as a hard-coded value in a literal string. The statement is hard parsed each time it is executed.

          Whereas, the USING clause allows us to specify placeholders in the code, and assign the values through the USING clause like this:
          execute immediate 'select * from emp where deptno = :1' using v_deptno;
          Now the statement is executed with a bind variable, which means it is only hard-parsed on the first run. This is more efficcient for code which is executed a lot.

          Cheers, APC
          • 2. Re: execute immediate with using clause
            886282
            But still even Its performance Issue also, I have seen so many Execute Immediate instances that these are used by binding with less parameters only,
            So still I feel with few binding parameters vs string execution as you told , will not make much difference. Don't know I feel there some thing else is missing there.
            • 3. Re: execute immediate with using clause
              BluShadow
              883279 wrote:
              But still even Its performance Issue also, I have seen so many Execute Immediate instances that these are used by binding with less parameters only,
              So still I feel with few binding parameters vs string execution as you told , will not make much difference. Don't know I feel there some thing else is missing there.
              I think it's your understanding that is what is missing.

              Many people work on multi-user high transaction database applications. These application have to issue the same statements multiple times using different values.

              If you don't bind your values into the statements, but instead concatenate them in, then you end up causing a hard parsing of the statement each time, which not only takes up time (causing performance to be slower), but also takes up server resources to do that parsing, as well as extra resources to store the execution plans. You see, as queries etc. are executed, Oracle tries to keep them (and their execution plans etc.) in memory, so that as it gets more similar requests it can use the same plans and offer the best performance. If the queries are not using bind variables, then the list of queries it is holding onto, all look like unique queries to it, and nothing ever gets re-used, and the database has to perform more work doing 'garbage collection' to get rid of old queries it thinks aren't needed to make way for the new ones, when in fact they really should be the same query being re-used. By not using bind variables, you are preventing the database from performing optimally... so why would you do that when the database has been designed to offer you the best performance it can if you use it correctly?

              Concatenating values into a query string, rather than using bind variables, is also a good way to allow SQL injection issues, meaning you are introducing security issues into your code. It also means that the datatypes of the bind variables are not being used correctly, so the dynamic strings being built up, have to try and do the datatype conversions within the string rather than allowing oracle to correctly bind in the correct datatypes itself.

              e.g.

              A badly written query that doesn't use bind variables may look something like this...
              SQL> ed
              Wrote file afiedt.buf
              
                1  declare
                2    v_sql   varchar2(32767);
                3    v_date  date := to_date('19-APR-1987','DD-MON-YYYY');
                4  begin
                5    v_sql := 'select * from emp where hiredate = to_date('''||to_char(v_date,'DD/MM/YYYY')||''',''DD/MM/YYYY'')';
                6    execute immediate v_sql;
                7* end;
              SQL> /
              
              PL/SQL procedure successfully completed.
              ... here the DATE value had to be converted to a string so it could be concatenated into the query string, and then the query itself had to account for it being a string, and do a TO_DATE within the query to convert it to a DATE datatype for the query to execute, all with a lot of messing around with multiple quotes to build up the SQL.

              Yet, if a bind variable had been used...
              SQL> ed
              Wrote file afiedt.buf
              
                1  declare
                2    v_sql   varchar2(32767);
                3    v_date  date := to_date('19-APR-1987','DD-MON-YYYY');
                4  begin
                5    v_sql := 'select * from emp where hiredate = :1';
                6    execute immediate v_sql using v_date;
                7* end;
              SQL> /
              
              PL/SQL procedure successfully completed.
              ... there was no need to convert the DATE to a string, and the query didn't need to do a TO_DATE on anything. In this case Oracle knows we are passing in a DATE datatype and correctly bind in the value of that date to compare it to the hiredate as a DATE datatype. The code is cleaner, less prone to bugs, and the datatypes are handled correctly by Oracle.

              To put it simply, if you choose not to use bind variables, then you're an idiot. They are there to be used for a reason.
              • 4. Re: execute immediate with using clause
                APC
                883279 wrote:
                But still even Its performance Issue also, I have seen so many Execute Immediate instances that these are used by binding with less parameters only,
                Let's start from first principles. The times when it is truly necessary to use dynamic SQL are few and far between. If the only variables in the dynamic string are bind variables as in the example I gave, there is no need to use dynamic SQL. That is, this code is bad :
                execute immediate 'select * from emp where deptno = :1' using v_deptno into emp_recs;
                and it should be replaced with this code:
                select * bulk collect into emp_recs from emp where deptno = v_deptno;
                The proper use of dynamic SQL for executing DML is when we want to vary the table's name or projection. We can't use bind variables for database objects in our dynamic statement so we have to concatenate them into the string.
                execute immediate 'select * from '|| v_table_name ||' where deptno = :1' using v_deptno into emp_recs;
                This doesn't invalidate the use of bind variables, as there is still a performance benefit to be gained from restricting the number of hard parses (in this case to the number of distinct values in the V_TABLE_NAME variable).

                The best practice is, always use bind variables unless you have a very good reason not to.

                Cheers, APC