SQL*Plus built-in DEFINE variable to indicate OS platform

William Robertson

This may be a tiny thing next to adding a Boolean type to the SQL language etc, but it would be really useful for writing portable SQL*Plus scripts if there was a built-in underscore variable (similar to _SQLPLUS_RELEASE etc) indicating the base platform of the SQL*Plus executable. (Or the invoking shell program e.g. cmd.exe, bash etc, although I can see that would take more effort than essentially a constant string that Oracle could include in each branch when compiling.)

As it is, if we want to have a SQL*Plus script delete a temp file (for example), we have to use elaborate workarounds like checking for slash characters in sys_context('userenv','host') and sys_context('userenv','terminal') or a .exe extension in  sys_context('userenv','client_program_name') or v$session.program for the current session, in order to decide whether to use del or rm.

Lothar Flatz

I like the idea. I just think that it should be a pseudo column like user rather than a define variable. Is there any predefined define variable at all?

William Robertson

There are a number of predefined variables. However none of them are platform-specific:

http://docs.oracle.com/database/121/SQPUG/ch_twelve017.htm#SQPUG038

It can't be a pseudocolumn - this is the SQL*Plus client I am talking about, not SQL or the database server. (We can get the server platform from v$database.platform_name, dbms_utility.port_string etc.)

Gaz in Oz

You can already DEFINE your own variable in sqlplus by running a .sql file utilising the sqlplus HOST command.

For example on windows:

SQL> host more shellvar.sql

set termout off

host echo define _OS=%OS% >shell.sql

host echo define _COMSPEC=%COMSPEC% >>shell.sql

@shell.sql

SQL> @shellvar

SQL> def _os

DEFINE _OS             = "Windows_NT" (CHAR)

SQL> def _comspec

DEFINE _COMSPEC        = "C:\Windows\system32\cmd.exe" (CHAR)

SQL>

On Linux running the same sqlplus commands:

SQL> @shellvar

SQL> def _os

DEFINE _OS             = "%OS%" (CHAR)

SQL> def _comspec

DEFINE _COMSPEC        = "%COMSPEC%" (CHAR)

SQL>

so you can check the value/s using '&_OS' and/or '&_COMSPEC' and act accordingly.

Admittedly it will only tell you whether the OS is windows, or it isn't.