Try using global_name: 'select * from global_name'. It contains only
one column, which is the name of the database. As far as I know, any
user has access to it.
fumi wrote:
>
> fumi <fumi_at_tpts5.seed.net.tw> wrote in message news:7uq32s$a36$8_at_news.seed.net.tw...
> >
> > Rui Anastácio <coreto03_at_axa-seguros.pt> wrote in message news:7un4f3$3gq$1_at_duke.telepac.pt...
> > > I have a procedure that writes a file to the system via utl_file. Since I
> > > have to indicate the directory and this changes from development to test and
> > > production I need to change the Stored Procedure upon compiling in different
> > > DB's (the problem).
> > >
> > > I could solve the problem if I knew the database name from the procedure.
> > > Then I could write the file to the corresponding place.
> > >
> > > USERENV might be a solution
> > > V$ is for SYS only right ?
> > > DBMS_APPLICATION_INFO might do it but I don't have the info. Anyone ?
> > >
> > > Any other way ?
> >
> >
> > USERENV and DBMS_APPLICATION_INFO are inconducive.
> > Login as SYSTEM (or someelse with DBA or select any table privilege),
> > create a function, and grant it to public:
> >
> > create or replace function get_database_name
> > return v$database.name%type
> > is
> > name v$database.name%type;
> > begin
> > select name into name from v$database;
> > return name;
> > end;
> > /
> > grant execute on get_database_name to public;
> > create public synonym get_database_name for system.get_database_name;
>
> One more comment:
> In Oracle8i, there is a build-in (undocumented?) function called DATABASE_NAME:
>
> SQL> set serveroutput on;
> SQL> exec dbms_output.put_line(database_name);
> FUMI
>
> PL/SQL procedure successfully completed.
>
> SQL> select dbms_standard.database_name from dual;
>
> DATABASE_NAME
> ----------------------------------------------------
> FUMI
Received on Tue Oct 26 1999 - 00:26:57 CDT
