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.