SQL * Plus可以从机器上读取环境变量吗？

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.

我知道数据库引擎本身(经常)在另一台机器上并且SQL * Plus没有直接读取这些环境变量的方法,但是我处于一个棘手的情况,我只需要来自机器的环境变量客户端本身正在运行。

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.

有没有办法在一个将在SQL * Plus中运行的单个脚本中将这些值欺骗到SQL * Plus客户端?该脚本由一个开始/结束PL / SQL块组成,但是如果我需要使用set / define / variable排序的SQL * Plus指令,这也不应该是一个问题。

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).

我不能做的是改变SQL * Plus可执行文件本身的启动方式(我没有权限将值作为参数传递)。

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.

注意:dbms_system.get_env()似乎从服务器本身检索环境变量,这是我不想要的。

3 个解决方案

#1

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

您可以从USERENV上下文中获取一些与客户端相关的内容,但不能获取任意环境变量。

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:

如果可以在本地计算机上创建文件,则可以使用host命令根据环境变量设置替换变量:

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.

当然,这是使用Unix-y路径和命令,但您可以在Windows中执行相同的操作。

#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.

Sys_context应该可以解决您的问题。您可以使用DBMS_SESSION.SET_CONTEXT('MY_NAMESPACE','MY_PARAMETER',v_input_parameter)在数据库中设置自定义环境变量;然后使用SYS_CONTEXT('MY_NAMESPACE','MY_PARAMETER')获取它;因此,您可以运行初始pl / sql块来在会话中设置变量,然后根据需要使用它。

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

你可以在这里看到一个例子:http://blog.contractoracle.com/2010/03/using-dbmssessionsetcontext-to-store.html

#3

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

如果你可以通过sqlplus参数传递机制传递变量。你可以做以下事情,

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

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

#1