RE: Is there a way to update the user id in the SQL prompt dynami cally ?

RE: Is there a way to update the user id in the SQL prompt dynami cally ?

 

  

Can't: Use two scripts, and always connect i.e. @s:\connect

Connect will call prompt. Or connect yourself, then call prompt.

Note that prompt has all kinds of extra crap right now that I don't use,
I leave it for you to play with. To get the original idea take out all
the remarked lines.

Prompt2.sql is added at bottom for more ideas.

@connect.sql:
set scan on
prompt connecting to &&[Email Address Removed] &&[Email Address Removed] sqlcase mixed
set pagesize 400
set pause off
set linesize 1000
set serveroutput on
undefine v_Instance
undefine v_Username

@prompt.sql
set linesize 200
SET TERMOUT OFF
set pause off
-- set pagesize 0
set feedback off
set heading off
set verify off
SET ECHO OFF
rem arraysize is set down for Oracle8 databases, else script doesn't
work.
set arraysize 10

spool login.tmp

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

select 'SET SQLPROMPT "'||user||'@'||

decode(instr(global_name,'.'),0,global_name,substr(global_name,1,instr(g
lobal_name,'.')-1))||'> "'
from global_name;

rem SELECT 'set sqlprompt
"'||USER||'@'||UPPER(SYS_CONTEXT('USERENV','DB_NAME'))||'> "'
rem FROM DUAL;
rem

rem select 'SET SQLPROMPT "'||user||'@'||upper(instance_name)||'> "'
from v$instance;

spool off
@login.tmp
host del login.tmp

REM set back to default
set numwidth 9
rem set linesize, pagesize in glogin.sql, login.sql, or connect scripts.
set feedback on
set heading on
set verify on
SET TERMOUT ON





rem DEFINE v_DBNAME="?"
rem DEFINE v_USER="?"
rem DEFINE v_SID="?"
rem DEFINE v_HOST="?"
rem COLUMN v_DBNAME NEW_VALUE v_DBNAME NOPRINT
rem COLUMN v_USER NEW_VALUE v_USER NOPRINT
rem COLUMN v_SID NEW_VALUE v_SID NOPRINT
rem COLUMN v_HOST NEW_VALUE v_HOST NOPRINT

rem SELECT
rem SYS_CONTEXT('USERENV','DB_NAME') V_DBNAME
rem ,USER V_USER
rem ,SYS_CONTEXT('USERENV','SESSIONID') v_SID
rem ,UTL_INADDR.GET_HOST_NAME v_HOST
rem FROM
rem DUAL
rem ;

rem SELECT
rem LTRIM(SID) v_SID
rem FROM
rem V$SESSION
rem WHERE
rem AUDSID=SYS_CONTEXT('USERENV','SESSIONID')
rem AND
rem STATUS='ACTIVE'
rem ;

rem SET SQLPROMPT "&v_HOST:&v_DBNAME:&v_USER(&v_SID)> "

rem UNDEFINE v_DBNAME
rem UNDEFINE v_USER
rem UNDEFINE v_SID
rem UNDEFINE v_HOST

rem **************************************************
rem DEFINE v_DBNAME="?"
rem DEFINE v_USER="?"
rem DEFINE v_SID="?"
rem DEFINE v_HOST="?"
rem DEFINE V_INSTANCE_NAME="?"
rem COLUMN v_DBNAME NEW_VALUE v_DBNAME NOPRINT
rem COLUMN v_USER NEW_VALUE v_USER NOPRINT
rem COLUMN v_SID NEW_VALUE v_SID NOPRINT
rem COLUMN v_HOST NEW_VALUE v_HOST NOPRINT
rem COLUMN V_INSTANCE_NAME NEW_VALUE V_INSTANCE_NAME NOPRINT
rem SELECT
rem SYS_CONTEXT('USERENV','DB_NAME') V_DBNAME
rem ,instance_name V_INSTANCE_NAME
rem ,USER V_USER
rem ,SYS_CONTEXT('USERENV','SESSIONID') v_SID
rem ,UTL_INADDR.GET_HOST_NAME v_HOST
rem FROM
rem v$instance
rem ;
rem
rem SELECT
rem LTRIM(SID) v_SID
rem FROM
rem V$SESSION
rem WHERE
rem AUDSID=SYS_CONTEXT('USERENV','SESSIONID')
rem AND
rem STATUS='ACTIVE'
rem ;
rem SET SQLPROMPT "&v_HOST:&v_DBNAME:&v_instance_name:&v_USER(&v_SID)> "
rem UNDEFINE v_DBNAME
rem UNDEFINE v_USER
rem UNDEFINE v_SID
rem UNDEFINE v_HOST

---------------------
prompt2.sql
set linesize 200
SET TERMOUT OFF
set pause off
-- set pagesize 0
set feedback off
set heading off
set verify off
SET ECHO OFF
rem arraysize is set down for Oracle8 databases, else script doesn't
work.
set arraysize 10

spool login.tmp
select 'SET SQLPROMPT "'||user||'@'||

decode(instr(global_name,'.'),0,global_name,substr(global_name,1,instr(g
lobal_name,'.')-1))||'> "'
from global_name;

SELECT 'set sqlprompt
"'||USER||'@'||UPPER(SYS_CONTEXT('USERENV','DB_NAME'))||'> "'
FROM DUAL;


spool off
@login.tmp
host del login.tmp

REM set back to default
set numwidth 9
rem set linesize, pagesize in glogin.sql, login.sql, or connect scripts.
set feedback on
set heading on
set verify on
SET TERMOUT ON



-----Original Message-----
From: Sucheta Babar
[mailto:oracledba-ezmlmshield-x4492359.[Email address protected]
Sent: Thursday, December 01, 2005 4:04 AM
To: LazyDBA Discussion
Subject: Re: Is there a way to update the user id in the SQL prompt
dynami cally ?

How the same thing can be achieved in ORACLE 9i

----- Original Message -----
From: "Chamberlain John "
<oracledba-ezmlmshield-x54312581.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, December 01, 2005 2:08 PM
Subject: RE: Is there a way to update the user id in the SQL prompt
dynami
cally ?


>
> If you are running a 10G client, put:
>
> set sqlprompt "_user'@'_connect_identifier> "
>
> in the glogin.sql file ($ORACLE_HOME/sqlplus/admin)
>
> and it will change the SQL prompt every time you change the
> connection/reconnect. Pre-10G it will not change when you reconnect.
>
>
>
> -----Original Message-----
> From: Cano Jose
> [mailto:oracledba-ezmlmshield-x86556998.[Email address protected]
> Sent: 30 November 2005 20:54
> 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
>
> -----------------------------------------
> Information in this email may be privileged, confidential and is
intended
> exclusively for the addressee. The views expressed may not be official
> policy, but the personal views of the originator. If you have received
it
> in error, please notify the sender by return e-mail and delete it from

> your
> system. You should not reproduce, distribute, store, retransmit, use
or
> disclose its contents to anyone. Please note we reserve the right
to
> monitor all e-mail communication through our internal and external
> networks.
>
>
>
> --------
> 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
>
>
> --
> This message has been scanned for viruses and
> dangerous content, and is believed to be clean.
>



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