This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to execute PL/SQL program from Java? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to execute PL/SQL program from Java?" Watch "How to execute PL/SQL program from Java?" New topic
Author

How to execute PL/SQL program from Java?

D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

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?


Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
Create a stored procedure with the PL/SQL and use a JDBC CallableStatement.


James Carman, President<br />Carman Consulting, Inc.
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

It's what I'm trying to avoid.
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
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

Joined: Oct 11, 2000
Posts: 1121

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

Joined: Feb 07, 2004
Posts: 472

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

Joined: Oct 11, 2000
Posts: 1121

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

Joined: Feb 07, 2004
Posts: 472

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?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to execute PL/SQL program from Java?
 
Similar Threads
How to Read DBMS_OUTPUT from a stored procedure in a java program
Get ResultSet by executing anonymous pl/sql block.
write a PL-SQL code in java class
Unable to Send Java mail
Multiple repeat insert