Can Sql*plus Read Environment Variables From The Machine Is It Running On?

I'm aware that the database engine itself is (often) on another machine and that SQL*Plus has no direct way of reading those environment variables, but I'm in a tricky situation wh

Solution 1:

You can get a few client-related things from the USERENV context, but not arbitrary environment variables.

If you can create a file on your local machine you could use the host command to set a substitution variable based on an environment variable:

SQL> host echo define homedir=$HOME >/tmp/gethome.sql

SQL> @/tmp/gethome.sql
SQL> host rm -f /tmp/gethome.sql

SQL>select'&homedir.'as home from dual;

HOME
------------/home/apoole

1row selected.

Not very pretty, but if you can't pass the variables on the command line as positional parameters then your options are rather limited.

This is using a Unix-y paths and commands of course, but you can do the same sort of thing in Windows.

Solution 2:

Sys_context should solve your problem. You can set custom environment variable in database using DBMS_SESSION.SET_CONTEXT ('MY_NAMESPACE', 'MY_PARAMETER', v_input_parameter); and then fetch it using SYS_CONTEXT ('MY_NAMESPACE', 'MY_PARAMETER'); So you can run a initial pl/sql block to set variable in session and then use it as per requirement.

You can see an example here: http://blog.contractoracle.com/2010/03/using-dbmssessionsetcontext-to-store.html

Solution 3:

If you could pass the variable via sqlplus argument passing mechanism. you could do the following,

cat > myscript.sql <<!select'&1'as HOME from DUAL;
!

sqlplus user/pwd@db@myscript.sql $HOME