Difference between revisions of "Selecting Database Name"
From TekiWiki
(Created page with "To select the Oracle database name, the most common method is: SELECT name FROM v$database This, however, requires special permissions. The database name is part of the...") |
|||
| (9 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | To select the [[Oracle]] | + | To select the [[Oracle Database]] name, the most common method is: |
| + | <pre> | ||
SELECT name FROM v$database | SELECT name FROM v$database | ||
| + | </pre> | ||
| − | This, however, requires special permissions | + | 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: | ||
| + | |||
| + | <pre> | ||
SELECT sys_context('USERENV', 'DB_NAME') FROM dual | SELECT sys_context('USERENV', 'DB_NAME') FROM dual | ||
| + | </pre> | ||
When used as part of a larger query, this does not need to join the v$database pseudo table. | 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]]. | + | 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: | For more hints and tips like this, see the eBook: | ||
| + | |||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | |[[File:UsingFunctionsInPeopleSoftQuerySmall.jpg|90px|link=http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21]] | ||
| + | |||
| + | | | ||
| + | [http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21 Using Functions in PeopleSoft Query] | ||
| + | |||
| + | [[PeopleSoft Query]] | ||
| + | |||
| + | |||
| + | |[[File:Transform-PeopleSoft-Query.JPG|108px|link=http://bit.ly/PS_XSLT_BOOK]] | ||
| + | |||
| + | | | ||
| + | [http://bit.ly/PS_XSLT_BOOK Transforming PeopleSoft Query] | ||
| + | |||
| + | |||
| + | |} | ||
Latest revision as of 22:48, 18 December 2016
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:
|
Using Functions in PeopleSoft Query
|
|
|
