title: Mapping between Oracle Packages and Java Packages
body: <p>In my database interfacing library <a href="http://jooq.sourceforge.net" rel="nofollow">jOOQ</a>, 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</p>
  
  <pre><code>CREATE FUNCTION f_author_exists (author_name VARCHAR2) RETURNS NUMBER;
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>// 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();
  </code></pre>
  
  <p>The reason I chose a mapping <strong>SQL function</strong> -> <strong>Java Class</strong> 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>
  
  <pre><code>p.getOutParam1();
  p.getOutParam2();
  </code></pre>
  
  <p>Now this design works fine with stored functions / procedures, where <strong>overloading</strong> is not possible. Within Oracle's (or DB2's) packages, however, I can have several functions with the same name, like</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>When I generate a class per function (or procedure), I will have naming clashes with several <code>FAuthorExists</code> Java classes. A lame solution is to add an index to the class name, such as <code>FAuthorExists2</code>, <code>FAuthorExists3</code>. 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 <code>FAuthorExistsVARCHAR2</code>, <code>FAuthorExistsVARCHAR2VARCHAR2</code>. Neither solution is desirable for obvious reasons. </p>
  
  <p>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?</p>
  
  <p>Any feedback appreciated!</p>