Selecting Database Name

From TekiWiki
Jump to: navigation, search

To select the Oracle Database name, the most common method is:

SELECT name FROM v$database

This, however, requires special permissions to be able to read the pseudo table v$database.

The database name is part of the session context and can be selected using the following function:

SELECT sys_context('USERENV', 'DB_NAME') FROM dual

When used as part of a larger query, this does not need to join the v$database pseudo table.

This is particularly useful in packaged solutions like PeopleSoft, SAP etc. where permissions to see v$database are non-standard.

For more hints and tips like this, see the eBook:

UsingFunctionsInPeopleSoftQuerySmall.jpg

Using Functions in PeopleSoft Query

PeopleSoft Query


Transform-PeopleSoft-Query.JPG

Transforming PeopleSoft Query