What is the difference between PLSQL Bind variables and Host variables

Question

With respect to PLSQL, what is the difference between a 'Host Variable' and a 'Bind Variable'?

This link from askTom says that "the distinction blurs in plsql -- its very close to sql". Then what is that tiny, 'blurry' difference?

Statement 1:

SELECT 1 FROM dual WHERE dummy = :B1;

Statement 2:

SELECT 1 FROM dual WHERE dummy = v_var;

In these two statements, Statement 1 represents a bind variable and Statement 2 represents a Host variable, am I correct?

Answer 1

Consider this snippet of C#:

int    v_empno = 7369;
string v_ename;

OracleCommand cmd = con.CreateCommand();
cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, v_empno, ParameterDirection.Input);
cmd.CommandText = "select e.ename from scott.emp e where e.empno = :1";
v_ename = cmd.ExecuteScalar().ToString();

v_empno and v_ename are host variables. Here you explicitly create your bind variable for use as :1 in your the statement.

Consider this snippet of PL/SQL:

declare
   v_empno  number := 7369;
   v_ename  varchar2(10);
begin
   select e.ename
     into v_ename
     from scott.emp e
    where e.empno = v_empno;
   dbms_output.put_line(v_ename);
end;
/

Again the declared variables v_empno and v_ename can be considered host variables, but when they are used in static SQL within the PL/SQL code, they are automatically turned into bind variables by the PL/SQL compiler/engine - you do not have to manually create your bind variable like in the C# example. If you examine the SQL that is actually executed by this piece of PL/SQL, it will look something like this:

   select e.ename
     from scott.emp e
    where e.empno = :B1

That is the PL/SQL compiler that automatically has created :B1 bind variable for your v_empno PL/SQL variable. And that is what Tom Kyte means that you cannot really make a proper distinction between host variable and bind variable in PL/SQL. When you write PL/SQL the variables are host variables when used in PL/SQL code and at the same time they are bind variables when used in the embedded SQL code. You do not need to make a distinction in PL/SQL, the compiler takes care of it for you.

Answer 2

As per documentation

Host variables are the key to communication between your host program and Oracle. Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program. Oracle stores input data in database columns, and stores output data in program host variables.

A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Host arrays of most host variables are also supported.

Bind variables are not simple text values. Their values are sent to the database, and the database can also set their values. PL/SQL itself takes care of most of the issues to do with bind variables. Every reference to a PL/SQL variable is in fact a bind variable.

For example:

int     empno; 
char    ename[10]; 
float   sal; 
... 
EXEC SQL SELECT ename, sal INTO :ename, :sal FROM emp 
     WHERE empno = :empno; 

empno, ename, sal are all Host variables
:empno, :ename, :sal are all bind variables

You can see a similar question here