Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 439
John O Avatar asked May 07 '15 17:05

John O


People also ask

What are the functions of the SQL*Plus environment?

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet.

What is SQL*Plus and why would you want to use it?

SQL*Plus is the command-line interface to the Oracle database. Its fundamental reason for existence is to allow you to enter and execute ad hoc SQL statements and PL/SQL code blocks.

Which is an SQL*Plus command?

SQL*Plus is a command-line tool that provides access to the Oracle RDBMS. SQL*Plus enables you to: Enter SQL*Plus commands to configure the SQL*Plus environment. Startup and shutdown an Oracle database.

How do you query an environment variable?

To Check if an Environment Variable ExistsSelect Start > All Programs > Accessories > Command Prompt. In the command window that opens, enter echo %VARIABLE%. Replace VARIABLE with the name of the environment variable.


1 Answers

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.

like image 93
Alex Poole Avatar answered Oct 28 '22 13:10

Alex Poole