There was a simple requirement to print the login name of the user running a BIPublisher report on that report itself. This got me hunting. There are tonnes of ways to get the login name from an oracle database, but because of the way BIP uses the DB and runs the SQL, not all of those ways would give the right answers. cleartext.blogspot.com
Simplest way, use the <?$XDO_USER_NAME?> parameter n your template.
But my requirement was to enforce data security on the report, to ensure the logged in user sees only the records he should see. This meant fetching the username at the SQL itself.
One sure shot way to do this is to use :XDO_USER_NAME in the SQL. This will fetch the userid of the logged in user. cleartext.blogspot.com
And there is one more way.
Use the sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER function in the SQL. cleartext.blogspot.com
To get all the sys_context parameters, this SQL can be used. cleartext.blogspot.com
select
sys_context ('userenv','ACTION') ACTION,
sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,
sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
sys_context ('userenv','CURRENT_USER') CURRENT_USER,
sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
sys_context ('userenv','DB_NAME') DB_NAME,
sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
sys_context ('userenv','ENTRYID') ENTRYID,
sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
sys_context ('userenv','HOST') HOST,
sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
sys_context ('userenv','INSTANCE') INSTANCE,
sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME, cleartext.blogspot.com
sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
sys_context ('userenv','ISDBA') ISDBA,
sys_context ('userenv','LANG') LANG,
sys_context ('userenv','LANGUAGE') LANGUAGE,
sys_context ('userenv','MODULE') MODULE,
sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
sys_context ('userenv','NLS_SORT') NLS_SORT,
sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
sys_context ('userenv','OS_USER') OS_USER,
sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
sys_context ('userenv','PROXY_USER') PROXY_USER,
sys_context ('userenv','PROXY_USERID') PROXY_USERID,
sys_context ('userenv','SERVER_HOST') SERVER_HOST,
sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME,
sys_context ('userenv','SESSION_USER') SESSION_USER,
sys_context ('userenv','SESSION_USERID') SESSION_USERID,
sys_context ('userenv','SESSIONID') SESSIONID,
sys_context ('userenv','SID') SID,
sys_context ('userenv','STATEMENTID') STATEMENTID, cleartext.blogspot.com
sys_context ('userenv','TERMINAL') TERMINAL
from dual
Try running this on Oracle Live SQL
