Tom Kyte’s Newest Book and SQLPlus Prompt

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 > "

I know _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

Related links:
My other blog entry about SQL*Plus
Related entry at Tom’s blog
SQL*Plus® User’s Guide and Reference

Filed in Oracle on 26 Aug 05 | Tags: book, sqlplus

---