oracle oracle中如何用sql语句读取环境变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20991187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how can read environment variable with sql statement in oracle
提问by sio4
I use Oracle 11g on my systems, and I need to get system's environment variables using SQL.
我在我的系统上使用 Oracle 11g，我需要使用 SQL 获取系统的环境变量。
is there any way to get it? (for example USER, HOME, PATH, in fact, I need to get my custom environment varibles for system management.)
有什么办法可以得到吗？（例如USER、HOME、PATH，其实我需要获取我的自定义环境变量来进行系统管理。）
or I read about dbms_system.get_env() in PL/SQL. How can I using this from my SQL? (sorry, I'm closer to system administrator.)
或者我在 PL/SQL 中阅读了 dbms_system.get_env()。如何从我的 SQL 中使用它？（对不起，我更接近系统管理员。）
EDIT: what I want to do is something like below:
编辑：我想做的是如下所示：
=============================
java: query to get XXX_INFO
---------------------------
server:
=============================
|
=============================
dbms: select SOMETHING...
---------------------------
server: export XXX_INFO=myXXX
=============================
回答by San
You can use sys_context function to get information that you need. E.g - to get ip address of the system use it as
您可以使用 sys_context 函数来获取您需要的信息。例如 - 要获取系统的 IP 地址，请将其用作
select sys_context('USERENV', 'IP_ADDRESS') from dual
output
输出
| SYS_CONTEXT('USERENV','IP_ADDRESS') |
|-------------------------------------|
| 10.0.0.113 |
Follow this linkfor more information
点击此链接了解更多信息
https://docs.oracle.com/database/121/SQLRF/functions199.htm#SQLRF06117
https://docs.oracle.com/database/121/SQLRF/functions199.htm#SQLRF06117
回答by Jon Heller
Install DBMS_SYSTEM as SYSDBA, from SQL*Plus. Then create a function and grant execute on it to your user. Normally creating objects in SYS is a bad idea, but since since DBMS_SYSTEM is undocumented you probably want to avoid granting the whole package to anyone.
从 SQL*Plus 将 DBMS_SYSTEM 安装为 SYSDBA。然后创建一个函数并将它的执行授予您的用户。通常在 SYS 中创建对象是一个坏主意，但由于 DBMS_SYSTEM 未记录，您可能希望避免将整个包授予任何人。
SQL> @?/rdbms/admin/dbmsutil.sql
Session altered.
Package created.
Synonym created.
Grant succeeded.
Session altered.
SQL> create or replace function sys.get_env(p_var in varchar2) return varchar2 is
2 v_return_value varchar2(4000);
3 begin
4 dbms_system.get_env(p_var, v_return_value);
5 return v_return_value;
6 end;
7 /
Function created.
SQL> grant execute on sys.get_env to <your user>;
Grant succeeded.
SQL>
Now from your user:
现在来自您的用户：
SQL> select sys.get_env('ORACLE_HOME') from dual;
SYS.GET_ENV('ORACLE_HOME')
--------------------------------------------------
C:\app\oracle\product.1.0\dbhome_1
