how can read environment variable with sql statement in oracle

You can use sys_context function to get information that you need. E.g - to get ip address of the system use it as

select sys_context('USERENV', 'IP_ADDRESS') from dual

output

| SYS_CONTEXT('USERENV','IP_ADDRESS') |
|-------------------------------------|
|                          10.0.0.113 |

Follow this link for more information

https://docs.oracle.com/database/121/SQLRF/functions199.htm#SQLRF06117

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> @?/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\12.1.0\dbhome_1

Since Oracle 12c, you can use a procedure in SQL like this:

WITH
  FUNCTION get_env(p_name IN VARCHAR2) RETURN VARCHAR2 IS
    v_ret VARCHAR2(4000);
  BEGIN
    dbms_system.get_env(p_name, v_ret);
    RETURN v_ret;
  END;
SELECT get_env('HOME') FROM dual

Of course you will also need access to dbms_system in this case.

I can do this (on Exadata)

select *
from V$instance;

And this gets me:

STATUS, PARALLEL, ARCHIVER, LOG_SWITCH_WAIT, LOGINS, SHUTDOWN_PENDING, DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED, CON_ID, INSTANCE_MODE, EDITION, FAMILY, DATABASE_TYPE

Then:

select *
from V$database;

DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE#, PRIOR_RESETLOGS_TIME, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE#, CONTROLFILE_TYPE, CONTROLFILE_CREATED, CONTROLFILE_SEQUENCE#, CONTROLFILE_CHANGE#, CONTROLFILE_TIME, OPEN_RESETLOGS, VERSION_TIME, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, REMOTE_ARCHIVE,ACTIVATION#, SWITCHOVER#, DATABASE_ROLE, ARCHIVELOG_CHANGE#, ARCHIVELOG_COMPRESSION, SWITCHOVER_STATUS, DATAGUARD_BROKER, GUARD_STATUS, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING, PLATFORM_ID, PLATFORM_NAME, RECOVERY_TARGET_INCARNATION#, LAST_OPEN_INCARNATION#, CURRENT_SCN, FLASHBACK_ON, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, DB_UNIQUE_NAME, STANDBY_BECAME_PRIMARY_SCN, FS_FAILOVER_MODE, FS_FAILOVER_STATUS, FS_FAILOVER_CURRENT_TARGET, FS_FAILOVER_THRESHOLD, FS_FAILOVER_OBSERVER_PRESENT, FS_FAILOVER_OBSERVER_HOST, CONTROLFILE_CONVERTED, PRIMARY_DB_UNIQUE_NAME, SUPPLEMENTAL_LOG_DATA_PL, MIN_REQUIRED_CAPTURE_CHANGE#, CDB, CON_ID, PENDING_ROLE_CHANGE_TASKS, CON_DBID, FORCE_FULL_DB_CACHING, SUPPLEMENTAL_LOG_DATA_SR

In my case NAME is what I needed