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

SELECT  ( CASE 
          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
             )
  THEN 'COLOR 0E'
  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
Advertisements

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.

    Like

  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
    SELECT CASE
    when sys_context('USERENV', 'SERVER_HOST') like 'edp%'
    THEN 'COLOR 0E'
    else 'COLOR F0'
    END
    host_cmd_col
    FROM dual;
    HOST &host_cmd

    Like

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s