|SQL*Plus User's Guide and Reference
Manipulating Commands, 5 of 7
Suppose that you want to be able to display the variables you use in your PL/SQL subprograms in SQL*Plus or use the same variables in multiple subprograms. If you declare a variable in a PL/SQL subprogram, you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL to access the variable from SQL*Plus.
Bind variables are variables you create in SQL*Plus and then reference in PL/SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms.
Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in other PL/SQL subprograms that you run in SQL*Plus.
You create bind variables in SQL*Plus with the VARIABLE command. For example
This command creates a bind variable named ret_val with a datatype of NUMBER. For more information, see the VARIABLE command in Chapter 8. (To list all of the bind variables created in a session, type VARIABLE without any arguments.)
You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example
This command assigns a value to the bind variable named ret_val.
To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example
To declare a local bind variable named id with a datatype of NUMBER, enter
Next, put a value of "1" into the bind variable you have just created:
If you want to display a list of values for the bind variable named id, enter
Try creating some new departments using the variable: