msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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: ,


Reader's Comments

  1. |

    I did everything in a manner that would work in all versions when possible – including using statspack instead of things like AWR reports. I still want the global name in there, not the sid (sids can repeat, global names should not).

  2. |

    I did everything in a manner that would work in all versions when possible

    Makes sense.

    I still want the global name in there, not the sid (sids can repeat, global names should not)

    I found related discussions on AskTom here and here.

    Thanks Tom for the clarifications.

  3. |

    [...] Well, my wait is over and now I have the book. I’m not lucky enough to have it signed by Tom though, maybe volume 2? [...]