This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills: The software developer's life manual and have John Sonmez on-line!
See this thread for details.
Win a copy of Soft Skills: The software developer's life manual this week in the Jobs Discussion forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to execute PL/SQL program from Java?

 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • 0
  • 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?
 
James Carman
Ranch Hand
Posts: 580
  • 0
  • 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
Linux Objective C Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's what I'm trying to avoid.
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • 0
  • 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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • 0
  • 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
Linux Objective C Ubuntu
  • 0
  • 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: 1141
1
Java Oracle
  • 0
  • 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
Linux Objective C Ubuntu
  • 0
  • 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?
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic