1) Here are various ways. You look like you are using 10g, so the first
line is all you should need. set sqlprompt "_user @ _connect_identifier>
"


2) The prompt.sql is a script not shown here that writes to disk and
executes it. Subsequently you could always call a 'connect.sql' script
that always calls prompt.sql... and that limits you connecting this
way... (or just call prompt.sql after connecting manually). This has to
be done until 10g.

3) The others code snippets do various tricks from glogin, but don't
necessarily change anything when you switch users.


-- set sqlplus prompt
set sqlprompt "_user @ _connect_identifier> "

_____________________________________-

rem @C:\oracle\admin\Scripts\prompt.sql
-- Set termout off
-- 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 ent> '
-- set termout on
-- undefine gname


-- set termout off
-- column dbname new_value db_prompt
-- select user || ':' || upper(instance_name) || ':' || upper(host_name)
|| '>' dbname from v$instance;
-- set sqlprompt "&&db_prompt "
-- set termout on

-----Original Message-----
From: Cano Jose
[mailto:oracledba-ezmlmshield-x86556998.[Email address protected]
Sent: Wednesday, November 30, 2005 3:54 PM
To: LazyDBA Discussion
Subject: Is there a way to update the user id in the SQL prompt
dynamically ?

Please help if you can .... Thank You in advance.

I have the following code in my login.sql

undefine myusrid mydbname
col myusrid new_value myusrid
col mydbname new_value mydbname
set termout off
select lower(user) myusrid,
global_name mydbname
from global_name
/
set termout on
set sqlprompt '&&myusrid.@&_CONNECT_IDENTIFIER> '

and it does work fine.
after login my prompt is:
[Email Address Removed] Now; I want to change to a different user:
[Email Address Removed] connect system/mysystempswd
Connected.
[Email Address Removed] show user
USER is "SYSTEM"
[Email Address Removed] What I am looking for is how to change the prompt so that the
new user is displayed as part of the prompt: [Email Address Removed] Jose.




--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page