fishScript.com Script con annotazioni in italiano e inglese

Shell scripting

Home >Shell scripting > Function connecting to oracle

The following is a base example of a shell script function that I use to connect to Oracle and execute sql statements.
Certainly there are various option to connect and get db results within a shell script, but I tend to adopt this combination of print [ sql code] | sqlplus -s [...] because it works fine on different Oracle db versions (8i, 10g).
This sample function checks if the schema owns/reads the object passed, if the object is not in all_objects you get the no rows selected. The some construct can be used to get results, execute a procedure, drop an object, etc. As showed in the second part, the function may be a good trick to verify database connection, in fact if the function does not return the table DUAL (or another table) accessible from a schema, it means there was not logon into the db.

#!/usr/bin/ksh
# file fun_chkobj
#tested on  HP-UX B.11.23 U ia64 
fun_chkobj() {
vdbschema=$1
vpass=$2
database=$3
obj=$4
voutput=`print "set heading off \n select table_name from all_tables where table_name ='$obj' ;"| sqlplus -s ${vdbschema}/$vpass@${database}`
echo $voutput
}


# calling the function
v=$(fun_chkobj "SALES" "PSWSALES" "CUSTOMER" "MYTABLE");

# printing function  result
echo $v

# executing the function using echo, this time there is not object
echo `fun_chkobj "ABPESDBO" "ABPESDBO123" "ACCAR2C1" "ANOTEXISTENTOBJECT"`



# implementing the function to verify connection parameters
chkConn=$(fun_chkobj "SYSTEM" "MANAGER" "DBCUSTOMER" "DUAL");
if [ "${chkConn}" != "DUAL" ]
then
echo " Cannot connect to db"
exit
fi

Tutorial

Executing a shell script [Unix]

Shell scripting passare parametri ad un programma [Shell scripting]

Cheking mandatory parameters have been passed to shell file, comparing strings in if construct and some other basics warming up with shell scripting [Shell scripting]

Estendere un logical volume e di un filesystem allocare più storage da riga di comando su Unix Hp [HP UX 11i]

Korn shell scripting If constructs AND, OR, &&, || syntax examples [Unix]

Function connecting to oracle getting results, executing query, verify db connection [Shell scripting]

Cheking parameters have been passed to shell file, comparing strings in if construct warming up with shell scripting [Shell scripting]

awk command line tutorial and examples sintax, functions, conditional constructs, text file operations [Unix]

How many processor and what kind of CPU extracting info from red hat system file /proc/cpuinfo [REDHAT]

sed command example on fixing errors and adding and removing blank lines [Unix]

Shell scripting eseguire controlli [Shell scripting]
Script

while do loop simple shell script [Shell scripting]

if e else strutture condizionali [Shell scripting]

Array valorizzazione e stampa [Shell scripting]

Hide user input on screen parameter of read command [Shell scripting]

Array in do while construct printing elements [Shell scripting]

Shell script automating oracle data pump schema export automatically export and compress database schemas in single separated dump files [Shell scripting]

Script per ricavare i nomi dei db (SID) dal file oratab [Unix]

Ciclo sul contenuto directory [Shell scripting]

If contruct check if a value is null or not [Unix]

Shell script extract items and looping example: issue dba statment for each db listed in oratab [Unix]

Passing parameters to shell script example sum of two numbers [Shell scripting]

Export and compress dump on the fly script to reduce storage need while using exp and imp Oracle utilities [Unix]

Pulire i file provenienti da ambieti dos/ windoes da caratteri sporchi dos2ux, dos2unix per eliminare i control M (^M) [Shell scripting]

Script for archiving and compressing folder and file utility example [Shell scripting]

How to get size or other file info in a shell script manipulating with cut or awk operative system commands output [Shell scripting]
Comandi

Privilegi su file o cartella (lettura, scrittura, execuzione) [Unix]

Cancellazione di una directory rm o rmdir [Unix]

Cd: cambiare directory [Unix]

Copiare un file in un altra cartella [Unix]

hostname [HP UX 11i]

uname visaulizza informazioni sul sistema operativo e la versione [HP UX 11i]

ipcs report stato dei processi [HP UX 11i]

getconf ricavare se si ha a disposizione un Kernel da 32-bit o 64-bit [HP UX 11i]

swlist -l patch determinare il software e le patch installate nella piattaforma [HP UX 11i]

who determinare gli utenti connessi [Unix]

shutdown [Unix]

env visualizzazione (e modifica) variabili dell'ambiente [Unix]

w attività degli utenti [Unix]

bdf visualizzare spazio disponibile [HP UX 11i]

df visualizzazione spazio disponibile [Unix]

Argomenti

Shell scripting

Cerca