aspose file tools*
The moose likes JDBC and the fly likes Java Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java Stored Procedure" Watch "Java Stored Procedure" New topic
Author

Java Stored Procedure

Pork C
Greenhorn

Joined: Jun 05, 2001
Posts: 1
Hi,

It will very helpful if someone could guide me with this problem.
I am trying to execute a java stored procedure in Oracle Lite
local database. For example...
1. After creating and loading the java class. I create the procedure as follows.
CREATE OR REPLACE PROCEDURE drop_it (
obj_type VARCHAR2,
obj_name VARCHAR2)
AS LANGUAGE JAVA
NAME 'GenericDrop.dropIt(java.sql.Connection,
java.lang.String, java.lang.String)';

2. When I finally execute the following SQL
SELECT drop_it('TABLE','testTable')
FROM dual
Expected result doesn't come only the following exception
Exception in thread "main" java.sql.SQLException: [POL-8035] no such attribute or method.
But in the case of java stored procedure without any SQL connection, I am able to excecute the it without any exception.

Thanks in Advance..
Pork.
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
"Pork C",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in with a new name which meets the requirements.
Thanks.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
MIchael Kmiec
Greenhorn

Joined: Jun 05, 2001
Posts: 9
It really depends on what your stored procedure is doing. Based on the name, it looks like your class is attempting some sort of DML - dropping a table would be my guess.
If memory serves me correctly - it's been a while since I've developed with Oracle - Oracle Java stored procedures limit what you can do with them. Primarily, they let you SELECT and then manipulate the returned data - I even think UPDATES are restricted. Really, it makes sense, since you don't want any old user coming along, running your stored procedure and subsequently hosing your database. Security issue or big bug? You be the judge.
This was a constant source of frustration until I found a reference to the limitation on http://technet.oracle.com (sorry I don't have the exact URL, as I said, it's been a while.) All the docs on Java stored procedures are at: http://technet.oracle.com/tech/java/jsp/. It also helps if you look up the error number [POL-8035] on technet.
Good luck,
/mike
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Java Stored Procedure