Listing 1
#!/usr/bin/ksh
echo "Starting file execution"
date
sqlplus -s userid/password << EOF >> /work/deepankar/out_put
@insert.sql
EOF
echo "Completed execution"
date
Explanation
The first line is a comment line, which is UNIX kernel specific.
In the following approach the available shell is KORN shell. To the shell this
line is just a comment. But if the kernel tries to execute a file with this
line, it will exec the specified interpreter and pass the script to it. It also
tells anyone who looks at the code that this scripting language is KORN shell
specific and might not work in other shell if attempted.
The "echo" and date commands are normal UNIX
commands. Echo used to throw anything to the prompt written after it and date
shows the current date.
In this case, on executing the script, it will throw the
following.
Figure 1
"sqlplus -s userid/password" is responsible for
database connection. In order to read from the Oracle script, the DML script
file (in our case) containing the DML's has to be called as @<file_name>.
The oracle file to be called if placed in any directory other than the shell
script has to be specified with the path name.
EOF indicates that after connecting to the database each
line will be redirected to oracle through << until the EOF is
encountered. Further, if we want to track any syntactical error in oracle query
within the DML script, we can redirect the query output to a file named out_put
(in our case) in the path given using >> redirection.
Examination of the shell script using a simple query
Let us say our oracle script file called insert.sql contains
the following query.
Figure 2
And let us say the shell script named test.ksh has the body
as described below.
Figure 3
On execution, the shell script will do the following.
1. Connect to the database.
2. Create a file named out_put in the path specified.
3. Run the oracle script.
4. Write the query output in the output file.
For the above case the out_put file should show the current
date as shown below.
Figure 4
