PL/SQL 101 : Substitution vs. Bind Variables — oracle-tech

    Forum Stats

  • 3,708,961 Users
  • 2,241,165 Discussions
  • 7,840,722 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

PL/SQL 101 : Substitution vs. Bind Variables

BluShadowBluShadow Moderator Posts: 40,816 Red Diamond
edited December 2015 in SQL & PL/SQL

PL/SQL 101 : Substitution vs. Bind Variables

(adapted from the thread: https://community.oracle.com/thread/2477546)

Author: @BluShadow
Last Updated: 27/05/2015


Introduction

We frequently get posts on the community where either people don't understand the difference between a substitution variable and a bind variable, or people fail to understand why they can't use the "&" substitution variables in their PL/SQL procedures and functions to prompt for input at run time.  This article will hopefully help clarify in your mind what the differences are so that you can understand where and when to use these.

1. Substitution Variables

The clue here is in the name... "substitution". It relates to values being substituted into the code before it is submitted to the database.

These substitutions are carried out by the interface being used. In this example we're going to use SQL*Plus as our interface...


So let's take a bit of code with substitution variables:

create or replace function myfn return varchar2 is
  v_dname varchar2(20);
begin
  select dname
  into   v_dname
  from   dept
  where  deptno = &p_deptno;
  return v_dname;
end;

Now when this code is submitted...

SQL> /

SQL*Plus, parses the code itself, and sees the "&" indicating a substitution variable.
SQL*Plus, then prompts for a value for that variable, which we enter...

Enter value for p_deptno: 20
old   7:   where  deptno = &p_deptno;
new   7:   where  deptno = 20;

... and it reports back that it has substituted the &p_deptno variable for the value 20, actually showing us the whole line of code with it's value.

This code is then submitted to the database. So if we look at what code has been created on the database we see...

SQL> select dbms_metadata.get_ddl('FUNCTION', 'MYFN', USER) from dual;
DBMS_METADATA.GET_DDL('FUNCTION','MYFN',USER)
-------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION "SCOTT"."MYFN" return varchar2 is
  v_dname varchar2(20);
begin
  select dname
  into   v_dname
  from   dept
  where  deptno = 20;
  return v_dname;
end;


The database itself knows nothing about any substitution variable... it just has some code, fixed with the value we supplied to SQL*Plus when we compiled it.

The only way we can change that value is by recompiling the code again, and substituting a new value for it.

Also, with substitution variables we don't necessarily have to use them just for 'values' (though that it typically what they're used for)... we can use them to substitute any part of the code/text that we are supplying to be compiled.. e.g.

create or replace function myfn(x in number, y in number) return number is
begin
  return &what_do_you_want_to_return;
end;
/
Enter value for what_do_you_want_to_return: y*power(x,2)
old   3:   return &what_do_you_want_to_return;
new   3:   return y*power(x,2); Function created. SQL> select dbms_metadata.get_ddl('FUNCTION', 'MYFN', USER) from dual; DBMS_METADATA.GET_DDL('FUNCTION','MYFN',USER)
-------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION "SCOTT"."MYFN" (x in number, y in number) return number is
begin
  return y*power(x,2);
end;

It really does substitute the substitution variable, with whatever text you supply.

2. Bind Variables

Bind variables are a completely difference concept to substitution variables.

