11 Replies Latest reply: Feb 17, 2014 12:29 PM by William Robertson RSS

Substitution Variable

Ashiq Irphan Newbie
Currently Being Moderated
Select *
from employees
where employee_id='&id'
Select *
from employees
where employee_id=&id

I know both the above queries produce the same result, but I would like to know whether the user input is read a numeric or varchar2 or it just depends on the type of the field ?

  • 1. Re: Substitution Variable
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

    Ashiq Irphan wrote:

     

    1. <span><span><span><span><span><span> 
    2. Select
    3. from employees 
    4. where employee_id='&id'</span></span></span></span></span></span> 
    1. <span><span><span><span><span><span> 
    2. Select
    3. from employees 
    4. where employee_id=&id</span></span></span></span></span></span> 

    I know both the above queries produce the same result, but I would like to know whether the user input is read a numeric or varchar2 or it just depends on the type of the field ?

    Use single-quotes around string literals; don't use them around NUMBER literals.  If employee_id is a NUMBER, then use the 2nd form you posted, without the single-quotes.

     

    SQL*Plus reads all substitution variables the same way, regardless of how they are used in your code.  (SQL*Plus actually doesn't know how they are used in your code; it substitutes the values before passing the code to the compiler, which figures out what you are doing.)

  • 2. Re: Substitution Variable
    Etbin Guru
    Currently Being Moderated

    I cannot provide evidence, but experience suggests it's safer to assume substitution variables being character strings and use explicit conversion when dealing with non-character data types

     

    Regards

     

    Etbin

  • 3. Re: Substitution Variable
    Solomon Yakobson Guru
    Currently Being Moderated

    Etbin wrote:

     

    I cannot provide evidence, but experience suggests it's safer to assume substitution variables being character strings and use explicit conversion when dealing with non-character data types

     

     

     

    Substitution variables are typeless. They are applied BEFORE sending code to Oracle for parsing. So it is coder who decides. If employee_id is numeric, then surrounding &1 in quotes is meaningless. Even more, it will degrade performance by a tiny fraction since Oracle will have to convert implicitly string to number.

     

    SY.

  • 4. Re: Substitution Variable
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

    Etbin wrote:

     

    I cannot provide evidence, but experience suggests it's safer to assume substitution variables being character strings and use explicit conversion when dealing with non-character data types

     

    Regards

     

    Etbin

    I'm not sure I agree 100% with your police work there.

     

    Given that employee_id is a NUMBER, are you suggesting:

    Select  * 

    from    employees 

    where   employee_id = TO_NUMBER ('&id');

    ?

    If you weren't using substitution variables, would you do something like this:

    Select  * 

    from    employees 

    where   employee_id = TO_NUMBER ('1234');

    ?  Of course not!   If &id has a value like 1234, then imagine what you would do if you were hard-coding 1234, and then substitute &id in that code where you have 1234.  That's how substitution variables work.  If employee_id is a NIMBER, then a correct way to code it is

     

    Select  * 

    from    employees 

    where   employee_id = &id;

    with no conversion of any kind, either implict or explicit.

  • 5. Re: Substitution Variable
    Etbin Guru
    Currently Being Moderated

    It seems my post wasn't appropriate (didn't pay much attention to the substitution variable included in single quotes).

    I just tried (hopefully still in context) to suggest the mandatory use of explicit conversion as it always worked for me.

     

    Regards

     

    Etbin

  • 6. Re: Substitution Variable
    Etbin Guru
    Currently Being Moderated

    That's what I meant (should have posted it in the first place )

     

    select :a_string string_input,:a_string the_string,

           :a_number number_input,to_number(:a_number) the_number,

           :a_date date_input,to_date(:a_date,'yyyymmdd') the_date

      from dual

     

    STRING_INPUTTHE_STRINGNUMBER_INPUTTHE_NUMBERDATE_INPUTTHE_DATE
    EtbinEtbin123456.789123456.7892014021502/15/2014

     

    Regards

     

    Etbin

  • 7. Re: Substitution Variable
    Solomon Yakobson Guru
    Currently Being Moderated

    Etbin wrote:

     

    That's what I meant (should have posted it in the first place )

     

     

     

    There are no substitution variables in your example. It uses bind variables. And same way you are forcing unnecessary implicit conversions by using varchar2 bind variable and to_number(:a_number).

     

    SY.

  • 8. Re: Substitution Variable
    Etbin Guru
    Currently Being Moderated

    Sorry, I don't think so straight about the difference. One way or another the substitution happens with bind variables too.

    The use to_number conversion seems to be rare but (frankly some rather long time ago) it occurred numbers carrying trailing minus sign were received from somewhere.

     

    Regards

     

    Etbin

  • 9. Re: Substitution Variable
    William Robertson Oracle ACE
    Currently Being Moderated

    I agree it's cleaner to use numeric substitution variables unquoted. The only downside would be if the value was null, in which case the unquoted version would fail with a syntax error.

  • 10. Re: Substitution Variable
    Solomon Yakobson Guru
    Currently Being Moderated

    William Robertson wrote:

     

    I agree it's cleaner to use numeric substitution variables unquoted. The only downside would be if the value was null, in which case the unquoted version would fail with a syntax error.

    Not really. When person executing code knows substitution variable is used in numeric condition he/she should type NULL when prompted, not '' (quote,quote).

     

    SY.

  • 11. Re: Substitution Variable
    William Robertson Oracle ACE
    Currently Being Moderated

    Agreed, that would be a better way to handle it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points