We have seen how to use substitution variables in the previous post. This post is about declaring variables and bind values to it. We use DEFINE (DEF in short) to declare a varible. We can use UNDEF to remove the binding. In the following example, we will declare a variable, bind a value to it. And we will write a procedure to print that variable.
SQL> DEFINE &myname := ‘GRASSFIELD’;
Enter value for myname:
SP2-0553: Illegal variable name “:=”.
SQL> DEFINE myname := ‘GRASSFIELD’;
SP2-0136: DEFINE requires an equal sign (=)
SQL> DEFINE myname = ‘GRASSFIELD’;
SQL> CREATE OR REPLACE PROCEDURE definetest
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘&myname’);
5 END;
6 /
old 4: DBMS_OUTPUT.PUT_LINE(‘&myname’);
new 4: DBMS_OUTPUT.PUT_LINE(‘GRASSFIELD’);
Procedure created.
SQL> exec definetest;
GRASSFIELD
PL/SQL procedure successfully completed.