Tuesday, March 29, 2016

Bind Variables

This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:

SQL> variable bv1 varchar2(3)
SQL> variable bv2 number
SQL> begin
  2  select 'ABC' into :bv1 from dual;
  3  select 123 into :bv2 from dual;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print bv1

BV1
--------------------------------
ABC

SQL> print bv2

       BV2
----------
       123

SQL> execute :bv1 := 'XYZ';

PL/SQL procedure successfully completed.

SQL> execute :bv2 := 456;

PL/SQL procedure successfully completed.

SQL> print bv1

BV1
--------------------------------
XYZ

SQL> print bv2

       BV2
----------
       456

SQL>

No comments: