In my database interfacing library jOOQ, I would like to add support for Oracle (or DB2, etc) packages. I have already implemented stored procedure/function support where every stored object is modelled as a generated Java class. For example, this stored function
CREATE FUNCTION f_author_exists (author_name VARCHAR2) RETURNS NUMBER;
will generate a class that can be used like this (note, there are also lots of convenience methods, this example just shows the general design):
// A new "function call instance". The function needs to be instanciated
// once per call
FAuthorExists f = new FAuthorExists();
// Set the function parameters on the call instance and call it
f.setAuthorName("Paulo");
f.execute(connection);
// Fetch the result from the function call instance
BigDecimal result = f.getReturnValue();
The reason I chose a mapping SQL function -> Java Class is because stored procedures allow complex return values (several OUT, or IN OUT parameters) that I want to be able to fetch one by one after calling the procedure:
p.getOutParam1();
p.getOutParam2();
Now this design works fine with stored functions / procedures, where overloading is not possible. Within Oracle's (or DB2's) packages, however, I can have several functions with the same name, like
CREATE PACKAGE my_package IS
FUNCTION f_author_exists (name VARCHAR2) RETURNS NUMBER;
FUNCTION f_author_exists (name VARCHAR2, country VARCHAR2) RETURNS NUMBER;
END my_package;
When I generate a class per function (or procedure), I will have naming clashes with several FAuthorExists
Java classes. A lame solution is to add an index to the class name, such as FAuthorExists2
, FAuthorExists3
. Another lame solution is to generate some sort of hash value (or the value itself) from the parameter names/types directly into the classname, such as FAuthorExistsVARCHAR2
, FAuthorExistsVARCHAR2VARCHAR2
. Neither solution is desirable for obvious reasons.
Does anyone have a simple solution to this problem? Or maybe an Idea of a better overall design which would not produce such function name overloading issues?
Any feedback appreciated!