| Stored procedures! Help! [message #91993] |
Wed, 14 January 2004 23:27 |
Ong
Messages: 5
Registered: January 2004
|
Junior Member |
|
|
I have a problem here. I have written a class with two methods in it. Both method are basically the same, they all insert except that one receives a parameter and one doesn't.
The class compiled perfectly using jbuilder and was loaded into Oracle via loadjava with the -thin options.
Both of the method has been published as a procedure in oracle. When i try both of the procedures, only one is successful (the one without a parameter)
Is it because we cannot use JDBC in oracle stored procedures?
Here is the user and tables i have created:-
CREATE USER "TESTING" PROFILE "DEFAULT"
IDENTIFIED BY "testing" DEFAULT TABLESPACE "USERS"
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "TESTING" WITH ADMIN OPTION;
GRANT "DBA" TO "TESTING" WITH ADMIN OPTION;
GRANT "EXP_FULL_DATABASE" TO "TESTING" WITH ADMIN OPTION;
GRANT "IMP_FULL_DATABASE" TO "TESTING" WITH ADMIN OPTION;
GRANT "RESOURCE" TO "TESTING" WITH ADMIN OPTION;
CREATE TABLE TABLE1 ("ID" VARCHAR2(10) NOT NULL, "TYPE"
VARCHAR2(10) NOT NULL);
CREATE TABLE TABLE2 ("ID" VARCHAR2(10) NOT NULL, "TYPE"
VARCHAR2(10) NOT NULL);
This is my java code:-
public class testing {
public static void test(String para)
{
try{
java.lang.ClassLoader classLoader = java.lang.ClassLoader.getSystemClassLoader();
Class driverClass = classLoader.loadClass("oracle.jdbc.OracleDriver");
java.sql.Driver driver = (java.sql.Driver) driverClass.newInstance();
java.sql.DriverManager.registerDriver(driver);
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@server:1521:DB", "TESTING", "TESTING");
java.sql.Statement statement = connection.createStatement();
String sql = "";
sql = "INSERT INTO "TESTING"."TEST" ("ID" ,"TYPE") VALUES ('"+ para +"' , NULL )";
statement.execute(sql);
statement.close();
connection.commit();
connection.close();
}catch (Exception e){
e.getMessage();
}
}
public static void test1()
{
try{
java.lang.ClassLoader classLoader = java.lang.ClassLoader.getSystemClassLoader();
Class driverClass = classLoader.loadClass("oracle.jdbc.OracleDriver");
java.sql.Driver driver = (java.sql.Driver) driverClass.newInstance();
java.sql.DriverManager.registerDriver(driver);
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@server:1521:DB", "TESTING", "TESTING");
java.sql.Statement statement = connection.createStatement();
String sql = "";
sql = "INSERT INTO "TESTING"."TEST1" ("ID" ,"TYPE") VALUES ('HI' , NULL )";
statement.execute(sql);
statement.close();
connection.commit();
connection.close();
}catch (Exception e){
e.getMessage();
}
}
}
Here is how i publish it:-
create or replace procedure test(para char)as language java name 'testing.test(String)';
create or replace procedure test1 as language java name 'sdo_functions.test1()';
And here is how i called it via SQLPlus:-
call test1();
Call completed.
call test('HI');
ERROR at line 1:
ORA-29531: no method test in class testing
Could someone enlighten me on this... thanks!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
