initialize ()
{
initialize code
}
function1 ()
{
function stuff
}
Functions are the key to writing just about ANY program that is longer than a page or so of text. Essentially, its all a matter of breaking up a large program, into smaller, managable chunks. Ideally, functions are sort of like 'objects' for program flow. You pick a part of your program that is pretty much self-contained, and make it into its own 'function'
Why are functions critical?
Properly written functions should exist by themselves, and affect few things external to themselves. You should DOCUMENT what things it changes external to itself. Then you can look very carefully just at the function, and determine whether it actually does what you think it should do.
When your program isn't working properly (WHEN, not if), you can then put in little debug notes to yourself in the approximate section you think is broken. If you suspect a function is not working, then all you have to verify is
Is the INPUT to the function correct?
Is the OUTPUT from the function correct?
Once you have done that, you then know the entire function is correct, for that particular set of input(s), and you can look for errors elsewhere.
A trivial function
printmessage() {
echo "Hello, this is the printmessage function"
}
printmessage
The first part, from the first "printmessage()" all the way through the final '}', is the function definition. It only defines what the function does, when you decide to call it. It does not DO anything, until you actually say "I want to call this function now".
You call a function in ksh, by pretending it is a regular command, as shown above. Just have the function name as the first part of your line. Or any other place commands go. For example,
echo The message is: `printmessage`
Remember: Just like its own separate shellscript. Which means if you access "$1" in a function, it is the first argument passed in to the function, not the shellscript.
Debugging your functions
If you are really really having difficulties, it should be easy to copy the entire function into another file, and test it separately from the main program.
This same type of modularity can be achived by making separate script files, instead of functions. In some ways, that is almost preferable, because it is then easier to test each part by itself. But functions run much faster than separate shellscripts.
A nice way to start a large project is to start with multiple, separate shellscripts, but then encapsulate them into functions in your main script, once you are happy with how they work.
CRITICAL ISSUE: exit vs return
THE main difference when changing between shellscripts and functions, is the use of "exit".
'exit' will exit the entire script, whether it is in a function or not.
'return' will just quit the function. Like 'exit', however, it can return the default "sucess" value of 0, or any number from 1-255 that you specify. You can then check the return value of a function, just in the same way you can check the return value of an external program, with the $? variable.
# This is just a dummy script. It does not DO anything
fatal(){
echo FATAL ERROR
# This will quit the 'fatal' function, and the entire script that
# it is in!
exit
}
lessthanfour(){
if [[ "$1" = "" ]] ; then echo "hey, give me an argument" ; return 1; fi
# we should use 'else' here, but this is just a demonstration
if [[ $1 -lt 4 ]] ; then
echo Argument is less than 4
# We are DONE with this function. Dont do anything else in
# here. But the shellscript will continue at the caller
return
fi
echo Argument is equal to or GREATER than 4
echo We could do other stuff if we wanted to now
}
echo note that the above functions are not even called. They are just
echo defined
A bare "return" in a shellscript is an error. It can only be used inside a function.
CRITICAL ISSUE: "scope" for function variables!
Be warned: Functions act almost just like external scripts... except that by default, all variables are SHARED between the same ksh process! If you change a variable name inside a function.... that variable's value will still be changed after you have left the function!! Run this script to see what I mean.
#!/bin/sh
# Acts the same with /bin/sh, or /bin/ksh, or /bin/bash
subfunc(){
echo sub: var starts as $var
var=2
echo sub: var is now $var
}
var=1
echo var starts as $var, before calling function '"subfunc"'
subfunc # calls the function
echo var after function is now $var
To avoid this behaviour, and give what is known as "local scope" to a variable, you can use the typeset command, to define a variable as local to the function.
#!/bin/ksh
# You must use a modern sh like /bin/ksh, or /bin/bash for this
subfunc(){
typeset var
echo sub: var starts as $var '(empty)'
var=2
echo sub: var is now $var
}
var=1
echo var starts as $var, before calling function '"subfunc"'
subfunc # calls the function
echo var after function is now $var
Another exception to this is if you call a function in the 'background', or as part of a pipe (like echo val | function )
This makes the function be called in a separate ksh process, which cannot dynamically share variables back to the parent shell. Another way that this happens, is if you use backticks to call the function. This treats the function like an external call, and forks a new shell. This means the variable from the parent will not be updated. Eg:
func() { newval=$(($1 + 1)) echo $newval echo in func: newval ends as $newval } newval=1 echo newval in main is $newval output=`func $newval` func $newval echo output is : $output echo newval finishes in main as $newval
Write Comments!
Lastly, as mentioned in the good practices chapter, dont forget to comment your functions! While shellscripts are generally easier to read than most programming languages, you really can't beat actual human language to explain what a function is doing!
#!/bin/bash
cat textfile.txt |while read Name
do sqlplus -s <a href="mailto:userid/password@db_name">userid/password@db_name</a> > output.log <<EOF
set verify off
set heading off
select customerid
from customers
where customername like '%${Name}%';
/ exit EOF
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN instance_name FORMAT a13 HEAD 'Instance|Name / Number'
COLUMN thread# FORMAT 99999999 HEAD 'Thread #'
COLUMN host_name FORMAT a13 HEAD 'Host|Name'
COLUMN status FORMAT a6 HEAD 'Status'
COLUMN startup_time FORMAT a20 HEAD 'Startup|Time'
COLUMN database_status FORMAT a8 HEAD 'Database|Status'
COLUMN archiver FORMAT a8 HEAD 'Archiver'
COLUMN logins FORMAT a10 HEAD 'Logins?'
COLUMN shutdown_pending FORMAT a8 HEAD 'Shutdown|Pending?'
COLUMN active_state FORMAT a6 HEAD 'Active|State'
COLUMN version HEAD 'Version'
SELECT
instance_name || ' (' || instance_number || ')' instance_name
, thread#
, host_name
, status
, TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time
, database_status
, archiver
, logins
, shutdown_pending
, active_state
, version
FROM
gv\$instance
ORDER BY
instance_number
/
This is located in a common library
#!/usr/bin/ksh
# Purpose: Search the alert log of $ORACLE_SID for error
# messages.
# If any error messages are found, send notification
# (see Notes below).
#
# Prereqs: $ORACLE_SID environment variable must be set or
# passed as a positional parameter. Also, $ORATAB
# environment variable must be set.
#
# Notes: If this script is invoked from a terminal session,
# display the results to the screen (STDOUT); otherwise,
# send a mail message to the specified mail accounts.
#
# Exit Status: 99 = Required input, $ORACLE_SID, not supplied.
# 90 = Invalid Oracle sid.
# 95 = init.ora file not found for input sid.
# 97 = Alert log file does not exist.
#
# Author: Michael Culp
# Date: May 2002
#===============================================================
# R E V I S I O N H I S T O R Y
#===============================================================
# Who:
# Date:
# Comments:
################################################################
function error_notification
{
if [ "$TERMINAL" = "Y" ]; then
echo "$ERROR_MSG"
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
echo "$ERROR_MSG" | mail $(sendvar)
done
fi
}
export SEND_MAIL_TO='mike.culp@gmail.com'
export SEND_MAIL_TO2='mike.culp@gmail.com'
SERVER=$(uname -n)
DATE=$(date +%c%t)
ERROR_MSG_FILE=/home/oracle/alertlog_message
# check to see if command is being executed interactively
/usr/bin/tty -s
STATUS=$?
if [ $STATUS = 0 ]
then
clear
TERMINAL=Y
else
TERMINAL=N
fi
# Test for required inputs.
# Positional parameters override env variables.
if [ ${1:-0} = 0 ]; then
if [ ${ORACLE_SID:-0} = 0 ]; then
ERROR_MSG=`echo "ORACLE_SID must be supplied as input to $0"`
error_notification
exit 99
fi
else
export ORACLE_SID=$1
fi
# Call the validate_sid function to verify ORACLE_SID is valid.
# The function also sets the ORACLE_HOME and ORACLE_BASE variables.
validate_sid
if [ "$VALID_SID" != "Y" ]; then
ERROR_MSG=`echo "ORACLE_SID ($ORACLE_SID) is invalid. Script is $0"`
error_notification
exit 90
fi
INIT_PATH="$ORACLE_HOME"/dbs
INIT_FILE="$INIT_PATH"/init"$ORACLE_SID".ora
if [ ! -s "$INIT_FILE" ]; then
ERROR_MSG=`echo "$INIT_FILE does not exist. Script is $0"`
error_notification
exit 95
fi
LOG_PATH="/"`awk -F= '/background_dump_dest/ { print $2 }' "$INIT_FILE" |tr -s " "|cut -f2-30 -d/`
ALERTLOG="$LOG_PATH"/alert_"$ORACLE_SID".log
# Create a list of valid Oracle error message prefixes by parsing
# the oerr script in $ORACLE_HOME/bin
/home/oracle/bin/awk_message_prefixes > /tmp/ora_prefixes
# Archiver error message prefix is not included in the oerr
# script. Add an entry for this into the "ora_prefixes" file.
echo '^ARC[0-9]: Error' >> /tmp/ora_prefixes
# Search the alert.log for any occurrences of an Oracle error
# message prefix.
grep -bsf /tmp/ora_prefixes $ALERTLOG > $ERROR_MSG_FILE
status=$?
if [ $status -eq 1 ]; then # no matches found
if [ $TERMINAL = 'Y' ]; then
echo "Amazingly enough, there are NO errors in $ALERTLOG"
fi
elif [ $status -gt 1 ]; then
ERROR_MSG=`echo "$ALERTLOG does not exist."`
error_notification
exit 97
fi
# If there are any messages in the message file, send
# appropriate notifications.
if [ -s $ERROR_MSG_FILE ]; then
echo "\nMessages from server $SERVER on $DATE" >> $ERROR_MSG_FILE
if [ $TERMINAL = 'Y' ]; then
cat $ERROR_MSG_FILE
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
mail $(sendvar) < $ERROR_MSG_FILE
done
fi
fi
if [ -f $ERROR_MSG_FILE ]; then
rm $ERROR_MSG_FILE
fi
if [ -f /tmp/ora_prefixes ]; then
rm -f /tmp/ora_prefixes
fi
exit
exec DBMS_STATS.GATHER_TABLE_STATS( ownname=>'SCHEMA_OWNER',
tabname=>'SOME_TABLE',
degree=>16,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
granularity => 'AUTO',
cascade => TRUE,
no_invalidate => FALSE);
#!/bin/bash
while read line
do /opt/oracle/bin/sqlplus -s <a href="mailto:user@db/password">user@db/password</a> @query.sql $line
done < /tmp/textfile.txt
BEGIN
FOR c IN (SELECT c.owner,
c.table_name,
c.constraint_name
FROM user_constraints c,
user_tables t
WHERE c.table_name = t.table_name AND
c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
END LOOP;
END;
/
BEGIN
FOR c IN (SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name AND
c.status = 'DISABLED'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END; /
SELECT DECODE(value,
NULL,
'PFILE',
'SPFILE') "Init File Type"
FROM sys.v_$parameter
WHERE name = 'spfile';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
set linesize 200
col host_name for a45
select INST_ID,
INSTANCE_NAME,
HOST_NAME,
STARTUP_TIME DATABASE_STATUS,
status
from gv\$instance;
Here is a nice little script I like to use for showing the database size for various components.
#!/bin/ksh ############################################################################
# Script Name..: db_overall_size.ksh
# Description..:
# Author.......:
# Date.........: 04/10/2013
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
# ############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 200
set pages 200
spool db_size.txt set feed off
column systot format 99,990.99 heading "System |Alloc|GB"
column sysused format 99,990.99 heading "System |Used|GB"
column systm format 99,990.99 heading "System Tbsp|MB"
column sysax format 99,990.99 heading "Sys Aux|GB"
column systmusd format 99,990.99
column sysused format 99,990.99
column undotb format 99,990.99 heading "Undo|GB"
column datatot format 99,990.99 heading "Data|Alloc|GB"
column dataused format 99,990.99 heading "Data|Used|GB"
column tmptot format 99,990.99 heading "TEMP|Alloc|GB"
column redotot format 99,990.99 heading "Online|Redo|MB"
column ctltot format 99,990.99 heading "Ctrl File|MB"
column systall format 99,990.99 column systused format 99,990.99
column fratot format 99,990.99 column fraused format 99,990.99
column total_gb format 99,990.99 column free_gb format 99,990.99
select name from v\$database;
select s.system_size SYSTOT,
s.system_size-f1.free_mb SYSUSED,
st.system_size SYSTM,
sa.system_size SYSAX,
ud.system_size UNDOTB,
d.data_size DATATOT,
d.data_size-f2.free_mb DATAUSED,
t.temp_size TMPTOT,
r.redo_size REDOTOT,
c.controlfile_size CTLTOT,
s.system_size+t.temp_size+r.redo_size+c.controlfile_size SYSTALL,
s.system_size-f1.free_mb+t.temp_size+r.redo_size+c.controlfile_size SYSTUSED,
fra.fratot FRATOT,
fra.fraused FRAUSED
from (select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like 'SYSTEM' or
tablespace_name like 'SYSAUX' or
tablespace_name like 'UNDO%' or
tablespace_name like 'TOOLS' or
tablespace_name like 'USERS') s,
(select sum(bytes)/1024/1024/1024 data_size
from dba_data_files
where tablespace_name not like 'SYSTEM' and
tablespace_name not like 'SYSAUX' and
tablespace_name not like 'UNDO%' and
tablespace_name not like 'TOOLS' and
tablespace_name not like 'USERS') d,
(select sum(bytes)/1024/1024 system_size
from dba_data_files
where tablespace_name like 'SYSTEM' ) st,
(select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like 'SYSAUX' ) sa,
(select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like 'UNDO%' ) ud,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files) t,
(select sum(bytes)/1024/1024 redo_size
from sys.v\$log) r,
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v\$controlfile) c,
(select sum(bytes)/1024/1024/1024 FREE_MB
from dba_free_space
where tablespace_name like 'SYSTEM' or
tablespace_name like 'SYSAUX' and
tablespace_name like 'UNDO%' and
tablespace_name like 'TOOLS' and
tablespace_name like 'USERS') f1,
(select sum(bytes)/1024/1024/1024 FREE_MB
from dba_free_space
where tablespace_name not like 'SYSTEM' and
tablespace_name not like 'SYSAUX' and
tablespace_name not like 'UNDO%' and
tablespace_name not like 'TOOLS' and
tablespace_name not like 'USERS') f2,
(select name, (SPACE_LIMIT)/1024/1024/1024 FRATOT, (space_used)/1024/1024/1024 FRAUSED
from V\$RECOVERY_FILE_DEST) fra;
spool off
EOF
Here is a little header that I like to use for my shell scripts for Oracle code,
feel free to suggest mods
#!/bin/ksh ############################################################################ # Script Name..: <script name>.ksh # Description..: # Author.......: Michael Culp # Date.........: 04/ /2013 # Version......: # Modified By..: # Date Modified: # Comments.....: # Schema owner.: # : alter session set current should be used to change schemas # Login User...: # Run Order....: # Dependent on.: # Script type..: # ############################################################################ sqlplus -s "/ as sysdba" <<EOF set lines 150 set pages 150 -- spool <some file name> -- spool off EOF
Compression Check Script
#!/bin/ksh
############################################################################
# Script Name..: comp_chk.ksh
# Description..: Estimate the compression for a table
# Author.......: Michael Culp
# Date.........:
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set serveroutput on
-- spool <some file name>
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(SCRATCHTBSNAME=>'USERS',
OWNNAME=>'CFGADM',
TABNAME=>'EQUIPMENT',
PARTNAME=>null,
COMPTYPE=>2, ---2 means OLTP
BLKCNT_CMP =>lv_BLKCNT_CMP,
BLKCNT_UNCMP =>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio.: '||lv_cmp_ratio);
dbms_output.put_line('2. Block Count.......: '||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type.......: '||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed...: '||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed: '||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed : '||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed: '||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
-- spool off
EOF