Sometimes we might want to print the value of a PL/SQL local variable.
- A “quick-and-dirty” way is to store it as the sole tuple of some table and use a PL/SQL statement to print the table with a SELECT command.
- Another way is to define a bind variable, which is the only kind that may be printed with a PRINT command.
Bind variables are the kind that must be prefixed with a colon in PL/SQL statements, such as :new.
The steps are as follows:
- We declare a bind variable as follows:
VARIABLE <name> <type>
where the type can be only one of three things: NUMBER, CHAR, or CHAR(n). - We may then assign to the variable in a following PL/SQL statement, but we must prefix it with a colon.
- Finally, we can execute a statement
PRINT :<name>;
outside the PL/SQL statement
Here is a trivial example, which prints the value 1.
SQL> VARIABLE x NUMBER SQL> BEGIN :x := 1; END; 2 / PL/SQL procedure successfully completed. SQL> PRINT :x; X ---------- 1
- We declare a bind variable as follows:
| Teens are notorious for feeling a little footloose and fancy-free. |