CodeSnip: How to Run Any Oracle Script File Through Shell Script in UNIX
 
Published: 19 Mar 2008
Abstract
In this article, Deepankar demonstrates the usage of Shell scripts in UNIX and their ability to run any Oracle script file. After reading this code snippet, you will be able to execute a number of oracle queries at one go without any need to wait for the end of one query to run another. This technique will be helpful for bulk insertion of records into a table.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 70460/ 119

Introduction

By using the above mentioned method, the user can run a useful oracle script through UNIX shell script. The insert script can contain millions of DML operations which can be executed at one go. Also, the prompt output can be redirected into a UNIX file for tracking any possible error in any particular DML operation.

Requirements

Oracle database configured in UNIX OS

Shell Scripts in UNIX

The shell scripts are self executable files. That simply means that all the lines, which are written inside the script, get executed one by one like any other command in UNIX. So the simplest way to execute millions of DML operations at one go is to connect to the oracle database and pass the commands written in the file to the database for execution.

Code

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

Conclusion

By following the method mentioned above, one can make any oracle script file run from oracle through shell script. The shell programming mentioned above is a simple one, but it can be extended to handle complicated business requirements. Also, any oracle process that needs to be run frequently can be invoked through a shell script, which in turn can be configured to run at given time intervals. If the server is a remote one then there may be a possibility that the running oracle process may get interrupted for say network issues. To avoid the issue, the shell script may be run in nohup mode allowing the process to run in the background from server side until the end without getting affected for different network issues.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





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


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-13 7:14:19 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search