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

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 22 Oct 1999 16:23:56 GMT
Message-ID: <7uq32s$a36$8@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; Received on Fri Oct 22 1999 - 11:23:56 CDT