Modifying SQL Prompt in LOGIN.SQL for SQL*Plus

[BJCooperIT]

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

 

[AOLEURO]

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 !

 

[BJCooperIT]

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

 

[AOLEURO]

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 !

 

[BJCooperIT]

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

 

[dspare]

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.

 

[BJCooperIT]

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

 

[Turkbear]

Hi,
Just 1 more idea,
try

 select version from sys.v$instance;

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

 

[BJCooperIT]

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:

USERNAMEATABASE 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

 

[dspare]

Perhaps you could sweet-talk the DBA into granting that select permission!!!