• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PL/SQL query vs Stored Procs in JDBC

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
for insert and update why dont u use BATCH ???
first check whehter ur driver allows it or not
 
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic