Generic Oracle stored function class

[too_many_details]

Hi all,

I would really appreciate some feedback on something I have been working on recently. I may have completely wasted my time doing this so let me know if thats the case.

I work exclusively with Oracle (9i) and ALL db interaction is via stored procs (company rules).

I have used the Spring StoredProcedure class extensively to wrap these calls to the Oracle db.

I noticed after a while though a couple of things:

1. A separate (usually inner) class for each stored procedure was becoming tedious to write.
2. Alot of the calls dealt with scaler (ints and strings mostly) parameters, both IN and OUT.
3. The next common return type was a cursor.

So in the best tradition of TDD, I decided to refactor the code as much as possible and see where it led.

I came up with a generic Oracle Stored Function class. Here's some example of its usage:

    public int callFunctionTestCaseA() {
        // no input params, return type int
        OracleStoredFunction sp = new OracleStoredFunction(ds,
                "testpkg.testfunc_a", Types.INTEGER);

        return sp.runForInt();
    }

    public String callFunctionTestCaseB() {
        // no input params, return type String
        OracleStoredFunction sp = new OracleStoredFunction(ds,
                "testpkg.testfunc_b", Types.VARCHAR);

        return sp.runForString();
    }

    public int callFunctionTestCaseC(int i) {
        // one int input param, return int
        SqlParameter[] inparams = new SqlParameter[] {
                new SqlParameter("int_in", Types.INTEGER)
            };
        OracleStoredFunction sf = new OracleStoredFunction(ds,
                "testpkg.testfunc_c", inparams, Types.INTEGER);
        Map in = new HashMap(1);
        in.put("int_in", new Integer(i));

        return sf.runForInt(in);
    }

    public String callFunctionTestCaseD(int i, String string) {
        // input param (int, String), return type String
        SqlParameter[] inparams = new SqlParameter[] {
                new SqlParameter("int_in", Types.INTEGER),
                new SqlParameter("string_in", Types.VARCHAR)
            };

        OracleStoredFunction sf = new OracleStoredFunction(ds,
                "testpkg.testfunc_d", inparams, Types.VARCHAR);
        Map in = new HashMap(2);
        in.put("int_in", new Integer(i));
        in.put("string_in", string);

        return sf.runForString(in);
    }

    public List callFunctiontestCaseE() {
        // no input params, return type cursor
        OracleStoredFunction sf = new OracleStoredFunction(ds,
                "testpkg.testfunc_e", OracleTypes.CURSOR);

        return sf.runForList();
    }

    public List callFunctiontestCaseF(int i, String string) {
        // input params (int, String), return type cursor
        SqlParameter[] inparams = new SqlParameter[] {
                new SqlParameter("int_in", Types.INTEGER),
                new SqlParameter("string_in", Types.VARCHAR)
            };
        OracleStoredFunction sf = new OracleStoredFunction(ds,
                "testpkg.testfunc_f", inparams, OracleTypes.CURSOR);
        Map in = new HashMap(2);
        in.put("int_in", new Integer(i));
        in.put("string_in", string);

        return sf.runForList(in);
    }

As you can see it has a passing resemblance to the JdbcTemplate class!

Before I post the actual class itself, do you think i've reinvented the wheel? Is it useful?

Cheers

Rakesh

[Rod Johnson]

Interesting. I can see the motivation if you have many classes.

Did you consider the following method on JdbcTemplate:

 public Map call(CallableStatementCreator csc, final List declaredParameters)

[too_many_details]

Hi Rod,

i believe Thomas R mentioned that in another thread.

However, when I went looking for an example, I could not find one (i checked the pdf manual, the tests, this forum and all your books). I guess I lack the experience to work it out for myself so gave up!

If you have a simple example you could post I would be grateful to see how it fits in with my needs and report back.

Thanks

Rakesh