I would like to change the sqlplus command-prompt to something more useful than

SQL>

You can modify the sqlprompt variable as follows,

SQL>
SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
FOO_OWNER@FOO> 

You can also use variables such as _PRIVILEGE and _DATE, although all I really want is the user and schema. Unfortunately, with the above technique, if you ever connect to a database using a full connection string (rather than a tnsnames.ora entry), this prompt will be a bit unwieldy. e.g.,

$ sqlplus foo_owner/foo_pass@'(DESCRIPTION=(ADDRESS_LIST=(load_balance=on)(failover=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.11)(PORT=1528)))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=FOO)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=15))))'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 12:47:19 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option

SQL> 
SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "        
FOO_OWNER@(DESCRIPTION=(ADDRESS_LIST=(load_balance=on)(failover=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.11)(PORT=1528)))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=FOO)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=15))))> 

I love that I can connect without a tnsnames.ora entry, but I would like a simplistic prompt and I want it loaded every time I start sqlplus.

Fortunately, sqlplus will look in the current directory and in the $SQLPATH environment variable for a login.sql, and execute it automatically. For example, if you include the following in your login.sql

SET TERMOUT OFF 
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON

Then whenever you run sqlplus the prompt will be set accordingly, e.g.,

$ sqlplus foo_owner/foo_pass@FOO

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 12:47:19 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option

foo_owner@FOO> show user
USER is "FOO_OWNER"
foo_owner@FOO>