Oracle sqlplus and UNIX environment
Date: 27 Feb 92 02:18:55 GMT
Message-ID: <dtb.699157135_at_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
/usr/local/fred
SQL>
This is REALLY handy in sqlplus scripts.
It also lets me set a UNIX environment variable
OraSqlUse
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:
- sqlplus takes a bit longer to get going
- The first few characters input can be lost if you do not wait for the prompt.
- 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 :
&&TMPPID
A variable unique to this process (until 32K processes later).
&&ORACLE_SID
Reflects current ORACLE_SID
&&ORACLE_HOME
Reflects current ORACLE_HOME
&&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.......
Regards
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 LOGINPID=$$ 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
rem
rem Next bit runs ..../sqlrc.sh to create an sqlplus script
rem unique to this process and starts it
host ${ORACLE_HOME}/local/etc/sqlrc.sh > ${HOME}/tmp&&tmpsql..sql
start ${HOME}/tmp&&tmpsql..sql
host rm ${HOME}/tmp&&tmpsql..sql
Now let's look at $ORACLE_HOME/local/etc/sqlrc.sh
#!/bin/att /bin/sh
echo "
rem SQL*Plus startup script generated by ${ORACLE_HOME}/local/etc/sqlrc.sh"
# 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/sqlrc.sh
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
then
TERM_SQLPLUS="${TERM_SQLPLUS:=$TERM}"
export TERM_SQLPLUS
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\"
"
else
echo " set pause ${SQL_PAUSE:=off}"
fi
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=${lines:=60}
lines=${LINES:-$lines}
lines=`expr $lines - 1`
cols=${cols:=80}
cols=${COLUMNS:-$cols}
lines=${LINES:-$lines}
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
unixlike)
echo "
set pause ${SQL_PAUSE:=off}
set feedback off
set pagesize 0
set linesize &&MAXLINESIZE
define PS_SQL=\"\"
set time off
"
;;
batch)
echo "
set pause off
set time off
"
;;
interactive)
echo "
set pause ${SQL_PAUSE:=on}
set pause \"&&SQL_PAUSE_PS\"
"
esac
echo "
set sqlprompt \"&&PS_SQL\"
rem End of ${ORACLE_HOME}/local/etc/sqlrc.sh
"
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 getenv.sh, 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/getenv.sh &1 > &&getenvcmd
start &&getenvcmd
host rm &&getenvcmd
undefine getenvcmd
Here comes $ORACLE_HOME/local/sqlplus/getenv.sh
#!/.attbin/sh
# NAME
# $ORACLE_HOME/local/gtc/sqlplus/getenv.sh - sql environment get
# DESCRIPTION
# 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/getenv.sh 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 Thu Feb 27 1992 - 03:18:55 CET