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 where I merely need the environment variables from the machine the client itself is running on.
Is there a way to cheat these values into the SQL*Plus client from within a single script that will be run in SQL*Plus? The script consists of a single begin/end PL/SQL block, but if I need to use SQL*Plus directives of the set/define/variable sort that shouldn't be a problem either.
What I can't do is alter the way that the SQL*Plus executable itself is started (I don't have access to pass the values in as arguments).
Is there any way to accomplish this?
Note: dbms_system.get_env() seems to retrieve environment variables from the server itself, which is what I do not want.
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
1 row 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.