os environment variables in sql-queries

[lowlander]

How can I use os (windows) environment variables like
'%ORACLE_HOME%' in sql-queries (+ pl/sql)

 

[jee]

One way is to use an operating command or batch file to dynamically generate a SQL script (using the environment variables as you wish) that can be executed by SQL*PLUS.

For instance (Windows cmd file):

ECHO select * from filename > script.sql
ECHO where columnname = '%xxx%' >> script.sql
ECHO exit >> script.sql
%orclhome%\BIN\SQLPLUS.EXE -silent sys/password @script.sql
DEL script.sql

 

[Guest_imported]

While there are clever ways to get information from
the operating system (pipes, extproc, etc), there is a lot of information in v$parameter and v$session that may be of use to you as well...