• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

How to execute PL/SQL program from Java?

 
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I do want to execute some PL/SQL programs from Java web application, but I do not want to use TOAD. If I provide program in executeUpdate then I get strange syntax errors reported by Oracle. Should I try executeBatch() instead of?
 
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Create a stored procedure with the PL/SQL and use a JDBC CallableStatement.
 
D Rog
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's what I'm trying to avoid.
 
drifter
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried using a CallableStatement with your PL/SQL?

CallableStatement cstmt = connection.prepareCall(PLSQLString);
// call set methods if needed
cstmt.execute();

We've used it in a couple of cases. Not sure what you've got against TOAD.

Tell the Details. Maybe your PL/SQL just isn't working yet.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
D,
You said:


If I provide program in executeUpdate then I get strange syntax errors reported by Oracle.


Perhaps you would care to post your code, along with the entire error message (and stack trace) you are getting? Then someone may be able to help you further.

Good Luck,
Avi.
 
D Rog
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nothing against toad. I do not want to get it integrated with Java code. Is it Java based BTW?
Here is proc:
------------------------------------------------
declare
x_num := 0;
begin
loop
insert into x_table values(x_num);
x_num := x_num + 1;

if x_num >= 20000
then exit;
end if;
end loop;
end;
/
---------------------------------------------------
Here is Java code:
....
String query = getStringParameterValue("query", null, 0);

try {
stmt = connection.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
if (getIntParameterValue("update_type", 0, 0) == 1) {
result.put("count", new Integer(stmt.executeUpdate(query)));
return result;
}
rs = stmt.executeQuery(query);
....
There is some flag telling should or not a query return result set. Probably I need to add one more flag for callable queries.
 
Avi Abrami
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
D,
In my opinion, you haven't supplied enough information in order for me to give you a definitive answer, but here's a suggestion. (Note that I'm assuming that "X_TABLE" is a database table -- and not a PL/SQL table.)

Or have I missed something?

Good Luck,
Avi.
[ May 11, 2005: Message edited by: Avi Abrami ]
 
D Rog
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, it looks like a work around for the particular example, however a real PL/SQL code can be less trivial to wrap it just in an insert query, although who knows?
 
eat bricks! HA! And here's another one! And a tiny ad!
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic