[QUOTE][i]Originally posted by lesstjm [/i]
[B]Is there a way I can get the name without querying a view? [/B][/QUOTE]
Well, you'll have to query something, either dual or some other table or view ;).
The suggested method with V$DATABASE and V$INSTANCE are OK, but one must have DBA role or some other quite strong privilege, or explicite privilege on those views. I for example don't like to give my users any privilege that they don't realy need for their work.
Well, to get the database name out from the sistem without having any special privilege, one can use *publicly available* view GLOBAL_NAME with the single column named GLOBAL_NAME, which contains (gues what!) global database name. So it behaves exactly as DUAL: it is a single_row/single_column, and everybody has gotten select privilege on it!
SELECT * FROM global_name;
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Give you a SYS_CONTEXT function that give you access to the value of a lot of user environment variables.
eg. DB_NAME, IP_ADDRESS, TERMINAL, SESSION_ID...
The sql to get the DB_NAME is
select sys_context('USERENV','DB_NAME') from dual;
before oracle 8i,
you can connect as sys and create a view for use
create view DB_NAME_V as select name from v$database;
create public synonym DB_NAME_V for DB_NAME_V;
grant select on DB_NAME_V to public;
The value for GLOBAL_NAME on 8i or 7.3.x will depend on the INIT.ORA of yr ddatabase and also depend if someone has changed.
Check what is it about "global name" from metalink