Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> -> Oracle sqlplus and UNIX environment

Oracle sqlplus and UNIX environment

From: David Bath <>
Date: 27 Feb 92 02:18:55 GMT
Message-ID: <dtb.699157135@otto>

Here are some code fragments that allow me to control the behaviour of sqlplus by my UNIX environment and also allows me to create a sqlplus variable from my UNIX environment by the following:

      ( Imagine I have done the following prior to invoking sqlplus
	  $ FRED_HOME=/usr/local/fred
	  $ export FRED_HOME
      SQL> start $ORACLE_HOME/local/sqlplus/getenv FRED_HOME
      SQL> prompt &&FRED_HOME

This is REALLY handy in sqlplus scripts.

It also lets me set a UNIX environment variable

to "unixlike", "batch" or "interactive" and automagically set pausing, pagesize, etc to current terminal definitions if appropriate. The "unixlike" setting turns headings and pause off, linesize to the maximum and pagesize to 0.
The "batch" setting turns pause off
The "interactive" setting makes sure that pause is on.

The other REALLY useful variable to play with before going in to sqlplus is SQL_ARRAY_SIZE. - As an application on my size have a few V.WIDE tables, select * from table blows up with the default array size of 20 rows, and my users are too dumb to set it appropriately.

The following fragments are not polished yet, but should give you a few ideas as to how a SysAdmin can make life easier for programmers using sqlplus under UNIX. Certainly, have environmental control over sql bahaviour is a BIG bonus. I have little time or incentive to polish it further - it works here.

If you have C shell users then you will have a lot of work to do. BSD users will have to alter the stuff relating to terminal settings.

The disadvantages are as follows:

  1. sqlplus takes a bit longer to get going
  2. The first few characters input can be lost if you do not wait for the prompt.
  3. User must be able to write to $HOME (is this a problem ???)

Other things to note are the variables defined to sqlplus that I find useful :


		A variable unique to this process (until 32K processes

Reflects current ORACLE_SID
Reflects current ORACLE_HOME
Reflects home of the current user ( Good to " spool &&HOME/key&&TMPPID" )

Note that the "SQL>" prompt is replaced by the time, Oracle SID and SQL>, this is good when running multiple sessions, so I am reminded which database I am hitting. You could include the current oracle user login name, but this would be unreliable unless you did a major fiddle whenever you used the CONNECT statement. The &&OLOGNAME variable has a similar drawback.

The environment I use is AT&T UNIX System V with a Bourne/Korn shell on a Pyramid, but at some customers use C shell, the glogin.sql has had to be complicated a bit.

Let me know if you find this useful or have any really cute ideas for inclusion. Would it be worthwhile getting some "standard variables" set up across sites if this takes off ?

Hope this helps.......

David T. Bath
Global Technology Corporation
Melbourne, OZ

=========CODE FRAGMENTS BELOW HERE ====================

OK - first of all the relevant bits from /etc/profile
	# Create a LOGINPID variable
	# This is used to generate unique .sql or .lst logs
	    export LOGINPID

Now let's have a look at $ORACLE_HOME/dbs/glogin.sql

    rem SQL*Plus global login startup file.     rem
    rem This is the global login file for SQL*Plus.     rem Add any sqlplus commands here that are to be     rem executed when a user invokes sqlplus     rem
    rem This next bit defines the tmpsql variable in the     rem current sqlplus session
    host echo "define tmpsql=$$" > ${HOME}/${LOGINPID}.sql     start ${HOME}/${LOGINPID}.sql
    host rm ${HOME}/${LOGINPID}.sql
    rem Next bit runs ..../ to create an sqlplus script     rem unique to this process and starts it     host ${ORACLE_HOME}/local/etc/ > ${HOME}/tmp&&tmpsql..sql     start ${HOME}/tmp&&tmpsql..sql
    host rm ${HOME}/tmp&&tmpsql..sql

Now let's look at $ORACLE_HOME/local/etc/

    #!/bin/att /bin/sh
    echo "
    rem SQL*Plus startup script generated by ${ORACLE_HOME}/local/etc/"     # First off, the simple stuff
    echo "
    set echo off
    rem SQL*Plus startup script for ${LOGNAME:=anonymous} `date`     rem Generated by ${ORACLE_HOME}/local/etc/

	rem Initial sets for sqlplus
	    set echo off
	    set pause off
	    set verify off
	    set scan on
	    set feedback 0
	    set newpage 0
	    set time off
	    set timing off
	    set sqlprompt \"\"
	    set pagesize 0

	rem Make stuff available from OS
	    define _EDITOR=\"${EDITOR:-vi}\"
	    set arraysize ${SQL_ARRAY_SIZE:-10}
	rem Put in stuff for non-standard SQL facilities
	rem Some are related to sqlplus features, some to the OS
	    define SQL_PAUSE_PS=\"${SQL_PAUSE_PS:-Press enter to continue}\"
	    define SQL_PAUSE=\"${SQL_PAUSE:-on}\"
	    define PS_SQL=\"${PS_SQL:-${ORACLE_SID} SQL> }\"
	    define MAXLINESIZE=500
	    define MAXPAGESIZE=50000
	    define MAXCHARSIZE=255
	    set linesize &&MAXLINESIZE
	    define TMPPID=\"$$\"
	    define TMP=\"${TMP:-/tmp}\"
	    define UNIVERSE=\"`universe`\"
	    define ORACLE_SID=\"${ORACLE_SID}\"
	    define ORACLE_HOME=\"${ORACLE_HOME}\"
	    define SPOOL_DIR_ORACLE=\"${SPOOL_DIR_ORACLE:-`pwd`}\"
	    define HOME=\"${HOME}\"

    # Run a few things in the HOME of the user - allows user to put in
    # some smarts for themself.  MUCH better than looking in the
    # current directory for glogin - ho ho
    # These can include simple definitions
	test -r ${HOME}/.sqlrc && . ${HOME}/.sqlrc
    echo "
	    define SQL_PAUSE_PS=\"${SQL_PAUSE_PS:-Press enter to continue}\"
	    define SQL_PAUSE=\"${SQL_PAUSE:-on}\"
	    define PS_SQL=\"${PS_SQL:-${ORACLE_SID} SQL> }\"
    # Check if this is an interactive session or not     if tty -s
	echo "
	column T new_value OLOGNAME noprint;
	select USER T
	from   DUAL;
	set time on
	set pause ${SQL_PAUSE:=on}
	set pause \"&&SQL_PAUSE_PS\"
	echo "    set pause ${SQL_PAUSE:=off}"


    lines=`att tput lines`
    cols=`att tput cols`
    # Set up things based on TERM_SQLPLUS variable

	lines=`att tput -T ${TERM_SQLPLUS:=$TERM} lines`
	cols=`att tput -T ${TERM_SQLPLUS:=$TERM} cols`
	lines=`expr $lines - 1`
	echo "
	rem Rework the page and linesize settings
	    set pagesize ${lines:-60}
	    set linesize ${cols:-80}


    # Set up things base on OraSqlUse variable

	case "${OraSqlUse}" in
		echo "
		    set pause ${SQL_PAUSE:=off}
		    set feedback off
		    set pagesize 0
		    set linesize &&MAXLINESIZE
		    define PS_SQL=\"\"
		    set time off
		echo "
		    set pause off
		    set time off
		echo "
		    set pause ${SQL_PAUSE:=on}
		    set pause \"&&SQL_PAUSE_PS\"

    echo "
	    set sqlprompt \"&&PS_SQL\"

	rem End of ${ORACLE_HOME}/local/etc/


OK, Here comes $ORACLE_HOME/local/sqlplus/getenv.sql

    rem NAME
    rem $ORACLE_HOME/local/sqlplus/getenv.sql     rem DESCRIPTION
    rem Allow sqlplus to set variables from UNIX environment     rem NOTES

    rem 	You should give this one a parameter or else .....
    rem         In a simple Sys V universe and sh/ksh, you do not
    rem 	need a, simply use echo
    rem BUGS
    rem 	Can bomb if set escapes, scans, defines are set weird
    rem AUTHOR
    rem    David T. Bath 	(Global Technology Corporation) 	1990

    define getenvcmd="&&TMP/&&TMPPID..sql"     host $ORACLE_HOME/local/sqlplus/ &1 > &&getenvcmd     start &&getenvcmd
    host rm &&getenvcmd
    undefine getenvcmd

Here comes $ORACLE_HOME/local/sqlplus/

    # NAME
    #	$ORACLE_HOME/local/gtc/sqlplus/ - sql environment get
    # 	This shell script within $ORACLE_HOME/local/gtc/sqlplus/getenv.sql
    # 	is the cutting edge for the OS part.
    # USAGE
    # 	$ORACLE_HOME/local/gtc/sqlplus/ VARNAME
    # NOTES
    # 	Writes a line of sqlplus script to a temporary file which is then
    # 	executed, defining a variable in sqlplus with the same (although
    # 	case insensitive name) as the environment variable passed as the
    # 	first argument.
    # AUTHOR
    # 	David T. Bath (Global Technology Corporation)

    eval echo "define ${1}=\\\"\$${1}\\\" Received on Wed Feb 26 1992 - 20:18:55 CST

Original text of this message