Posts Tagged ‘sqlplus’

Set user variable in SQL*Plus based on current user

Friday, October 8th, 2010

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>

Command history in sqlplus using a linux shell: rlwrap

Saturday, April 10th, 2010

When using the Oracle sqlplus command on a Unix of Linux machine or when running Cygwin on a Windows machine, you will lack the use of the command history that you will have when you run sqlplus on a Windows machine.
You can solve this by installing a small utility and use this small utility to call sqlplus.

The program is called “rlwrap”. This is a read-line wrapper, and it allows the editing of keyboard input for any other command. It maintains a separate input history for each command, and can tab-expand words.

How to use it:

  • First download rlwrap and install it.
  • Then in your .bashrc file add an alias for sqlplus: alias sqlplus=’rlwrap sqlplus’
  • And on the command prompt you can now use sqlplus with a command history.