Re: create directory using %ORACLE_HOME%

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 29 Aug 2002 12:27:34 +0200
Message-ID: <akksv4$du2$1@ctb-nnrp2.saix.net>

Jim Poe wrote:

> create or replace directory %ORACLE_HOME% || '\FIT_ADMN';
>
> This doesn't work as is. Is there another way to accomplish this?

Yes.

> The %ORACLE_HOME% environment variable seems to have limited use in
> SQLPLUS and does not work at all in PL/SQL.

Yes.

These variables are operating system environment variables. Not PL/SQL (or SQL*Plus) variables.

In theory, it is possible for PL/SQL to support it - there are calls (like GetEnvironmentVariable() on Win32) that allows an app access to the environment variables. However, SQL*Plus runs on a wide range of platforms. For x-platform portability, mucking around with something as unique and different as environment variables (totally dependant on the operating system and even its configuration), is a bad idea.

The trick to make it work is to use STDIN. The operating system shell processes and parses (to a limited extent) standard input. If you thus use an environmental variable in STDIN, it will be substituted with the variable's value.

In Unix this is very easy. Consider:

# export TABLE_NAME=foobar
# export CRITERIA="where col1 <= (sysdate-10)"
# sqlplus john/doe << EOF

select * from $TABLE_NAME $CRITERIA ;
exit;
EOF To explain - I'm redirecting STDIN to SQL*Plus. The redirection remains until the keyword EOF is encountered (it could be any keyword I want).

Similarly:
# echo "select * from $TABLE_NAME $CRITERIA ; exit;" > my_sql.sql # sqlplus john/doe @my_sql

Again, as the command line is processed by the shell, it will replace the environmental variables with the applicable values.

As you can see, this substitution happens (performed by the command shell) before SQL*Plus gets it hands on it.

On Windows platforms it is not that easy as the command shell language and features are limited. What you can do is use the echo option, i.e.

C:\> set TABLE_NAME=foobar
C:\> echo "SELECT * FROM %TABLE_NAME%; EXIT;" > my_sql.sql
C:\> sqlplus john/doe @my_sql

Or you can resort to Unix like shells on Windows (e.g. Cygnus or using Microsoft's Unix tools for NT/XP).

--
Billy
 

Received on Thu Aug 29 2002 - 05:27:34 CDT