wood burning stoves 2.0*
The moose likes JDBC and the fly likes PL/SQL query vs Stored Procs in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PL/SQL query vs Stored Procs in JDBC" Watch "PL/SQL query vs Stored Procs in JDBC" New topic
Author

PL/SQL query vs Stored Procs in JDBC

jean xi
Greenhorn

Joined: Jul 25, 2001
Posts: 10
I am working on a project with lots of data insert,update and select from or to Oracle 8i. I created a bunch of stored procs in Oracle package to handle these tasks and in Java code i use Callable statement to call these stored procs, of couse i have to set up all the parameters first. but my boss suggests to use strait PL/SQL in java code, pass to Oracle as a string through JDBC, so will gain the performance by without set up all the parameters. I'm not very sure about his suggestion, does that mean passing PL/SQL as a string to database is better than call stored procs?
Thanks a lot
Yogen Vadnere
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
for insert and update why dont u use BATCH ???
first check whehter ur driver allows it or not

Yogen Vadnere
SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
Your boss is recommending something that goes against the purpose of using stored procedures. Remember that sp's are precompiled and optimized by Oracle. They are ready to be executed yielding very fast performance. If you pass the sp as a string in a callable statement, or even just a statement, Oracle has to compile and optimize the sp before it will execute it, and Oracle will do this every single time you pass the sp string to Oracle. This will degrade the performance of database accesses considerably.
You never want to place any type of SQL or PL/SQL in your Java code. Think about it. Everytime you want to change your queries you will be forced to recompile your java source. I usually place SQL queries in text files using a naming system and extract the SQL from the file using a key to the query. If you need to pass arguments to your queries, then create a helper class that inserts the arguments into the SQL template string retrieved from the file containing your SQL strings.
just an idea, and it works great
saf
[This message has been edited by SAFROLE YUTANI (edited October 05, 2001).]
jean xi
Greenhorn

Joined: Jul 25, 2001
Posts: 10
Thanks Saf.
But they have decided to embed pl/sql string into java code, i have to change my code to use string replace calling stored procs. i feel not so happy, but have to do it.
Anyway, thanks a lot. i'll use your suggestion in my next project.
Take care
SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
Hey Jean, can you list the exact steps to use in an SQL+ window to create and grant "execute" for a stored procedure? I'm trying it right now but I'm having problems. I have a simple sp in a file, and at the SQL+ prompt I'm typing "@<path to file>". I'm not getting any errors, but when test to see if the sp was entered into Oracle, I'm getting errors indicating that the sp or object does not exist.
thanks,
saf
shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
Jean,
This may make you feel a little happy about using pl/sql instead of stored procedures everywhere.
At the end of this article is a paragraph about "Use stored procedures where appropriate".
http://www.as400.ibm.com/developer/java/topics/jdbctips.html
jean xi
Greenhorn

Joined: Jul 25, 2001
Posts: 10
Hi Saf,
you said your sp was in a file. you must put your sp in some package of your oracle database and compile it. then you can execute in your sql+.
Have a good weekend.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PL/SQL query vs Stored Procs in JDBC
 
Similar Threads
Problem using Callable Statement
batch update in SQL
getting ResultSet from Java Stored Procedure
Java Stored Procedure in SQL Server
Using JDBC for a remote call in Oracle