Bind variables typically relate to SQL queries (they can be used in dynamic PL/SQL code, but that's not good practice!), and are a placeholder for values within the query. Unlike substitution variables, these are not prompted for when you come to compile the code.

Now there are various ways of supplying bind variables, and I'll use a couple of examples, but there are more (such as binding when creating queries via the DBMS_SQL package etc.)

In the following example:

create or replace function myfn(p_deptno in number) return varchar2 is
  v_dname varchar2(20);
  v_sql   varchar2(32767);
begin
  v_sql := 'select dname from dept where deptno = :1';
  execute immediate v_sql into v_dname using p_deptno;
  return v_dname;
end;
/ Function created.


The ":1" is the bind variable in the query.

If you examine queries running in the database you will typically see bind variables represented as :1, :2, :3 and so on, though it could be anything preceded by a ":" such as :A, :B, :C, :X, :FRED, :SOMETHING etc.

When the query is passed to the SQL engine (in this case by the EXECUTE IMMEDIATE statement), the query is parsed and optimised and the best execution plan determined. It doesn't need to know what that value is yet to determine the best plan. Then when the query is actually executed, the value that has been bound in (in this case with the USING part of the execute immediate statement) is used within the execution of the query to fetch the required data.

The advantage of using bind variables is that, if the same query is executed multiple times with different values being bound in, then the same execution plan is used because the query itself hasn't actually changed (so no hard parsing and determining the best plan has to be performed, saving time and resources).

Another example of using bind variable is this:

create or replace function myfn(p_deptno in number) return varchar2 is
  v_dname varchar2(20);
begin
  select dname
  into   v_dname
  from   dept
  where deptno = p_deptno;
  return v_dname;
end;
/ Function created.

Now, this isn't immediately obvious, but what we have here is the ability of the PL language to seamlessly integrate SQL within it (giving us PL/SQL). It looks as though we just have an SQL statement in our code, but in reality, the PL engine parses the query and supplies the query to the SQL engine with a bind variable placeholder for where the PL variable (parameter p_deptno in this case) is within it. So the SQL engine will get a query like...

select dname
from   dept
where  deptno = :1

and then the PL engine will handle the binding of the value (p_deptno) into that query when it executes it, as well as dealing with the returning value being put INTO the PL variable v_dname. Again, the SQL supplied to the SQL engine can be optimised and re-used by code because it isn't hard coded with values.

So, here, the binding of values is implicit because the PL engine is removing the need for us to have to code them explicitly.

The other advantage of using bind variables is that you don't have to worry about the datatypes.

Going back to our dynamic SQL example, we often we see people creating code such as this (actually we see them posting it as it's not working for them, because they don't understand bind variables and datatypes)...

create or replace function myfn(p_hiredate in date) return number is
  v_empno number;
  v_sql   varchar2(32767);
begin
  v_sql := 'select empno from emp where hiredate = to_date('''||to_char(p_hiredate,'DD/MM/YYYY')||''',''DD/MM/YYYY'')';
  execute immediate v_sql into v_empno;
  return v_empno;
end;
/ Function created.

The developer is trying to concatenate in a date or varchar variable with the appropriate single quotes and formatting required to make the SQL make sense. Not only does that prevent the SQL explain plan from being re-used with different values, but it makes the code hard to maintain and get right in the first place (as well as leaving things open to SQL injection in some cases)

But, with bind variable, that's not necessary... simply doing...

create or replace function myfn(p_hiredate in date) return number is
  v_empno number;
  v_sql   varchar2(32767);
begin
  v_sql := 'select empno from emp where hiredate = :1';
  execute immediate v_sql into v_empno using p_hiredate;
  return v_empno;
end;
/ Function created.

... is all that is needed.

The SQL engine knows that it is expecting a DATE datatype for the value because of what it's being compared against, and the USING statement is supplying a value of DATE datatype. There's no need to mess around with date formats or multiple quotes etc. it just simply works. The same applies with other datatypes.

3. Hard vs. Soft Parsing - The benefits of Binding

As a final demonstration for this article, I'll demonstrate what I've talked about above; how using bind variable correctly will allow the SQL engine to re-use the execution plans it's already worked out for a query, even if the values being bound in change.

Off-screen, I've created a quick procedure called "show_sqls" on my database which basically queries the v$sqltext view to display what SQL plans are stored in the database.  I won't detail the actual procedure here, you can easily search the web for v$sqltext and find plenty of examples.  All my procedure is going to show is the ID and Hash Value of the SQL statement and the execution plan, and the SQL text itself...

Firstly then, let's create a PL/SQL procedure that doesn't bind variables properly into a dynamic SQL statement (how NOT to do it!):

create or replace procedure myproc(deptno in number) is
  cnt       number;
  sqltext   varchar2(32767);
begin
  sqltext := 'select /* WITHOUTBINDING */ count(*) from emp where deptno = '||to_char(deptno);
  execute immediate sqltext into cnt;
end;
/ Procedure created.

Here you can see we're just going to concatenate the department number value that's passed in, to the end of the sql string.


So, let's call that procedure with our first parameter value of 10...

SQL> exec myproc(10);

PL/SQL procedure successfully completed.



The procedure executes successfully, so let's see what query was stored on the database...

SQL> exec show_sqls('%* WITHOUTBINDING *%');
SQL ID: 5tf6vt1wa4ju3  - Hash Value: 2023900995 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 10 PL/SQL procedure successfully completed.

Here you can see the SQL text that's been stored has the department number included as part of the SQL string.
Let's call our procedure again with a different value, and see what's stored in the database...

SQL> exec myproc(20);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHOUTBINDING *%');
SQL ID: 5tf6vt1wa4ju3  - Hash Value: 2023900995 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 10
SQL ID: 4t32471jpz4us  - Hash Value: 1667208024 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 20 PL/SQL procedure successfully completed.

Ok, so now you can see the database has stored two different SQL's (one for each of the values), each with their own SQL ID, and each with their own Hash Value, indicating that they will each have their own execution plan, and each one will have had to be hard parsed to determine that plan.

Once more...

SQL> exec myproc(30);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHOUTBINDING *%');
SQL ID: 04hhpu038hh4m  - Hash Value: 109592723 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 30
SQL ID: 5tf6vt1wa4ju3  - Hash Value: 2023900995 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 10
SQL ID: 4t32471jpz4us  - Hash Value: 1667208024 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 20 PL/SQL procedure successfully completed.

That's proof enough that each time we are executing our procedure with a different value, the database is having to hard parse the query and store a new execution plan for it.


Now, Let's change our procedure to correctly use bind variables...

create or replace procedure myproc(deptno in number) is
  cnt       number;
  sqltext   varchar2(32767);
begin
  sqltext := 'select /* WITHBINDING */ count(*) from emp where deptno = :1';
  execute immediate sqltext into cnt using deptno;
end;
/

As you can see, we've used a bind variable in the SQL string, and we're passing the value in using the USING clause of our execute immediate statement.  Let's see what happens...

SQL> exec myproc(10);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHBINDING *%');
SQL ID: csrv5d64p1v0x  - Hash Value: 2303781917 - SQL: select /* WITHBINDING */ count(*) from emp where deptno = :1 PL/SQL procedure successfully completed.


Sure enough, our procedure executed ok, and we have an SQL statement stored in the database, with it's own ID and Hash Value.  Notice that the SQL Text for this statement still shows our bind variable ":1", and not the value that was passed in when it executed.

Let's call it again with another value...

SQL> exec myproc(20);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHBINDING *%');
SQL ID: csrv5d64p1v0x  - Hash Value: 2303781917 - SQL: select /* WITHBINDING */ count(*) from emp where deptno = :1 PL/SQL procedure successfully completed.


The procedure executed ok with our different value, BUT this time we still only have a single SQL statement stored in the database.  Even though the value was different, the SQL engine was able to determine that it already has an execution plan for our SQL statement, because nothing has changed with the statement itself; this is soft parsing rather than having to re-determine another execution plan, and thus the same execution took place as before, with our different value bound in at the time it actually executed the statement.

One last time...

SQL> exec myproc(30);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHBINDING *%');
SQL ID: csrv5d64p1v0x  - Hash Value: 2303781917 - SQL: select /* WITHBINDING */ count(*) from emp where deptno = :1 PL/SQL procedure successfully completed.

Sure enough, regardless of the value we pass in, the SQL engine is able to re-use the same statement over and over again, saving it time and effort, as well as the resources for storing multiple SQL statements.

In real applications, where multiple users are hitting the same SQL statements against the database, but just for different search criteria, using bind variables is going to help the database resources as well as improve performance.

Summary

So, in summary, Substitution variables are variables that the user interface detects and prompts for text to substitute into the code before submitting it to the database, and Bind variables are placeholders in queries that allow SQL queries to be soft parsed rather than hard parsed when the query is re-used, help prevent SQL injection, and allow for the values to be supplied easily and seamlessly within the code issuing it.

Comments

  • Nicks123Nicks123 Posts: 37 Red Ribbon
  • jaramilljaramill Posts: 4,298 Gold Trophy

    Excellent article BluShadow.  I use substitution variables in combination with my Unix shell scripting and dynamically creating SQL scripts (not to be confused with dynamic sql) but using a substitution variable for different schema names.

    BluShadowAnuprasad R
  • Solomon YakobsonSolomon Yakobson Posts: 17,946 Black Diamond

    Nice article.

    Just one suggestion. I'd change comments BADBINDING and GOODBINDING to WITHOUTBINDING and WITHBINDING. BADBINDING could be a bit misleading since there is no binding in that SQL at all. Same way GOODBINDING is misleading too. Binding isn't good or bad - we either use binding or not.

    SY.

    Vysakh Suresh - 3035408
  • Vysakh Suresh - 3035408Vysakh Suresh - 3035408 Posts: 1,239 Gold Badge

    Simple, sound and well written document.. waiting for the next one from you..;)

  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond

    Nice article.

    Just one suggestion. I'd change comments BADBINDING and GOODBINDING to WITHOUTBINDING and WITHBINDING. BADBINDING could be a bit misleading since there is no binding in that SQL at all. Same way GOODBINDING is misleading too. Binding isn't good or bad - we either use binding or not.

    SY.

    Solomon Yakobson wrote:
    
    Nice article.
    
    Just one suggestion. I'd change comments BADBINDING and GOODBINDING to WITHOUTBINDING and WITHBINDING. BADBINDING could be a bit misleading since there is no binding in that SQL at all. Same way GOODBINDING is misleading too. Binding isn't good or bad - we either use binding or not.
    
    SY.
    

    That's a fair point Solomon, so I've now updated those comments.

  • Geert GruwezGeert Gruwez Posts: 1,011

    very good article.

    I'd add a note on "set define off" and "set define on" and how that impacts the substitutions

  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond

    very good article.

    I'd add a note on "set define off" and "set define on" and how that impacts the substitutions

    Certainly something I may consider, however those commands are specific to the SQL*Plus tool, so aren't necessary relevant if someone is using another tool such as Toad, PL/SQL Developer or SQL Developer etc. as they have their own ways of turning substitution variable detection on/off (in some cases it depends on whether the script is run directly in the tool or "run as script" as well)

  • Gaz in OzGaz in Oz Posts: 3,752 Bronze Crown

    Great article.

    As I read thru, I was reminded of how "CURSOR_SHARING" influences parsing/bind variables and how "CURSOR_SHARING=FORCE" can be a dangerous thing, and may be used as a quick (dirty) fix, when in actual fact the query/application itself needs fixing, not permanently setting a db parameter for what should be only temporary work around at best. Might be worth mentioning the pitfalls/traps of setting this parameter to FORCE.

    Cheers,

    Gaz.

    BluShadow
  • Stefan JagerStefan Jager Posts: 1,746 Silver Trophy

    Brilliant as usual Blu. As always I'm learning from your articles: I didn't know that the PL engine was clever enough to implicitly create bind variables (so I always wrote them myself to make sure). Nice to know! Makes code quite a bit more readable imho.

    BluShadow
  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond

    Great article.

    As I read thru, I was reminded of how "CURSOR_SHARING" influences parsing/bind variables and how "CURSOR_SHARING=FORCE" can be a dangerous thing, and may be used as a quick (dirty) fix, when in actual fact the query/application itself needs fixing, not permanently setting a db parameter for what should be only temporary work around at best. Might be worth mentioning the pitfalls/traps of setting this parameter to FORCE.

    Cheers,

    Gaz.

    So very true Gaz, however as it's a 101 article just giving the basics for beginners, best not confuse people with things they don't understand.  The problem with telling people about what they shouldn't do, is that they then go and try to do it and use it without fully understanding the implications.

    Don't think of a banana.  Oops you just did. 

  • Greetings,

    I have a simple question,

    ----code below----

    DECLARE

    V_ONE int;

    BEGIN

    V_ONE:=1;

    IF V_ONE=1 THEN

       &ONE;

    ELSE

       &TWO;

    END IF;

    END;

    ---

    The upper code, should prompt only once for &one right?

    But seems its not the situation, after i give value to &one and press enter another prompt &two also gets called.

    What is the logic for using substitution vairables in if...else conditions?

    Sanjiv Ranjit

  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond

    Greetings,

    I have a simple question,

    ----code below----

    DECLARE

    V_ONE int;

    BEGIN

    V_ONE:=1;

    IF V_ONE=1 THEN

       &ONE;

    ELSE

       &TWO;

    END IF;

    END;

    ---

    The upper code, should prompt only once for &one right?

    But seems its not the situation, after i give value to &one and press enter another prompt &two also gets called.

    What is the logic for using substitution vairables in if...else conditions?

    Sanjiv Ranjit

    If you read the above article you'll come to understand why.

    In short, you are using "substitution variables" in your code.  At the point you submit this code, the code hasn't even reached the database, and is NOT being executed as code.  Before the code gets submitted to be executed by the database, the interface (e.g. SQL*Plus) wants to substitute the substitution variables you've specified for actual values/text, so it will prompt you for ALL of those variables, before the code is then submitted to be executed.  Only once they've all be substituted will the code execute, and the IF statement determine that it needs to execute whatever code is substituted in place of &ONE;

    L. FernigriniRyan O. Lee
  • VikroniumVikronium Posts: 25 Bronze Badge
  • Excellent Post.

    Substitution and input bind variables are both methods for replacing a variable in a query or procedural code with a corresponding value from your program. Some databases call bind variables "parameters".

    Thanks For Sharing..!

  • User_OLK44User_OLK44 Posts: 8 Red Ribbon

    Another example of using bind variable is this:


    create or replace function myfn(p_deptno in number) return varchar2 is

     v_dname varchar2(20);

    begin

     select dname

     into  v_dname

     from  dept

     where deptno = p_deptno;

     return v_dname;

    end;

    /


    Function created.


    Now, this isn't immediately obvious, but what we have here is the ability of the PL language to seamlessly integrate SQL within it (giving us PL/SQL). It looks as though we just have an SQL statement in our code, but in reality, the PL engine parses the query and supplies the query to the SQL engine with a bind variable placeholder for where the PL variable (parameter p_deptno in this case) is within it. So the SQL engine will get a query like...


    select dname

    from  dept

    where deptno = :1


    #################################

    If above is the case, then why developers needs to explicitly use bind variables in code to prevent SQL injection.

  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond

    If above is the case, then why developers needs to explicitly use bind variables in code to prevent SQL injection.


    If you understand how SQL injection works, then when someone just concatenated data together to create a dynamic SQL statement, it's possible for someone to turn that one statement in to multiple statements or include additional columns etc. That's a bit like you could do with the substitution variables.

    With Bind variables, the database will quite literally take whatever value is supplied and treat it as an atomic value within the statement. Someone trying to provide a value (especially for strings) that contained other columns or statement terminators with further statements, would actually just be providing a value for the comparison which wouldn't match.

    So, in a simplistic sense, if you had:

    sql := 'select name from employee where employee_id = '||emp_id;
    execute immediate sql;
    

    and emp_id was a string, then someone could supply a value like '123; delete from password;'

    resulting in a dynamic statement to be executed:

    select name from employee where employee_id = 123; delete from password;
    

    which could have devastating effects.

    Using bind variables:

    select name into v_name from employee where employee_id = emp_id;
    

    someone supplying that same value would just get no data back, because oracle would consider the query to be:

    select name from employee where employee_id = :1
    

    internally, and the value of :1 would be the supplied string, with no employee_id matching '123; delete from password'

  • User_OLK44User_OLK44 Posts: 8 Red Ribbon

    Thats a great explaination.

    Few more followup questions.

    1. So SQL injection impacts only concatenated strings ? and NOT the sqls in which only some data values are passed from variables( like in stored procedures) used maybe in select part or where condition ( Not concatenated  || )
    2. Can we say every PLSQL variable is a bind variable and we do NOT need to write it explicity using colon(:) syntax if we are not using concatenated strings in PLSQL procedure code ?
  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond
    1. Typically, yes.
    2. Yes, PL/SQL variables used in SQL statements are automatically bound in to the query behind the scenes. The ":" binding is only needed where the query is dyanamically generated, so is therefore a string because you're indicating it as a placeholder to be bound in later.
  • User_OLK44User_OLK44 Posts: 8 Red Ribbon

    Kindly help me with a way to use bind variable in utl_file.fopen, i have a vulnerability in my stored procedure code because of below statement.

     infile_handle := UTL_FILE.FOPEN(i_directory, i_inFileName, 'R');

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

    I tried to change it like below but it doesn't work --

      vsql := 'UTL_FILE.FOPEN(:1, :2, ''R'')';

    EXECUTE IMMEDIATE vsql INTO infile_handle USING i_directory, i_inFileName;

  • Billy VerreynneBilly Verreynne Posts: 27,975 Red Diamond

    Please do not double post questions. (https://community.oracle.com/tech/developers/discussion/4477277/how-to-use-bind-variable-in-utl-file-fopen)

    Your dynamic call is a POL/SQL call - not SQL. It needs to be an anonymous PL/SQL block. It is not.

    BluShadow
Sign In or Register to comment.