Bind vs Substitution variables

I always have difficulty remember the difference between these type of
variables. Although now, that I've started doing a lot of ORDS related work, the difference is become more apparent.

Also substitution variables are really a binding to a user variable.

Bind variables

PROMPT bind variables are...
VAR foo VARCHAR2

SET SERVEROUTPUT ON
BEGIN
  :FOO := 'in PL/SQL blocks';
  DBMS_OUTPUT.PUT_LINE('mostly used...');
END;
/
SET SERVEROUTPUT OFF

PRINT foo

output

bind variables are...

PL/SQL procedure successfully completed.

mostly used...


FOO
---
in PL/SQL blocks

Substitution variables

PROMPT Where as substitution variable are...
DEFINE FOO = 'useful in SQL scripts'
SET VERIFY OFF
SELECT '&FOO' AS BAR FROM DUAL;
SET VERIFY ON

output

Where as user variable are...

BAR                 
---------------------
useful in SQL scripts

To be explict, use bind vars to interact with PL/SQL blocks and substituion variables could be used anywhere. The only downside to substituion variables is that you can't DEFINE a variable with another user variable.

Also you can break substitution variables, if there's an errant SET DEFINE OFF, still not sure how you could test for this? Perhaps using default values and testing for it i.e. like SQLCMD variables.

Further reading:

• Literals, Substitution Variables and Bind Variables
• PL/SQL 101 : Substitution vs. Bind Variables