CODEWITHSUNDEEP
oracle-databaseenvironment-variablessqlplus
John O
John O

Reputation: 5433

Can SQL*Plus read environment variables from the machine is it running on?

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.

Upvotes: 12

Views: 21469

Answers (4)

Jared Still
Jared Still

Reputation: 146

This can be done by using an empty sql script.

The script 'getvar.sql' just needs to be an empty file.

When sqlplus is called with 'getvar.sql', everything in the command line after that is stored to named substitution variables; &1, &2, etc

$  touch getvar.sql

$  sql /nolog @getval "Fred"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 13 13:10:33 2023

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SP2-0640: Not connected
@ > prompt &1
Fred

Upvotes: 0

Nicholas Sushkin
Nicholas Sushkin

Reputation: 13800

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

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

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

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191315

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.

Upvotes: 11

Lokesh
Lokesh

Reputation: 7940

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.

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

Upvotes: 0

Related Questions