Oracle stored procedure support?
How well does Spring's stored procedure support Oracle stored procedures? Any limitations? Will I be able to pass in (or out) types defined using for example:
CREATE OR REPLACE TYPE t_mytype AS OBJECT(
avarchar1 VARCHAR2(15),
avarchar2 VARCHAR2(40),
);
Using Springframework 1.1 RC1...
If there are any good examples on calling stored procedures, specifically for Oracle, please point me to them. I looked at this one and it was useful:
http://www.springframework.org/docs/...toredProcedure
Take a look at the unit tests
The unit tests for the stored procedure support might be of some help. The tests are in org/springframework/jdbc/object/StroredProcedureTestSuite.java in the test directory. You should be able to use the SqlTypeValue / AbstractSqlTypeValue to create any kind of special parameter. If not, we would like to know so we can add support for it.
Example of Oracle stored procedure support
Hi again,
I build the following example to show how you can use Oracle stored procedure with IN OUT UDT's parameters using Spring Framework JDBC abstraction layer.
1. create the UDT:
Code:
CREATE OR REPLACE TYPE T_DEMO AS OBJECT(
a NUMBER,
b NUMBER,
c NUMBER);
2. create the stored procedure (yes I know, this is a very basic example :wink:):
Code:
CREATE OR REPLACE PROCEDURE procSum (pDemo in out T_DEMO)
IS
BEGIN
pDemo.c := pDemo.a + pDemo.b;
END;
/
3. java code:
Code:
import java.sql.Connection;
import java.util.Map;
import java.util.HashMap;
import org.apache.commons.dbcp.BasicDataSource;
import oracle.sql.StructDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.Datum;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor;
public class OracleJdbc
{
private void execute() throws Exception {
//some DI will be welcome here
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:orcl");
ds.setUsername("user_name");
ds.setPassword("user_password");
JdbcTemplate template = new JdbcTemplate(ds);
CommonsDbcpNativeJdbcExtractor nje = new CommonsDbcpNativeJdbcExtractor();
//get a native Oracle Connection
Connection con = nje.getNativeConnection (template.getDataSource ().getConnection ());
StructDescriptor sdesc = StructDescriptor.createDescriptor ("T_DEMO", con);
Object[] attributes = {new Integer(10),
new Integer(25),
new Integer(150)};
STRUCT param = new STRUCT (sdesc, con, attributes);
//check data before call
printStruct(param);
Map params = new HashMap();
params.put("pDemo", param);
SumStoredProcedure sproc = new SumStoredProcedure(template, "procSum");
Map results = sproc.execute(params);
STRUCT result = (STRUCT) results.get ("pDemo");
//check data after call
printStruct(result);
ds.close ();
}
private void printStruct(STRUCT struct) throws Exception {
Datum[] data = struct.getOracleAttributes ();
System.out.println ("pA = " + data[0].intValue ());
System.out.println ("pB = " + data[1].intValue ());
System.out.println ("pC = " + data[2].intValue ());
}
public static void main (String[] args) throws Exception {
new OracleJdbc().execute ();
}
private class SumStoredProcedure extends StoredProcedure {
public SumStoredProcedure(JdbcTemplate jdbcTemplate, String name) {
setJdbcTemplate(jdbcTemplate);
setSql(name);
declareParameter(new SqlOutParameter("pDemo", OracleTypes.STRUCT, "T_DEMO"));
compile();
}
}
}
Program output:
Quote:
...
INFO: Database Product Name is Oracle
Aug 12, 2004 6:31:20 PM org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
INFO: Driver Version is 9.2.0.3.0
pA = 10
pB = 25
pC = 150
Aug 12, 2004 6:31:20 PM org.springframework.jdbc.object.SqlCall compileInternal
INFO: Compiled stored procedure. Call string is [{call procSum(?)}]
Aug 12, 2004 6:31:20 PM org.springframework.jdbc.object.RdbmsOperation compile
INFO: RdbmsOperation with SQL [procSum] compiled
pA = 10
pB = 25
pC = 35
I hope this will help.
Example of Oracle stored procedure support
Good example, thanks for posting it. I have one suggestion and that is to use an AbstractSqlTypeValue for the in parameter - I have modified the example to show how this would be used.
There is one method to implement and that is createTypeValue. This method gets the connection passed in so there is no need to extract it from the JdbcTemplate. If you provide a NativeJdbcExtractor on the template then that will be applied automatically as well.
Code:
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import java.util.HashMap;
import org.apache.commons.dbcp.BasicDataSource;
import oracle.sql.StructDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.Datum;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor;
public class OracleJdbc
{
private void execute() throws Exception {
//some DI will be welcome here
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@fiji:1521:my10g");
ds.setUsername("spring");
ds.setPassword("passwd");
JdbcTemplate template = new JdbcTemplate(ds);
CommonsDbcpNativeJdbcExtractor nje = new CommonsDbcpNativeJdbcExtractor();
template.setNativeJdbcExtractor(nje);
SqlTypeValue param = new AbstractSqlTypeValue() {
protected Object createTypeValue(Connection con, int sqlType,
String typeName) throws SQLException {
StructDescriptor sdesc = StructDescriptor.createDescriptor ("T_DEMO", con);
Object[] attributes = {new Integer(10),
new Integer(25),
new Integer(150)};
STRUCT p1 = new STRUCT(sdesc, con, attributes);
OracleJdbc.printStruct(p1);
return p1;
}
};
Map params = new HashMap();
params.put("pDemo", param);
SumStoredProcedure sproc = new SumStoredProcedure(template, "procSum");
Map results = sproc.execute(params);
STRUCT result = (STRUCT) results.get("pDemo");
//check data after call
printStruct(result);
ds.close ();
}
public static void printStruct(STRUCT struct) throws SQLException {
Datum[] data = struct.getOracleAttributes ();
System.out.println ("pA = " + data[0].intValue ());
System.out.println ("pB = " + data[1].intValue ());
System.out.println ("pC = " + data[2].intValue ());
}
public static void main (String[] args) throws Exception {
new OracleJdbc().execute ();
}
private class SumStoredProcedure extends StoredProcedure {
public SumStoredProcedure(JdbcTemplate jdbcTemplate, String name) {
setJdbcTemplate(jdbcTemplate);
setSql(name);
declareParameter(new SqlOutParameter("pDemo", OracleTypes.STRUCT, "T_DEMO"));
compile();
}
}
}
Now don't try this yet. I found a bug in the CallableStatementCreatorFactory - we only take advantage of the SqlTypeValue support for IN parameters. Since the parameter in the example was an IN OUT parameter it was not applied and the call failed. I have fixed this problem and I will commit this fix as soon as I have time to do some more testing. This should make the upcoming 1.1 release.