CodeSnip: How to Run Any Oracle Script File Through Shell Script in UNIX
page 4 of 5
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 68869/ 44


Listing 1

echo "Starting file execution"
sqlplus -s userid/password  << EOF >> /work/deepankar/out_put
echo "Completed execution"


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

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-04-15 6:58:59 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search