I downloaded part of Tom Kyte’s newest book “Expert Oracle Database Architecture – 9i and 10g Programming Techniques and Solutions” from Tom’s blog and started reading with interest (Can’t wait until the book is available at Amazon). I got to page 25, “Setting Up Your Environment” and read through page 28 when I noticed the following:
define gname=idle column global_name new_value gname select lower(user) || '@' || substr( global_name,1, decode( dot, 0, length(global_name), dot-1) ) global_name from (select global_name, instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> '
The above in login.sql sets up the SQL*Plus prompt. Then, I wondered, since the book was about 9i and 10g, why didnâ€™t Tom use or mention Oracle’s
_CONNECT_IDENTIFIER pre-defined variable (available in version 9.2 and above) and
_USER pre-defined variable available in 10.1? In 10g, setting the SQL Prompt in SQL*Plus is as easy as:
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
_CONNECT_IDENTIFIER works because I have tested it in my Oracle 9.2 environment. I do not have 10g installed, so I did not test
_USER. Maybe it’s mentioned somewhere else in the book. I guess I’ll have to wait until I get my shipment from Amazon 🙂