Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Modifying SQL Prompt in LOGIN.SQL for SQL*Plus

Status
Not open for further replies.

BJCooperIT

Programmer
May 30, 2002
1,210
US
I am currently switching back and forth between SQL*Plus sessions of Oracle 8.0.5 and 8.1.7 in two different databases on different servers. I would like my SQL prompt to leave no doubt which session I am working in since one is development and one is production. My sessions use the same LOGIN.SQL. I have added some code to modify my SQL Prompt:

set termout off
define new_prompt='nolog'
column new_prompt new_value new_prompt
select username || ': ' || substr(substr(global_name,1,30),1,
instr(substr(global_name,1,30),'.')-1) new_prompt from user_users, global_name;
set sqlprompt "&new_prompt> "
set termout on

Does anyone know a way to modify this so that I can also reflect the version number in the prompt? View v$version does not seem to be helpful enough to use without some hokey code to get the first row and substring out the version.

Anyone got a better idea? Thanks!
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
 
Hi

Use version from v$instance (if you can access this table), like this

select username || ': ' || substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1)||'-'|| v.version new_prompt
from user_users, global_name
, v$instance v;

Output is:

SYSTEM: SQL-9.2.0.1.0 Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !
 
Unfortunately that to comes up as 0.0.0. This is my solution (for now):

set termout off

define my_version='0.0.0'
column my_version new_value my_version
select substr(banner,instr(banner,' ',-1,3),6) my_version
from sys.v_$version v
where rownum = 1;

define new_prompt='nolog'
column new_prompt new_value new_prompt
select username || ':' ||
substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1)
new_prompt
from user_users, global_name;

set sqlprompt "&new_prompt&my_version> "
set termout on


Feel free to use it if you need it! (select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
 
You get 0.0.0 ???

If you write select version from v$instance;

What is you output? Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !
 
I cannot access v$instance. The 0.0.0 came from the defaulted value I set in LOGIN.SQL. Had to set term on to see the real error. (select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
 
If you are the DBA you can remedy your access to v$instance by logging in as SYS and issuing:

grant select on v$instance to public;

After that any user will be able to see the data in that view.
 
My thanks to both of you. I wish I were the DBA but on this contract I am just a developer with few DBA privileges in production.
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
 
Hi,
Just 1 more idea,
try
Code:
 select version from sys.v$instance;

Perhaps you have rights but no synonym has been created...

[profile]
 
Great thought Turkbear, but I still do not have access. Guess I will stick to my "hokey" code. At least I can tell where I am connected if I see:

USERNAME:DATABASE 8.1.7>

no matter how I got it!
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
 
Perhaps you could sweet-talk the DBA into granting that select permission!!! :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top