GLOGIN with Color Display

As a dba, I will be executing various scripts in multiple databases. Mostly in non-productions databases.

 In the event, if any of these scripts were executed inadvertently in the production database, it will be disastrous.

There is always a possibility of executing scripts inside a wrong window/terminal, completely wiping out schemas/databases.

To deal in such risky situations, I modified the glogin script and color coded the display of environment details as sqlprompt. Before executing any command, I just look at the prompt and make sure, I am logged into the right database.

cd $ORACLE_HOME/sqlplus/admin
$ cat glogin.sql
define _editor=vi
column global_name new_value gname
set termout off
/***PLS MODIFY THE BELOW SQL AS PER YOUR REQUIREMENT OF VARIABLES AND COLOR ***/
select   --chr(27)||'[32m'||chr(27)||'[1'||to_char(0)||'m'|| --GREEN  COLOR
         --chr(27)||'[33m'||chr(27)||'[1'||to_char(0)||'m'|| --YELLOW COLOR
         --chr(27)||'[34m'||chr(27)||'[1'||to_char(0)||'m'|| --VIOLET COLOR
           chr(27)||'[35m'||chr(27)||'[1'||to_char(0)||'m'|| --PINK   COLOR
upper(sys_context('USERENV','HOST'))||'-'||sys_context('USERENV','DB_NAME')||'-'||sys_context('USERENV','DB_UNIQUE_NAME') global_name from dual;
/***PLS MODIFY THE ABOVE SQL AS PER YOUR REQUIREMENT OF VARIABLES AND COLOR***/
set termout on
set sqlprompt '&gname> '

The glogin.sql script provided by Oracle is just right for most of the environments. I only modified the display varialbes in my sql prompt and added required color display as per my requirement.

Pls modify the script as per your requirement and test it thoroughly in a lower environment, before using in production dbs. 

Readers' discretion is advised.

References:

http://www.adp-gmbh.ch/ora/sqlplus/login.html 

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:446220075876 

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm 

https://stackoverflow.com/questions/35909900/coloring-and-customising-sqlplus-prompt-in-shell 

http://orasql.org/scripts/colors.sql 

http://oracle.ninja/color-your-sqlplus-scripts/

While testing this script, I encountered these issues in some of our dbs.
1. While connecting to one db, Oracle complained with the following error:

–There is a size limit of 50 characters on the glogin string display.
–If this limitation exceeds, you get the following error:
“string beginning “‘s too long. maximum size is 50 characters.”

–I reduced the length of the displayed string with substr function.

2. When you are connecting to an idle instance as sysdba and trying to startup the db, Oracle displayed the message "Enter value for gname:". I supplied the $ORACLE_SID name, connected to idle instance and started the db. This problem will not arise when you are connecting to a running db.

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 8 16:31:43 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

Enter value for gname: <ENTER DB_NAME >