How to connect to an Oracle database hosted on a different server from ksh script

This can be done two ways, by providing tns string inside the script or by placing it into tnsnames.ora file:

FILE1=output.log
sql="INSERT INTO TABLE1(...)VALUES(...);"
sqlplus 'username/pwd@@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHostname)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myDB)))' << EOF > $FILE1
$sql
commit;
exit;
EOF

FILE1=output.log
sql="INSERT INTO TABLE1(...)VALUES(...);"
sqlplus 'username/pwd@DB_ALIAS' << EOF > $FILE1
$sql
commit;
exit;
EOF

How to store sqlplus output into a single or multiple variables via ksh script

db_status=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select status from v\$instance;
       exit
EOF`
echo $db_status

The above works for when you want to retrieve a single value. If you want to retrieve multiple values, use:

output=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select host_name, instance_name, status from v\\$instance;
       exit
EOF`

var1=`echo $output | awk '{print $1}'`
var2=`echo $output | awk '{print $2}'`
var3=`echo $output | awk '{print $3}'`
echo $var1
echo $var2
echo $var3

You can also put your sql statement into a file with .sql extension. Something like that:

set head off
set verify off
set feedback off
set pages 0

SELECT field1, field2, field3 
FROM Table1;

exit;

And then read the script values into variables:

sqlplus -s "/ as sysdba" @script2run.sql.sql | read var1 var2 var3

Korn Shell scripting intro

I recently started writing korn shell scripts here and there and below is some useful info on how to get started. I use vi editor for creating files:

vi myscript.ksh

Korn shell scripts usually have extension ksh The first line of the script should always be:

#!/usr/bin/ksh

After the file has been created, one needs to give it execute permissions so that the file becomes runable:

chmod 755 myscript.ksh

Now the script can be executed as follows:

./myscript.ksh

if the script name or path has spaces in it, it needs to be wrapped in double quotes

"C:\My Documents\My Scripts\myscript.ksh"

The escape character for ksh scripts is a (\) backslash. For example if you want to escape a dollar sign ($) inside your script, instead of writing

select * from v$database;

use

select * from v\$database;

(#) pound sign is used for comments and print and echo commands are used to output text to a sceen