Change SQLPlus color according to database name

I am in and out of lots of databases every day. I wanted a way to color code my session so that I could tell at a glance if I was logged on to production or non-production. So  I added this to my login.sql file:


set termout off
set timing off
COLUMN  host_cmd_col  NEW_VALUE host_cmd

          when sys_context('USERENV', 'DB_NAME')
          in (  'PROD', 'IPROD', 'KPROD',     -- rac instances
                'PA', 'PALP', 'PP2', 'BPROD', -- db server 1
                'ERP', 'QPROD', 'PR4',        -- db2 server
                'HR', 'HTA'                   -- db3 server
  else 'COLOR 70'
        END) host_cmd_col
FROM dual;

HOST  &host_cmd

COLOR [attr]

attr        Specifies color attribute of console output

Color attributes are specified by TWO hex digits -- the first corresponds to the
background; the second the foreground. Each digit can be any of the below values.

0 = Black    8 = Gray
1 = Blue     9 = Light Blue
2 = Green    A = Light Green
3 = Aqua     B = Light Aqua
4 = Red      C = Light Red
5 = Purple   D = Light Purple
6 = Yellow   E = Light Yellow
7 = White    F = Bright White

set termout on
set timing on

5 thoughts on “Change SQLPlus color according to database name

  1. Thanks. I’d been tearing my hair out* trying to figure this out. I’d been missing the “host” part. D’oh!

    * Not literally. I shave my head.


  2. We have standardized server names on the new Exadata machines so I was able to simplify this:

    COLUMN host_cmd_col NEW_VALUE host_cmd
    when sys_context('USERENV', 'SERVER_HOST') like 'edp%'
    else 'COLOR F0'
    FROM dual;
    HOST &host_cmd


  3. Pingback: Manage your SQL Plus Window | An Oracle Guy's ColdFusion

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s