12 Replies Latest reply: Jul 24, 2013 12:14 PM by Oracle Maniac RSS

    bind variables

    Oracle Maniac

      Hi ,

       

      If i execute this query from my sql*plus client , what is the difference between the substitution and bind variables then ?

       

      select * from emp where empno=&x;

      select * from emp where empno=:x;


      In both the cases , my query which will be sent to the server for execution will be complete (without any placeholder) .


      The only difference I can think of is that we cannot use bind variable in place of table_names & column names but we can use substitution variables.As show below


      select * from  :var where empno=:y--- this wont work when supplied with values dynamically


      select * from  &var  where empno=&y-- This works.

       

       

       

      So what else is the difference between bind and substitution variable then ?

       

      Thanks

        • 1. Re: bind variables
          SomeoneElse

          Substitution variable: resolved on the client

          Bind variable: resolved on the database


          • 2. Re: bind variables
            riedelme

            SomeoneElse wrote:

            Substitution variable: resolved on the client

            Bind variable: resolved on the database


            Also:

            Substitution variable: resolved during compilation (constant at run time)

            Bind variable: resolved at run time

            • 3. Re: bind variables
              Pacmann

              Just to illustrate one implication of what SomeoneElse said : substitute variables are not binded, each execution (with different variable values) will be hard parsed.

               

              SQL> define x = 1

               

              SQL> select * from dual where 1 = &x

                2  /

               

               

              D

              -

              X

               

              SQL> select sql_text from v$sql where sql_text like 'select * from dual%';

               

               

              SQL_TEXT

              -------------------------------------------------------------------------------------------

              select * from dual where 1 = 1

              • 4. Re: bind variables
                Oracle Maniac

                what happens when i use this query in my sql* plus client

                 

                SELECT  * FROM EMP WHERE EMPNO=:X ;  suppose i supply a value 7499 through my sql*plus client . So is it like in the case of bind variable , the moment I pass

                SELECT  * FROM EMP WHERE EMPNO=:X  from my sql prompt , my query is send to the server for parsing (syntactic and semantic check) .When i provide a value ,my value travels to the server over the TCPIP and at runtime  my value is replaced with the placeholder  .Finally my query is executed .

                 

                 

                And ofcourse (the concept of soft parse is associated with bind variables. Just wanted to know at which side (client or server) is my value assigned ,in case of bind  variable)

                • 5. Re: bind variables
                  Rahul_India

                  SomeoneElse wrote:

                   

                  Substitution variable: resolved on the client

                  Bind variable: resolved on the database

                   

                  Ok how resolved on the client?

                   

                  select * from dual where 1 = &x

                  It asks me for the value of x

                  • 6. Re: bind variables
                    Rahul_India

                    ok got it

                    • 7. Re: bind variables
                      Pacmann

                      "set verify off" in SQL*Plus and it won't ask you anymore... (it's my default client setting in my login conf file )

                      • 8. Re: bind variables
                        Rahul_India

                        set define off too?

                         

                        Cn you share your config files details

                        • 9. Re: bind variables
                          Oracle Maniac

                          Can you pls elaborate .I know that bind variables are resolved at the db end. Just for confirmation of what i summarized in my last comment .Thanks

                          • 10. Re: bind variables
                            Oracle Maniac

                            Set verify off is for  hiding the new and the old values which sql* plus shows to the user

                            • 11. Re: bind variables
                              John Spencer

                              When you use a substitiution variable in sqlplus or any other client that supports it, the client interprets the value of the variable and puts it into the sql statement.  So, if you have:

                              select * from emp where emp_id = &n

                              Then the client gets the value you supply and replaces the substitution variable with the value that you supply, so, if you enter 10 the query that the sql engine sees is actually:

                              select * from emp where emp_id = 10

                              That is why you need to put quotes around a substitution variable when you are using it as a string in a projection or predicate.  That is, if you wanted to query by last_name with a substitution variable you would need to do something like:

                              select * from emp where last_name = '&ln'

                              When you use a bind variable like:

                              select * from emp where emp_id = :n

                              the client passes the sql statement exactly as you wrote it and at the same time it also says, by the way, the value for :n is 10.  If you then change the value of :n to 20 the client still passes the exact same sql statement, but this time says the value of :n is 20.

                               

                              As others have pointed out, with a substitution variable every sql statment with a new value for the variable is a brand new, never seen before statement and needs to be hard parsed by the SQL engine.  Every time you use a new value of a bind variable the SQL statment remains the same, so the SQL engine can avoid the (expensive) hard parse.

                               

                              John

                              • 12. Re: bind variables
                                Oracle Maniac

                                Thanks John .This is what i was expecting .