I have more then one Oracle 8 database servers. I need to have SQL promp to be my connect string. Username whenever I login in SQL Plus. Connect String is the same as Server Name.

Example:

Server 1 = aaaaa
Server 2 = bbbbb
Username = Naaa

Prompt should be

aaaaa.Naaa> or bbbbb.Naaa>

>>>>>
I use the following sql script to change my prompt. Modify it according to your requirements.
Copy and paste the below script (between .............) into a file named login.sql.
Copy this login.sql file in the working directory of your sqlplus (like c:\oracle\ora81\bin).
Oracle looks for a file named login.sql in its working directory. If it finds one then it executes it.

..................

set lines 1000
set pages 50
set serverout on size 500000
set head off
set pages 0
set termout off
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
/
spool z1.sql
select 'set sqlprompt '''||lower(global_name)||':'||user||'>''' from sys.global_name
/
spool off
@z1
set head on
set pages 60
set termout on

...................

>>>>>
I take a slightly different approach. I use the "new_value" clause of the column statement.

Here is the script:

-- setPrompt.sql
-- -------------
set term off

column prompt_col new_value prompt_var

define prompt_var = "not connected> "

set prompt off
select lower(user) || '@' || ' (' || global_name || ')' || chr(10) || ' > ' prompt_col
from global_name
/

set sqlprompt "&prompt_var"

set term on

Quick Links:
Do you have an Oracle Question?

Best regards,
Oracle Database, SQL, Application, Programming Tips