Determine then name of the DB inside a Stored Procedure

Determine then name of the DB inside a Stored Procedure

Post by Rui Anast├íci » Sun, 31 Dec 1899 09:00:00



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 ?

 
 
 

Determine then name of the DB inside a Stored Procedure

Post by michael_bia.. » Sun, 31 Dec 1899 09:00:00


Hi.

 I think the simpliest solution is
  Either
  Ask your DBA to
     "GRANT SELECT ON SYS.V$DATABASE TO PUBLIC;"
     In you SP - make a select from it.
  Or
  if you can not do it - ask DBA to write a SP that
  makes a SELECT and returns database name.
  Ask him to GRANT EXECUTE ON <sp> TO PUBLIC;

  HTH. Michael.
  P.S. In my place we have a lot of users with access
       rights to V$....



Quote:> 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 ?

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Determine then name of the DB inside a Stored Procedure

Post by fumi » Sun, 31 Dec 1899 09:00:00



> 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;

 
 
 

Determine then name of the DB inside a Stored Procedure

Post by fumi » Sun, 31 Dec 1899 09:00:00




> > 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

 
 
 

Determine then name of the DB inside a Stored Procedure

Post by Curtis Holbroo » Sun, 31 Dec 1899 09:00:00


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.



> > > 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