Skip to Main Content
  • Questions
  • Is it possible to reuse the same Connection (established from an external JDBC client) within a java stored procedure?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Osho.

Asked: January 18, 2017 - 4:37 pm UTC

Last updated: January 19, 2017 - 5:05 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Title: Is it possible to reuse the same Connection (established from an external JDBC client) within a java stored procedure?


My application design is as follows

Java client (using standard JDBC/Callable statement ) calls > Oracle Function , which in turn calls > Java stored Procedure


The code snippets are as below:


External Java client:

1) I am creating a connection object in java using the following code

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

Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test", "username","password");


2) Using this connection object I am calling a function inside oracle database using the following code

String sqlProcedure = "{? = call BASE_FUNCTION(?)}";

callableStatement = connection.prepareCall(sqlProcedure);

callableStatement.setString(2, javaKlass);

callableStatement.execute();

String response = callableStatement.getString(1);



Oracle Function:


1) BASE_FUNCTION does nothing but calls java stored procedure inside oracle database

create or replace FUNCTION BASE_FUNCTION (
KLASS_NAME VARCHAR2 
)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'com/plugin/base/BaseFunction.execute(java.lang.String) return oracle.sql.VARCHAR2';


Java Stored Procedure: This contains the business login for the CRUD operations.

1) Now I create new connection AGAIN in BaseFunction class to create and execute the necessary PreparedStatements for doing CRUD

 con = DriverManager.getConnection("jdbc:default:connection:");


I wanted to know if there is way to use the connection object made initailly inside Java Stored Procedure instead of creating a new connection object.

and Connor said...

Here's a simple example

SQL> create or replace and compile
  2   java source named "test"
  3   as
  4   import java.sql.*;
  5   import oracle.jdbc.driver.*;
  6
  7   public class test
  8   {
  9   public static int RowCount(String Table_name)
 10   throws SQLException
 11   {
 12   int rows = 0;
 13
 14   Connection cnx = new OracleDriver().defaultConnection();
 15   String sql = "SELECT COUNT(*) FROM "+Table_name;
 16   Statement stmt = cnx.createStatement();
 17   ResultSet rset = stmt.executeQuery(sql);
 18   while (rset.next()) {rows = rset.getInt(1);}
 19   rset.close();
 20   stmt.close();
 21   return rows;
 22   }
 23
 24   }
 25  /

Java created.

SQL> create or replace
  2   function j_count_table( p_table_name in varchar2 ) return number
  3   as
  4   language java
  5   name 'test.RowCount( java.lang.String ) return java.lang.Int';
  6  /

Function created.

SQL> select j_count_table('scott.emp') from dual;

J_COUNT_TABLE('SCOTT.EMP')
--------------------------
                        14

1 row selected.

SQL> delete from scott.emp where rownum = 1;

1 row deleted.

SQL> select j_count_table('scott.emp') from dual;

J_COUNT_TABLE('SCOTT.EMP')
--------------------------
                        13

1 row selected.

SQL> rollback;

Rollback complete.

SQL> select j_count_table('scott.emp') from dual;

J_COUNT_TABLE('SCOTT.EMP')
--------------------------
                        14

1 row selected.


Hope this helps

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library