Thursday, May 31, 2007

How to query server and client environment in PL/SQL

To query the oracle server environment variables inside a pl/sql block that were set when the database was started:

$ . oraenv
ORACLE_SID = [oracle] ? ora1022
$ /usr/ucb/ps auxwwe | sed -n
$ export TZ=GMT
$ printf "%s\n" "set lines 10" "var f varchar2(40)" "set autop on" \
> "exec dbms_system.get_env('TZ',:f)" |
> sqlplus -s / as sysdba

To query the client or listener environment, use:

SQL> var f varchar2(40)
SQL> set autop on
SQL> exec sys.dbms_system.get_env('TZ',:f);



Latest Posts