Oracle stored procedure support?

[nilesh]

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

[trisberg]

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.

[nilesh]

Ok, thanks, I will look at the unit tests.

I've got something working for passing in Oracle objects using oracle.sql.StructDescriptor and oracle.sql.STRUCT, and setting the value in the HashMap to the STRUCT object. I guess it is pretty close to how we would normally do it without Spring's StoredProcedure class.

[irbouho]

Just wandering... would it be hard to use wrapper stored procedures (stored procedures that accept simple [NUMBER, VARCHAR2, ...] parameters and instantiate the User Defined Type, populate it with the received data, then call the indeed stored procedure and pass in the populated the UDT)? This way, you can still use current SpringFramework JDBC abstraction layer and perhaps upgrade your code when the indeed functionnality is ready for production use.

The useness of such a work-around depends on the number of stored procedures with UDTs you have to use. I also hope you do not have stored procedures that return UDT!!!

[nilesh]

I can't figure out how to do IN OUT parameters, or even OUT parameters. For example if I have a function like this:

    FUNCTION get_operator(
        p_operator                IN OUT   t_operator,
        p_status                  OUT      t_status,
        p_login                   IN       INTEGER DEFAULT 0,
    )
        RETURN INTEGER IS

Where t_operator and t_status are Oracle objects like the one I described in the first post in this thread.

I can do the IN part of p_operator just fine, but don't know how to get the OUT value. For p_status, I do declareParameter(new SqlParameter(...) for it and that doesn't cause any errors, but if I set it to SqlOutParameter I get an SQLException, so I have not been able to get the OUT value for that either. The only thing in the returned Map is the return value of the function..

[nilesh]

irbouho - Wrapping stored procedures is a possiblity, though many of our UDT's are very large (and sometimes complex), so we want to avoid this. If we can't get the Spring abstraction to work, I think we might just use normal JDBC for the stored procedures that don't work with it. And just use DataSourceUtils for the connection lookup so that we can still do declarative transactions.

Unfortunately we do have a lot of UDT's that are OUT and IN OUT parameters... So hopefully that will work.

[irbouho]

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:

  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:

CREATE OR REPLACE PROCEDURE procSum (pDemo in out T_DEMO)
IS
BEGIN
 pDemo.c := pDemo.a + pDemo.b;
END;
/

3. java 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:

...
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.

[nilesh]

That example did help me get it working. Thanks a bunch!

[trisberg]

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.

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.