Tuesday, February 15, 2011

A tip, a conference, an extension and a challenge

This post contains four unrelated notes.

First a small SQL*Plus tip. I really like to know with which user I am connected to which database, so in my login.sql script I used to have this section:

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

I saw this piece of code in Tom Kyte's book Expert Oracle Database Architecture and I have used it ever since. The downside from using this adjusted prompt, is that the first line isn't properly aligned anymore with lines 2 and further, as you can see by this example:

rwijk@ORA11202> select ename
2 from emp
3 where deptno = 10
4 /


3 rows selected.

I read a tip on Oracle-L, which I now use. Instead of "set sqlprompt '&gname> '", I now use "host title &gname". This adjusts the title bar of my SQL*Plus window with the user@database information, and leaves the prompt at "SQL> ", thus leaving my SQL statements aligned while I'm still able to see my connect string all the time.


On March 22, the Dutch Oracle usergroup OGh organizes their second APEX day. Again we have a great lineup of speakers. You can read about it here. Due to the great response, we had to scale up the event. Now there is room for 250 people instead of our originally planned 150. And be sure to bring your manager with you. He or she doesn't want to miss the APEX at work track where customers will tell how they have successfully implemented parts of their business processes with APEX. I'm especially looking forward to the Northgate story as this is a huge APEX project.


Today there was good news for future Oracle searches on Google. On their blog they announced a Personal Blocklist Extension. Unfortunately it currently doesn't work for the Dutch version of Chrome. The following quote from the Google blog is very promising though:

If installed, the extension also sends blocked site information to Google, and we will study the resulting feedback and explore using it as a potential ranking signal for our search results.


The Northern California Oracle Users Group (NoCOUG) has issued their Second International SQL Challenge. You can read about it here in their journal. SQL commands for creating the required data are available here. Initially, it may look like insufficient explanation is given to solve the problem, but that's part of the fun. If you like SQL and puzzles, then you should definitely give this one a go.



  1. Rob,
    thank you very much for the sql*plus tip - this is something I was looking for a long time. I added the define block to my login-file some years ago when I saw it in Tom Kytes Expert One-on-One Oracle book - but actually never used it because of the indention that ruined my copy&paste operations.

    And by the way: thank you for your excellent blog.

  2. Rob,

    if you want to change you sqlprompt and keep alignment in your querys you can add chr(10) and spaces to your sqlprompt. For example I use:

    SQL> column _promp new_value _promp
    SQL> select sys_context('USERENV', 'INSTANCE_NAME')||'.'||lower(sys_context('USERENV', 'SESSION_USER'))||'>'||chr(10)||chr(10)"_promp" from dual;

    1 row selected.

    SQL> set sqlprompt '&_promp '

    2 1
    3 from
    4 dual;

    1 row selected.

  3. There has been a problem with the paste I think and/or with the visualization.
    There are five spaces after &_promp

  4. Thanks Martin and Joaquin for your comments.

    @Joaquin: I know it's a matter of taste, but I prefer my current settings :-)

  5. Rob,

    I was very excited about this post.
    I've been calling my own setprompt script from login.sql for longer than I care to remember.
    This is a great alternative.

    However, there is 1 limitation:
    Though it works like a charm for sqlplus.exe (dos box) windows, I can not get it to work on sqlplusW.exe windows.

    But as soon as we switch to 11g I will have to start using sqlplus.exe anyway.

    Erik van Roon

  6. Hi Erik,

    What puzzles me is why you would choose to use sqlplusw.exe, when you have sqlplus.exe available ...

    Just try it, and you'll be amazed you didn't switch earlier.

    Here is a great article that helps you setting it up: http://www.williamrobertson.net/documents/sqlplus-setup.html


  7. Hi Rob,

    Thanks for the feedback, but I know how to set up sqlplus.
    It's just one of those things where personal preference dictates arguments.

    True, in sqlplusw I miss scrolling through previous commands.
    And, yes 12 Mb of memory is infinite times zero Kb. But it's still next to nothing on my 12 Gb laptop.
    Most other reasons mr Robbertson mentions are downright silly.
    I mean "you can't change the font or the colours". Really?
    And though it's true that "there is no option to paste copied text with a right mouseclick", you can in fact use Ctrl-C (or Ctrl-Ins) and Ctrl-V (or Shift-Ins) to copy and paste.
    Personally I enjoy the fact that I don't have to leave the keyboard to copy and paste.

    But what I miss the most in sqlplus is sqlplusw's 'File - Open'.
    This enables me to just browse to a different folder and, bingo, it's my current folder.
    In sqlplus the current folder when you start sqlplus stays the current folder.
    If you want to change it, either you close sqlplus, change directory and restart sqlplus, or you make a new shortcut with a new 'Start in', or...
    Anyway all solutions come down to starting a new sqlplus session.