title: Oracle: Java stored functions: working with database
body: <p>I have a class, that works with database via JDBC. Connection to database establishes as usual:</p>
  
  <pre><code>    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);
      }
  </code></pre>
  
  <p>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 =)</p>
  
  <p><strong>UPD</strong>: i have maven project that compiles in jar. My class is:</p>
  
  <pre><code>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 &lt;= columnCount; i++){
                  sb.append(rs.getObject(i));
              }
          }
          conn.close();
          return sb.toString();  
      }
  }
  </code></pre>
  
  <p>Next, im using loadjava to upload that jar in DB:</p>
  
  <pre><code>loadjava -resolve -v -user user/password@localhost:1521/ae C:\Exchange\TestJavaToOracleProject.jar
  </code></pre>
  
  <p>Then I'm trying to create PL/SQL wrapper:</p>
  
  <pre><code>create or replace
  function getUserObjectInfoById(obj_id IN NUMBER) return VARCHAR2
  as language java 
  name 'testjavatooracleproject.App.selectJavaObjectById(long) return java.lang.String';
  </code></pre>
  
  <p>Then I'm trying to call my function:</p>
  
  <pre><code>select getUserObjectInfoById(400094) from dual;
  </code></pre>
  
  <p>but I'm getting error:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p><strong>UPD</strong>: problem is solved. Solution for me: usage following code for create connection:</p>
  
  <pre><code>OracleDriver ora = new OracleDriver();
  Conenction conn = ora.defaultConenction();
  </code></pre>