Home » Developer & Programmer » JDeveloper, Java & XML » Stored procedures! Help!
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!
Previous Topic: ORACLE XMLDB question
Next Topic: Secure Thin JDBC
Goto Forum:
  


Current Time: Mon Jul 26 11:24:26 CDT 2021