Intro Input VariablesInterpretingvariables

ifs Loops MiscIndex ofcommands

Scripting rules

Scripts: commands in files

A program or command is executed by typing the name, with any inputs after it. A file with commands in it may be executed the same way, if it has execute permission (chmod u+x file or chmod 700 file.) These are called scripts. Lines in the file are run as if they were typed at the keyboard. There are a few extra commands that are only useful in scripts:

script sample runs% cat abcls# First line:head -n1 abcecho done boss

% abcaardvark.dat abc stuff.txtlsdone boss

The echo UNIX command prints all of it's inputs to the screen, with a space between them. A # at the start of any line is a comment -- the line is ignored (but #! on the first line is special.) As usual with inputs, extra spaces are ignored. ""'s can save spaces (they really turn it all into one space-containing input):

script sample run

echo a b cecho "a b c" d

% abca b ca b c d

The second echo has two inputs: "a   b   c" and d.

To keep echo on the same line, use the -n option:

script sample run% cat abcecho -n echo testecho is hereecho again

% abcecho test is hereagain

Input/Parameters

Inputs are "numbered" $1, $2, $3, ... . $0 is the command name. $# is the number of parameters, and is made automatically when you run a script. Parameters not entered can be used, and are "" (no letters.)

script sample runs

echo "Running:" $0echo $2 $4 $6 $8echo "Total inputs: " $#

% xyz a b c d e f g h i j k lRunning: xyzb d f hTotal inputs: 12

% xyz baby doggyRunning: xyzdoggyTotal inputs: 2

$@ is a short-cut for $1 $2 $3 ... . It is all of the inputs, with spaces between them. It is mostly useful to have a for loop (later) look at all inputs. The shift command moves all input parameters (not $0) "left." It removes $1, moves $2 to $1, and so on. It can also be useful in loops:

script sample runsecho All: $@shiftecho "New first:" $1shiftecho "New first:" $1echo "All:" $@

%xyz a b c d e fAll: a b c d e fNew first: bNew first: cAll: c d e f

Variables

You can make your own variables just by picking a name. Give it a value with = (there cannot be any spaces on either side of the =.) To look up the value, put a $ in front of the name:

script sample runsa=grumpyb="sleepy sneezy"echo a X becho $a X $bls=dopeyecho ls X $lsls$lsa=X$1${a}Yecho "-->" $a

%xyz happya X bgrumpy X sleepy sneezyls X dopeyxyz stuff.txt scripts.htmldopey: command not found--> XhappygrumpyY

This is different from many computer languages, when it is obvious you are using a variable (examples: =, for a in, read a) leave out the $. When you want to look in a variable, use the $. It is the only way the script can tell the letter a from the contents of box a.

In the line a=X$1${a}Y, the {}'s are to let bash know you want the variable a. Without it, it would look up (the nonexistant) $aY. {}'s are optional when they are not needed (${1} is the same as $1.)

Interpreting variables and commands

Variables, like $abc or $1, are substituted before most other things happen. This can make new words, that are checked in the normal way. You can do some strange tricks with this, such as putting two things together to make a command, or a pathname:

script sample runs

% cat abc$1$2$1 $2/stuff.txt

% abc l saardvark.dat abc stuff.txtl: command not found

%abc cat xxxcatxxx: no such commandcat: xxx/stuff.txt: file not found

