DEV Community

Mark Sta Ana
Mark Sta Ana

Posted on • Originally published at booyaa.wtf on

Bind vs Substitution variables

#plsql #oracle

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
Enter fullscreen mode Exit fullscreen mode

output

bind variables are...

PL/SQL procedure successfully completed.

mostly used...


FOO
---
in PL/SQL blocks
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

output

Where as user variable are...

BAR                 
---------------------
useful in SQL scripts
Enter fullscreen mode Exit fullscreen mode

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:

Top comments (0)