Forum Stats

  • 3,824,942 Users
  • 2,260,442 Discussions


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

William Robertson
William Robertson Member Posts: 9,567 Bronze Crown
edited Jan 19, 2020 7:05AM in Database Ideas - Ideas

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.

BPeaslandDBAberxLothar FlatzDeepak Kamath-OracleFamilyGuy81Sven W.
6 votes

Active · Last Updated


  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    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
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Mar 11, 2016 9:50AM

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

    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
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jan 6, 2020 12:56AM

    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


    SQL> @shellvar

    SQL> def _os

    DEFINE _OS             = "Windows_NT" (CHAR)

    SQL> def _comspec

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


    On Linux running the same sqlplus commands:

    SQL> @shellvar

    SQL> def _os

    DEFINE _OS             = "%OS%" (CHAR)

    SQL> def _comspec



    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.

    Sven W.