Set user variable in SQL*Plus based on current user

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.
HR>

VARIABLE vAppId VARCHAR2(50);
DECLARE
    vUser VARCHAR2(50);
BEGIN

    SELECT USER INTO vUser FROM DUAL;

    IF vUser = 'SCOTT' THEN
        :vAppId := '1234';
    ELSIF vUser = 'HR'  THEN
        :vAppId := '5678';
    ELSE
        :vAppId := '';
        DBMS_OUTPUT.PUT_LINE('No applicationId is set');
    END IF;
END;
/
COLUMN vappidcol new_value APPLICATION_ID noprint
SELECT :vAppId vappidcol FROM DUAL;

HR>

Now you can view your new set variable:

HR>define

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)

HR>

Related posts

Tags: ,

2 Responses to “Set user variable in SQL*Plus based on current user”

  1. Anita Welm-Grosvenor says:

    Thanks for this example.

    This was exactly what I needed for my roll-out to production. My developers have added this functionality in the login.sql.

    Regards, Anita

  2. Oliver says:

    Thanks for sharing this. I have been struggling with converting a variable in PLSQL in a SQLPLUS variable. Thanks again for this short but very instructive example!

    Oliver Wright

Leave a Reply