PL/SQL - calling java class in OracleDB and establish connection to that DB

Rado_mir

Hello Oracle Users. I have a question. I`m trying to deploy java class into Oracle db. These class functionality is to establish connect to the same Oracle db. I know that is wrong but i really need to do that. Is that possible?? Maybe i will show some example and what i have done.

Java Class

public class test {

public static String say(String arg)
{
Connection connection = null;
try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

System.out.println("error with driver");
e.printStackTrace();

}

try {

connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:OracleDB", "system",
"adminroot");
Statement stmt = connection.createStatement();
ResultSet rset = stmt.executeQuery("Create table test(a number)");

} catch (SQLException e) {

System.out.println("connection error");
e.printStackTrace();


}
return arg;

}
}
That test class i have compiled, tested and deployed on my Oracle DB with loadjava command.

Then i wrote a PL/SQL function to call java test class.

create or replace function trythis
(id VARCHAR2)
return VARCHAR2
as language java
name 'project1.test.say(java.lang.String)
return java.lang.String';

Then i get some GRANT errors ( i have handle with this )

When i`m trying to call --> Select trythis('test') from dual; function is returning text but i can`t find new table test. Could someone help me with that?? I was thinking problem is with ojdbc drivers and i have used loadjava to deploy that drivers into oracle but that won`t help.

Best regards
Radomir

PS. Sorry for my English.

Unknown

The approach to perform DDL in a function calling Java is just completely WRONG

Databases are designed, they are not created on the fly, and they are not a garbage bin.

Apart from that, using this 100 percent wrong approach, the caller never sees the object you created, by design, as the create statemet is an implicit autonomous transaction.
In your case the statement is not even executed in your session, as you connect to the database again.

Learn to design databases. Or stay away of them.

--------------
Sybrand Bakker
Senior Oracle DBA

Rado_mir

Thanks for advice.

Edited by: Rado_mir on 2013-06-02 23:56

Rado_mir

New idea. DDL isn`t working but if i would make some DQL ? Like select??
I`m trying to deploy java class like this:

public class test {


public static String say()



throws SQLException{
Connection conn = new OracleDriver().defaultConnection();

String sql = "Select names from pdb_proteins where numbers=61";

try {

// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.OracleDriver") ;
System.out.println("Oracle JDBC driver loaded ok.");

} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
}

try {
ResultSet rset = null;
PreparedStatement pstmt = conn.prepareStatement(sql);
rset=pstmt.executeQuery(sql);
String wynik = null;
wynik = rset.getString(1);
return wynik;
} catch (SQLException e) {

System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return "nope";

}
}
}
Then invoke with PL/SQL function

and i`m getting error ORA-29534: object SYSTEM.oracle/jdbc/OracleDriver

Could someone help me with this? How i could register jdbc driver inside Oracle db??

Edited by: Rado_mir on 2013-06-03 02:42

tsangsir

I guess you should use the Server-Side Internal Driver to connect the same DB

http://docs.oracle.com/cd/E11882_01/java.112/e16548/ssid.htm