Most special characters (like *) can be put in double-quotes to treat them as letters. But, variables or parameters (or anything with a $) are still looked up. To treat $ as a normal character, use single quotes (' next to ENTER, not the upper-right -- that is a back-quote):

script sample runsecho "abc$1de$2f"a=lazyecho "a$a" 'a$a'echo $a"te"

% abc tiger pumaabctigerdepumafalazy a$alazyte

Besides "start of line=run this command", the script can't tell the difference between the letter 5, the number 5 (seems silly, but the letter 5 plus the letter 0 is 50; sometimes we want that,) the command 5 (you can name a script 5) or the 5th input ($5), unless you tell it. You can force something not at the start of a line to be a command with $(word). This is completely different from the $ to look up a variable. It can be useful for assigning variables, and, later, is useful in loops:

script sample runsa=$( echo $1 | cut -c1-2)echo First two letters are $als=xyzecho lsecho $lsecho $(ls)xyz=lsecho $($xyz)

% abc pantherFirst two letters are palsxyzaardvark.dat abc stuff.txtaardvark.dat abc stuff.txt

To do math, use $((math)). Again, this use of $ has nothing to do with variables at all:

script sample runsabc=10echo 1+4 $1+4 $1+$abcecho $((1+4)) $(($1+4)) $(($1+$abc))echo $((1+1))+$((2+2))echo $(1+1)

% abc 61+4 6+4 6+105 10 162+41+1 : command not found

Ifs and tests

It is possible to check parameters and variables to see if they are a certain word; or are less than, greater than, ... ; or check if they are filenames, directories, ... . This is done using an if with the test command (%info test for all of the rules):

script sample runsif test $1 -lt 10then echo $1 is less than 10fiif test -f $1then echo $1 is in this direlse echo $1 isnt a file!fi

% abc 66 is less than 106 isnt a file!

% abc stuff.txtline 3: not a math expressionstuff.txt is in this dir

then and fi are required. The else is optional. Indentation is just to make it look nice. Errors (if test stuff.txt -lt 10) will produce a message, but the script keeps running.

Brackets are a shorthand for the test command (but they need spaces around them.) if test $1 -eq 2 is the same thing as if [ $1 -eq 2 ].

Many people prefer to combine the if and then (using a semicolon.) Oddly, then may not have a semi colon after it: if test $1 -le 10; then echo "input is 10 or less"; fi.

An if may be nested inside another if, following the same rules. This checks whether both inputs are files:

script sample runs# swap two files (if they exist):if test -f $1then if test -f $2 then mv $1 tempfile.temp mv $2 $1 mv tempfile.temp $2 else echo "$2 is not a file. Quitting" fielse echo "$1 isn't a file"fi

%swap xhgj abc.datxhgj is not a file. Quitting

%swap abc.dat hkjshkjs isn't a file

%swap abc.dat def.dat

The middle if is indented, with its else and fi lined up, to hopefully make it a little easier to read.

An alternate would be to combine into one big if with nots and ors (these can get tricky) to say "if $1 or $2 isn't a file": if [ ! -f $1 -o ! -f $2 ].

An if does not need a test command after it. The if really just runs the next command and checks the "exit status." An exit status of 0 means true. For examples, test 5 -gt 8 has an exit status of 1 (which means false,) test -d "WWW" might have an exit status of 0 (there is a WWW dir,) cat a.dat has exit status 0 and ls notafile has exit status 1. The man pages describe the exit status's. In general, "normal" unix commands use: 0=no errors, not-0=type of error. Even after an if, they will also produce output (which you should redirect to /dev/null.) For example, if cat abc.dat &> dev/null is a (bad) way to check if abc.dat exists (recall &> is a short cut for > and 2> combined.)

test was specially written to produce a useful exit status for use with if. But, for example, grep fox a.dat prints lines with the word "fox" and returns 0 if there were any. This is a way to say "if file $1 has the word 'fox' in it": if grep fox "$1" > /dev/null

Loops

The most useful type of loop looks like:

for a in LIST OF STUFFdo# thing to do goes here echo -n "X${a}X"done

It expects a list, separated by spaces, after the for a in. a is a variable (probably one you haven't used before.) It assigns each item in the list to a and performs the lines between do and done for each. In this case, a will be LIST, then OF, then STUFF, and the output will be XLISTX XOFX XSTUFFX.

This is useful when you use a command to generate the list:

for a in $@ Every input for a in $(cat a.txt) Every word in a.txt for a in $(cat a.txt | tr " " "#") Really bad way to get every line in

a.txt for a in $(ls) Everything in current directory for a in $(find . -name " ") Everything in current dir, recursively (find

gives complete path names.) Every line in a.txt (until a blank line). It is tricky:

cat a.txt | (

read awhile test -n $ado#use $a here read adone )

 Things in ()'s count as one command. cat a.txt pipes a.txt to the loop. read a reads lines from the keyboard, which is a.txt because of the pipe. test -n checks a is not nothing (a blank line.)A better way to do it is test $? -neq 0. $? is true if the last command "worked." read a "works" if there was a line left in the file. This is very similar to a loop in a "real" programming language.

Counting to 10:

count=0while test $count -lt 10do#do something for 0,1,2...9 count=$(($count+1))done

Misc problems

text file busy: Using cat > abc to write to abc works fine, but legally ends with ^D (the end-of-file character.) Typing ^Z suspends the command. This means it is still running, just not going anywhere. It can be restarted later. If you try to run abc the computer may think you are still writing to it (technically, you are) and tell you the text file is busy right now.

bash: abc not found: When a bash script is run, it first runs .bashrc. Our system guys added a line at the end of .bashrc, cd ~/, that fixes some problem somewhere else and makes bash not really work. They assumed everyone uses the older sh instead of bash.What happens is you type % abc, the computer sees is a script (not a compiled program) and starts a new bash program to run it. It runs .bashrc, goes home, and then can't find abc. Comment out that line to fix this problem. For fun, add a line echo "Running, boss" to see it really does run .bashrc.

Typing % sh abc runs it under the old sh shell. Typing %bash abc runs it under bash. Typing just % abc runs it under your current shell (which is probably bash (%finger

username to check.) A "real" shell always starts with a line like: #! /bin/sh which tells it which shell to use for running the script (yes, # is a comment, but #! as the first thing is special.)

cd in a script: In technical-speak, a script forks off a new process that gets a copy of the current state. That means it is like opening a new window that starts in the same directory as when you ran it. As usual, if you give a long path to run a script from a different directory, it still starts in the current dir. A script can use cd in the normal way, and will change the directory for the script, but as soon as the script ends, the imaginary window closes and you are back where you started, in the same dir.

Index of commands

$1, $2, ... First input, second input, ... $0 Name of script (command you typed to run it.) $# Number of inputs

$@ All inputs (1-end) with a space between $? Return value of last command (tricky to use.)

shift move all inputs one smaller ($1=$2,) reduces $# by one. # Comment. Must be first thing in line $abc Look up value of abc variable $(abc) Run command "abc". $($abc) looks up abc and runs it. $((3+4)) Do math. Without this, 3+4 is three-letter word "3+4" ${abc} {}'s are optional parenthesis. They are needed for parms past 9 (${10}). ${1:3} Substring of $1, starting at 3rd position. ${abc:2:4} Substring of abc,

starting at 2nd position and using next 4 characters. [ ] Short cut for test

if test -f stuff.datthen

fi

if test happy -eq $1then

else

fi

General format for if

for a in this that otherdo

done

while test $1 -le 5do

done

General format for for and while

read a Read one line from keyboard and put in variable a 'abc' (quotes near ENTER): Treat this as a bunch of letters. "abc" (double quotes): Expend all $-rules, but otherwise treat as a bunch of letters. `abc` (back-tic [upper-left]): same as $(abc). Runs command. eval abc: Evaluate.

Also same as $(abc).

script sample runfor a in 4 8 3 7 13 4do echo -n $a" " if test $a -ge 5 then echo "big" else echo "small" fidone

%forexample4 small8 big3 small7 big13 big4 small

The lines between do and done are run six times, with a equal to each number. -ge means >=. Indentation is just to make it easier to read.

script sample runif [ $# -ne 1 -o ! -f $1 ]then echo "Usage: $0 FILENAME" exit 1fi

words=0for a in $(cat $1)do# echo "["$a"]"words=$((1+$words))done

echo "Num of words is" $words

%wordcount abc.datNum of words is 32

$(cat $1) simply "pastes" that file after for a in, which then looks at every word. Note the output of cat is automatically redirected (it does not go to the screen.) words is a standard counter, with one added for each word, in the awkward script manner. If you have a lot of math, use a more formal programming language.

The first part is a standard check: if there is not one input, or it is not a file (-o (letter O) is or, ! is not -- see %info test)

How do I get Oracle to automatically start when my server boots up?

Make sure the entry for your database SID in the ORATAB file ends in a capital "Y". Eg:

# $ORACLE_SID:$ORACLE_HOME:[N|Y]#ORCL:/u01/app/oracle/product/8.0.5:Y#

The scripts for starting and stopping databases are: $ORACLE_HOME/bin/dbstart and dbshut. SQL*Net (or Net8) is started with the lsnrctl command. Add the following entries to your /etc/rc2.d/S99dbstart (or equivalent - for example HP-UX uses /sbin/rc2.d/*) file:

su - oracle -c "/path/to/$ORACLE_HOME/bin/dbstart" # Start DB'ssu - oracle -c "/path/to/$ORACLE_HOME/bin/lsnrctl start" # Start listenersu - oracle -c "/path/tp/$ORACLE_HOME/bin/namesctl start" # Start OraNames (optional)

[edit] Can I trace a Unix process id to a SID and SERIAL#?

Yes, v_$process.spid contains the Unix process id. See example script at http://www.orafaq.com/scripts/performance/unixusr.txt

[edit] How does one SELECT information into a vi-file?

When using vi to edit SQL*Plus or Pro*C code, sometimes one need to insert a table definition or data values into the file. You can simply open a new line, put 'desc EMP' (or a SELECT statement) into it and type:

:.,.!sqlplus -s /

... automatically output from your statement is put in the vi buffer for cutting and pasting. One can even pipe the output through grep, awk, sed or perl before inserting into the file.

[edit] How does one SELECT a value from a table into a Unix variable?

One can select a value from a database column directly into a Unix environment variable. Look at the following shell script examples:

#!/bin/shVALUE=`sqlplus -silent user/password@instance <<ENDset pagesize 0 feedback off verify off heading off echo off

select max(c1) from t1;exit;END`if [ -z "$VALUE" ]; then echo "No rows returned from database" exit 0else echo $VALUEfi

Second example, using the SQL*Plus EXIT status code (can only return integer values smaller than 256):

#!/bin/kshsqlplus -s >junk1 /nolog <<EOFconnect user/password@instancecolumn num_rows new_value num_rows format 9999select count(*) num_rows from table_name;exit num_rowsEOFecho "Number of rows are: $?"

Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.

#!/bin/kshsqlplus -s /nolog |& # Open a pipe to SQL*Plus

print -p -- 'connect user/password@instance'print -p -- 'set feed off pause off pages 0 head off veri off line 500'print -p -- 'set term off time off'print -p -- "set sqlprompt "

print -p -- "select sysdate from dual;"read -p SYSDATE

print -p -- "select user from dual;"read -p USER

print -p -- "select global_name from global_name;"read -p GLOBAL_NAME

print -p -- exit

echo SYSDATE: $SYSDATEecho USER: $USERecho GLOBAL_NAME: $GLOBAL_NAME

Note: In all these examples we use the -s or -silent option to suppress SQL*Plus banners. The /nolog option indicates that the script will login to the database. This prevents Unix from displaying your userid and password in the Unix process list (ps -ef).

[edit] How to start Enterprise Manager

When you can't access https://your-hostname:1158/em then the enterprise manager (EM) is down and needs to be started:

For sh, bash, ksh:

ORACLE_SID=YOURDBSID export ORACLE_SID$ORACLE_HOME/bin/emctl start dbconsole

For csh, tcsh:

setenv ORACLE_SID YOURDBSID$ORACLE_HOME/bin/emctl start dbconsole

Connecting to oracle database from unix shell script

How to connect to database through Unix Shell Script

In this post we will how to connect Oracle database through unix shell scripting

Connecting to oracle database and accessing data from shell script

$ cat sqlconnect.sh#!/bin/bashsqlplus / << EOFSQLselect sysdate from dual;exit;EOFSQL$

We will get sysdate as output with Oracle bannerOutput of above code

$ ./sqlconnect.shSQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 22 10:52:26 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining optionsSQL>SYSDATE---------22-AUG-09SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options$

To remove oracle banner from the output connect the database in silent mode (use -s option)

Connect to a database via UNIX Shell Script in silent mode

$ cat sqlconnect.sh#!/bin/bashsqlplus -s / << EOFSQLselect sysdate from dual;exit;EOFSQL$ ./sqlconnect.shSYSDATE---------22-AUG-09$

Connect to a Database in Shell Programming and storing the output in UNIX variable

Use back ticks to do the same as

$ cat sqlconnect.sh#!/bin/bashORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQLset head offselect ename from emp where empno=7839;exit;EOFSQL`echo emp name : $ORACLE_VALUE$ ./sqlconnect.shemp name : KING$

To get access two columns data and storing result in Unix variables

$ cat sqlconnect.sh#!/bin/bashORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQLset head offselect empno':'ename from emp where empno=7839;

exit;EOFSQL`echo emp no : `echo $ORACLE_VALUE cut -d ":" -f1`echo emp name : `echo $ORACLE_VALUE cut -d ":" -f2`$ ./sqlconnect.shemp no : 7839emp name : KING$

How to get data from a database table into UNIX file using shell script

Using a Shell Script to connect to sql database and execute db query from a unix shell script

$ cat sqlconnect.sh#!/bin/bashsqlplus -s scott/tiger << EOFSQLspool emp.datselect ename from emp where empno=7839;spool offexit;EOFSQL$ ./sqlconnect.sh

ENAME----------KING$ ls -ltrtail -1-rw-r--r-- 1 scripter scripterworld 245 Aug 22 10:55 emp.dat$

Startup and shutdown Oracle Database with UNIX shell script

To startup oracle database from UNIX shell script

sqlplus -s "/ as sysdba" << EOFstartup;exit;EOF

To shutdown oracle database from unix shell script

sqlplus -s "/ as sysdba" << EOFshutdown immediate;exit;EOF

UNIX shell script to run SQL files as

sqlplus -s scott/tiger << EOF@emp.sql@dept.sqlexit;EOF

To pass unix variable from shell to Oracle database.

$ cat sqlconnect.sh#!/bin/bash

echo -n "Enter empno number : "read VALEMP

if [ -z "${VALEMP}" ]thenecho "You shoul enter empno."exit 1fi

ORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQLset head offselect empno||':'||ename from emp where empno=${VALEMP};exit;EOFSQL`

echo emp no : `echo $ORACLE_VALUE | cut -d ":" -f1`echo emp name : `echo $ORACLE_VALUE | cut -d ":" -f2`$ ./sqlconnect.shEnter empno number : 7839emp no : 7839emp name : KING$

To Call Shell script from SQL use HOST keyword

HOST /home/scripter/scripts/myshell.sh

Please give your valuable doubts or feedback via comments

Scripter said...

To pass unix variable from shell to Oracle database.

$ cat sqlconnect.sh#!/bin/bash

echo -n "Enter empno number : "read VALEMP

if [ -z "${VALEMP}" ]thenecho "You shoul enter empno."exit 1fi

ORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQLset head offselect empno||':'||ename from emp where empno=${VALEMP};EOFSQL`

echo emp no : `echo $ORACLE_VALUE | cut -d ":" -f1`echo emp name : `echo $ORACLE_VALUE | cut -d ":" -f2`$ ./sqlconnect.shEnter empno number : 7839emp no : 7839emp name : KING$