Sometimes you want to set user variables in a SQL*Plus script that depends on the environment you are working on.
For example; if you are deploying scripts in a development environment and the same scripts on a production environment you would like to set some variables that are used in stored procedures or packages, installation scripts etc.
Instead of prompting for these variables it is easier to set the variables in a script.
This script will do the following:
- first find the name of the current user
- then in PL/SQL, set different values based on the current user
- then convert the bind variable to a user variable
- and define your user variable in the SQL*Plus environment.
VARIABLE vAppId VARCHAR2(50);
SELECT USER INTO vUser FROM DUAL;
IF vUser = 'SCOTT' THEN
:vAppId := '1234';
ELSIF vUser = 'HR' THEN
:vAppId := '5678';
:vAppId := '';
DBMS_OUTPUT.PUT_LINE('No applicationId is set');
COLUMN vappidcol new_value APPLICATION_ID noprint
SELECT :vAppId vappidcol FROM DUAL;
Now you can view your new set variable:
DEFINE _DATE = "08/10/2010 19:24:52" (CHAR)
DEFINE _USER = "HR" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "vim" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
DEFINE APPLICATION_ID = "5678" (CHAR)