Oracle: Java stored functions: working with database

Question

I have a class, that works with database via JDBC. Connection to database establishes as usual:

    Connection conn = null;
    try {
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        conn = DriverManager.getConnection(DS_URL);
    } catch (SQLException ex) {
        logger.debug("Cannot register driver for Oracle DB", ex);
    }

Now I have to make Oracle stored function, that works with database. Is there any different ways to work with DB (e.g. establishing connection by using some context)? To me it seems to be little silly to establish connection by URL from within function, that is stored in the same DB =)

UPD: i have maven project that compiles in jar. My class is:

package testjavatooracleproject;

import java.sql.*;

public class App 
{
    public static void main(String[] args) throws SQLException{
        System.out.println(selectJavaObjectById(400094));
    }

    public static String selectJavaObjectById(long id) throws SQLException
    {        
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user/password@localhost:1521:ae");
        PreparedStatement ps = conn.prepareStatement("select * from user_objects where object_id = ?");
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        int columnCount = rs.getMetaData().getColumnCount();
        StringBuilder sb = new StringBuilder();
        if (rs.next()){
            for(int i = 1; i <= columnCount; i++){
                sb.append(rs.getObject(i));
            }
        }
        conn.close();
        return sb.toString();  
    }
}

Next, im using loadjava to upload that jar in DB:

loadjava -resolve -v -user user/password@localhost:1521/ae C:\Exchange\TestJavaToOracleProject.jar

Then I'm trying to create PL/SQL wrapper:

create or replace
function getUserObjectInfoById(obj_id IN NUMBER) return VARCHAR2
as language java 
name 'testjavatooracleproject.App.selectJavaObjectById(long) return java.lang.String';

Then I'm trying to call my function:

select getUserObjectInfoById(400094) from dual;

but I'm getting error:

ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.net.SocketPermission localhost:1521 connect,resolve) has not been granted to user. The PL/SQL to grant this is dbms_java.grant_permission( 'user', 'SYS:java.net.SocketPermission', 'localhost:1521', 'connect,resolve' )
29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
*Cause:    A Java exception or error was signaled and could not be
           resolved by the Java code.
*Action:   Modify Java code, if this behavior is not intended.

UPD: problem is solved. Solution for me: usage following code for create connection:

OracleDriver ora = new OracleDriver();
Conenction conn = ora.defaultConenction();

Answer 1

There are basically three ways to do the registration and loading of drivers

1. Class.forName(oracle.jdbc.driver.OracleDriver());
   Connection con = DriverManager.getConnection(DS_URL);

2.System.setProperty("jdbc.drivers","sun.jdbc.odbc.JdbcOdbcDriver");

3. as mention by you 

Answer 2

As far I remember the error you are getting is because you would not have given permission

exec dbms_java.grant_permission( 'user', 'SYS:java.net.SocketPermission',
'localhost:1521', 'connect,resolve' );