Running Oracle SqlPlus with Java in Kshells

Tags
shell scripts

 
Thread Tools Search this Thread
# 1  
Old 04-27-2009
Running Oracle SqlPlus with Java in Kshells

Hello,

This may not be the right place to ask for help for this problem but i might be because i'm using Java & SqlPlus in alot of Kshell scripts.

Just to give a high level picture, i'm basically using Java to control each SqlPlus execute command, by reading a flat file that contains the run values from previous steps and then in lets say the current step, check the values from previous steps to see for example if step3 = step1 + step2 ect.

The Java program will compare the previous step rules and then execute the SqlPlus, query to output from the SqlPlus and make a new Java entry.

But what is happening is in the Oracle Session Log it is creating and not deleting 10s upon 10s of seession entries and when i look at some of them they say they are Java executables.

So do you think this is a Java problem and if so is there a way to somehow delete the session immediately after the step runs so that the session thread disappears?

Thanks for any insights and even quesses at this point!

Fyi it's really only a problem now that system has been moved to a very heavily used instance and now everyone is seeing alot more sessions being created other than their own and not sure why the system is grinding to a halt.
# 2  
Old 04-27-2009
Quote:
Originally Posted by bobk544
...
But what is happening is in the Oracle Session Log it is creating and not deleting 10s upon 10s of seession entries and when i look at some of them they say they are Java executables.

So do you think this is a Java problem ...
Yes, it could be. You will have to ensure a couple of things in your java program:

(a) all transactions must be committed or rolled back, either in the sqlplus session or outside in the (invoker) java program. If your transactions are locking an database object, a good practice is to commit/roll back explicitly so that the lock is released.

(b) close all your ResultSet,
Code:
Statement/PreparedStatement/CallableStatement,

and Connection objects explicitly. If you have them in a "try" block, add a "finally" block and close all the objects by invoking their respective close() methods. If you do not do this, the session remains in Oracle and shows up in v$session view of Oracle. Too many of them, and your system comes to a grinding halt. Or you might even encounter the "too many open cursors" error.

Quote:
...
and if so is there a way to somehow delete the session immediately after the step runs so that the session thread disappears?
...
Well, the best thing you can do to make your java apps scalable is to follow (a) and (b) above. If your system has come to a halt and you want to know how to kill those sessions, then you'll have to ask your Oracle DBA to issue a command like the following:

Code:
ALTER SYSTEM KILL SESSION 'sid,serial#';

where "sid" and "serial#" are the columns of v$session view, corresponding to your java executable as the program.

There is another way of killing the relevant Oracle process from the OS itself, but it is not recommended as it could lead to database instance failure.

Quote:
...
Just to give a high level picture, i'm basically using Java to control each SqlPlus execute command, by reading a flat file that contains the run values from previous steps and then in lets say the current step, check the values from previous steps to see for example if step3 = step1 + step2 ect.

The Java program will compare the previous step rules and then execute the SqlPlus, query to output from the SqlPlus and make a new Java entry.
Not knowing your application, it is risky to suggest anything. But you do realize that you could call sqlplus from Korn shell itself and do all that processing, right ? Using java to invoke sqlplus just seems to be a convoluted way of doing things.

Hope that helps,
tyler_durden
# 3  
Old 04-28-2009
Thanks very much Tyler really appreciate your help, have a good rest of the week!

bk

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Fedora: Uninstall Java JDK 1.8 and install Oracle Java 1.7.15 TechGyaann Fedora 6 12-23-2017 12:59 AM
Please help: Oracle gqsql or sqlplus output format like mysql Linusolaradm1 UNIX and Linux Applications 6 03-11-2013 10:56 AM
Running sqlplus for 5 DB in a shell big123456 Shell Programming and Scripting 1 11-27-2012 07:59 AM
Connect to Oracle using sqlplus Subhasis Shell Programming and Scripting 1 09-18-2012 05:52 AM
Running SQLPLUS Script in CRONTAB santoshpayal Shell Programming and Scripting 11 06-21-2010 07:14 PM
redirecting oracle sqlplus select query into file bongo Shell Programming and Scripting 6 05-18-2010 10:21 AM
Connecting to Oracle DB using sqlplus shrutihardas Shell Programming and Scripting 26 05-06-2010 10:32 PM
Connecting to Oracle DB using sqlplus shrutihardas UNIX for Dummies Questions & Answers 1 05-04-2010 05:49 PM
Formatting Oracle sqlplus output Gopal_Engg Shell Programming and Scripting 6 03-11-2010 09:18 PM
How to access Oracle table using sqlplus AngelMady UNIX and Linux Applications 2 06-17-2008 04:22 PM
Executing SQLPLUS in UNIX Script from JAVA anooptech UNIX for Advanced & Expert Users 0 04-16-2008 09:53 AM
Problem while calling Oracle 10g SQLPLUS files ganapati UNIX for Advanced & Expert Users 2 05-01-2007 04:03 AM
running shell script from sqlplus dkr123 Shell Programming and Scripting 9 07-20-2006 11:52 PM
Running SQLPLUS from Shellscripting ... chittari Shell Programming and Scripting 2 06-12-2006 05:09 AM