Forum Stats

  • 3,751,256 Users
  • 2,250,338 Discussions
  • 7,867,361 Comments

Discussions

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

Rado_mir
Rado_mir Member Posts: 7
edited Jun 3, 2013 6:50AM in SQL & PL/SQL
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.
Tagged:

Answers

  • 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
    Rado_mir Member Posts: 7
    edited Jun 3, 2013 2:56AM
    Thanks for advice.

    Edited by: Rado_mir on 2013-06-02 23:56
  • Rado_mir
    Rado_mir Member Posts: 7
    edited Jun 3, 2013 5:46AM
    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
    tsangsir Member Posts: 409 Silver Badge
    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
    tsangsir
This discussion has been closed.