Re: Determine then name of the DB inside a Stored Procedure

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