spag4j - Stored Procedure Generator for Java:
============================================= 
spag4j is a code generator written in Java to produce Java code for accessing a stored procedure layer
from a database. Currently it supports stored procedures written in an Oracle. It produces either an 
EJB3 Java layer or simple static access methods.

The Problem:
============ 
There are several OR Mapping Tools for simply accessing tables and views in the open source world. For
accessing an stored procedure layer all this tools are not enough. It's time to access stored procedures 
from Java without having to code them every time from scratch. 

Because of the specific dialects from one database vendor to another it really hard to generate access 
methods for them. spag4j currently focuses on the special needs to produce access code for Oralce PL/SQL 
Stored Procedures. It also focuses on generating Java POJOs and PL/SQL Wrapper procedures from PL/SQL 
Records which can not be accessed via the Java JDBC Bridge (see Oracle in deep).

Oracle in deep (http://download.oracle.com/docs/cd/B10500_01/java.920/a96654/ref.htm#1007714):
============================================================================================== 
It is not possible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, 
BOOLEAN, or table with non-scalar element types. As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table 
types, create wrapper procedures that handle the data as types supported by JDBC.
--> Therefore I started to write spag4j

Additional Features:
=================== 
Has a dictonary functionality for getting beautiful Java class, mehtod and variablen names
Uses open architecture technology (http://www.openarchitectureware.org/)

How does it work:
================= 
1. spag4j analysis the datastructur of the underlaying oracle database schema 
2. spag4j generates a metamodel of the structure 
3. spag4j uses the metamodel information to generate java and oracle access code 
4. spag4j produces an EJB3 for an oracle stored procedure package 
5. spag4j produces all needed POJOs
6. After all these steps you can access every PL/SQL Procudure from Java

Sample:

Given PL/SQL Package:
	CREATE OR REPLACE PACKAGE SAMPLE AS
	   TYPE Person IS RECORD (
	   	ID NUMBER,
	   	NAME VARCHAR(250),
	   	DATEOFBIRTH TIMESTAMP
	   	);
	   	
	   TYPE ArrayOfPerson IS TABLE OF Person INDEX BY BINARY_INTEGER;
	   
	   FUNCTION  FindAllPersons (
	   	AllPersons OUT ArrayOfPerson) RETURN NUMBER;
	   	
	   FUNCTION  StorePerson (
	   	PersonToStore IN Person) RETURN NUMBER;
	   	
	   FUNCTION  GetTimestamp  RETURN DATE;
	   	
	END SAMPLE;
	
	CREATE OR REPLACE PACKAGE BODY SAMPLE AS
	
	   FUNCTION  FindAllPersons (
	   	AllPersons OUT ArrayOfPerson) RETURN NUMBER IS
	   	RetVal             NUMBER;
	   BEGIN
	     -- do something
	   	 RetVal := 0;
	   END;
	   
	   FUNCTION  StorePerson (
	   	PersonToStore IN Person) RETURN NUMBER IS
	   	RetVal             NUMBER;
	   BEGIN
	    -- do something
	   	RetVal := 0;
	   END;
	   	
	
	   FUNCTION GetTimestamp RETURN DATE IS
	   BEGIN
	      RETURN SYSDATE;
	   END GetTimestamp;
	
	END SAMPLE;
Following Java code will be created (I only publish snippets):
public Double callFindallpersons(ObjectHolder allPersons)
        throws SAMPLEException {
        Connection con = null;
        CallableStatement stmt = null;

        try {
            con = getConnection();

            stmt = con.prepareCall(
                    "begin ? := SAMPLE_LAYER0.Findallpersons(?); end;");

            stmt.registerOutParameter(1, java.sql.Types.NUMERIC);
            stmt.registerOutParameter(2, java.sql.Types.ARRAY,
                "SAMPLEAROFPERSON");

            stmt.executeUpdate();

            ObjectHolder returnValue = new ObjectHolder();

            returnValue.setObject(stmt.getDouble(1));
            Array outArray2 = stmt.getArray(2);
            Object[] outArrayObj2 = (Object[]) outArray2.getArray();
            ArrayOfPerson outObj2 = convertToArrayOfPerson(outArrayObj2);
            allPersons.setObject(outObj2);

            stmt.close();

            return returnValue.getObject();
        } catch (Exception e) {
            throw new SAMPLEException(e.getMessage(), e);
        }
}
public java.util.Date callGettimestamp() throws SAMPLEException {
        Connection con = null;
        CallableStatement stmt = null;

        try {
            con = getConnection();

            stmt = con.prepareCall(
                    "begin ? := SAMPLE_LAYER0.Gettimestamp(); end;");

            stmt.registerOutParameter(1, java.sql.Types.TIMESTAMP);

            stmt.executeUpdate();

            ObjectHolder returnValue = new ObjectHolder();

            returnValue.setObject(JdbcTypeConverter.convertFromDate(
                    stmt.getTimestamp(1)));

            stmt.close();

            return returnValue.getObject();
        } catch (Exception e) {
            throw new SAMPLEException(e.getMessage(), e);
        }
}    
public Double callStoreperson(Person personToStore)
        throws SAMPLEException {
        Connection con = null;
        CallableStatement stmt = null;

        try {
            con = getConnection();

            stmt = con.prepareCall(
                    "begin ? := SAMPLE_LAYER0.Storeperson(?); end;");

            stmt.registerOutParameter(1, java.sql.Types.NUMERIC);
            // setInputVariable PlSqlRecordType
            Struct inStruct2 = convertFromPerson(personToStore, con);
            stmt.setObject(2, inStruct2);

            stmt.executeUpdate();

            ObjectHolder returnValue = new ObjectHolder();

            returnValue.setObject(stmt.getDouble(1));

            stmt.close();

            return returnValue.getObject();
        } catch (Exception e) {
            throw new SAMPLEException(e.getMessage(), e);
        }
}
private ArrayOfPerson convertToArrayOfPerson(Object[] rawData)
    throws SQLException {
    ArrayOfPerson collection = new ArrayOfPersonImpl();

    for (int i = 0; i < rawData.length; i++) {
        Struct struct = (Struct) rawData[i];
        Person element = convertToPerson(struct);
        collection.add(element);
    }

    return collection;
}

private Person convertToPerson(Struct struct) throws SQLException {
    Object[] rawData = struct.getAttributes();
    Person obj = new PersonImpl(JdbcTypeConverter.convertToDouble(
                rawData[0]), (String) rawData[1],
            JdbcTypeConverter.convertToDate(rawData[2]));

    return obj;
}

private Object[] convertFromArrayOfPerson(ArrayOfPerson coll, Connection con)
    throws SQLException {
    Object[] result = new Object[coll.size()];
    int i = 0;

    for (Person obj : coll) {
        result[i++] = convertFromPerson(obj, con);
    }

    return result;
}

private Struct convertFromPerson(Person obj, Connection con)
    throws SQLException {
    Object[] objArray = new Object[3];
    objArray[0] = JdbcTypeConverter.convertFromDouble(obj.getId());
    objArray[1] = obj.getFullName();
    objArray[2] = JdbcTypeConverter.convertFromDate(obj.getDateofbirth());

    Struct struct = jdbcHandler.createStruct(objArray, "SAMPLEPERSON", con);

    return struct;
}
Following PL/SQL Wrapper code will be created:
CREATE TYPE SamplePerson AS OBJECT (Idd NUMBER,FullName VARCHAR2(250),Dateofbirth TIMESTAMP)
/

CREATE TYPE SampleArOfPerson AS VARRAY(2147483647) OF SamplePerson
/
	
CREATE OR REPLACE PACKAGE SAMPLE_LAYER0
AS

	FUNCTION Findallpersons (AllPersons OUT SampleArOfPerson)
	RETURN NUMBER;
		
	FUNCTION Gettimestamp
	RETURN DATE;
		
	FUNCTION Storeperson (PersonToStore IN SamplePerson)
	RETURN NUMBER;

	FUNCTION CnvPersonRec (rec IN SAMPLE.PERSON) 
	RETURN SamplePerson;

	FUNCTION CnvArOfPersonTbl (tbl IN SAMPLE.ARRAYOFPERSON)
	RETURN SampleArOfPerson;

	FUNCTION CnvPersonObj (obj IN SamplePerson) 
	RETURN SAMPLE.PERSON;

	FUNCTION CnvArOfPersonCol (col IN SampleArOfPerson)
	RETURN SAMPLE.ARRAYOFPERSON;

END SAMPLE_LAYER0;
/

CREATE OR REPLACE PACKAGE BODY SAMPLE_LAYER0
AS
	FUNCTION Findallpersons (AllPersons OUT SampleArOfPerson)
	RETURN NUMBER
	IS
		-- OUT params
		tmpAllPersons SAMPLE.ARRAYOFPERSON;
		-- return value
		tmpRetVal NUMBER;
	BEGIN
		-- call wrapped function
		tmpRetVal := SAMPLE.FINDALLPERSONS(tmpAllPersons);
		-- convert OUT params
		AllPersons := CnvArOfPersonTbl(tmpAllPersons);
		RETURN tmpRetVal;
	END;
		
	FUNCTION Gettimestamp
	RETURN DATE
	IS
		-- return value
		tmpRetVal DATE;
	BEGIN
		-- call wrapped function
		tmpRetVal := SAMPLE.GETTIMESTAMP();
		RETURN tmpRetVal;
	END;
		
	FUNCTION Storeperson (PersonToStore IN SamplePerson)
	RETURN NUMBER

	IS
		-- IN params
		tmpPersonToStore SAMPLE.PERSON;
		-- return value
		tmpRetVal NUMBER;
	BEGIN
		-- convert IN params
		tmpPersonToStore := CnvPersonObj(PersonToStore);
		-- call wrapped function
		tmpRetVal := SAMPLE.STOREPERSON(tmpPersonToStore);
		RETURN tmpRetVal;
	END;

	FUNCTION CnvPersonRec (rec IN SAMPLE.PERSON) 
	RETURN SamplePerson IS
		result SamplePerson;
	BEGIN
		result := SamplePerson(
rec.ID,rec.NAME,rec.DATEOFBIRTH);
		RETURN result;
	END;

	FUNCTION CnvArOfPersonTbl (tbl IN SAMPLE.ARRAYOFPERSON)
	RETURN SampleArOfPerson IS
		result SampleArOfPerson;
		rec SAMPLE.PERSON;
		NumOfEntries NUMBER;
	BEGIN
		result := SampleArOfPerson();
		NumOfEntries := 1;
		IF tbl.Count > 0 THEN
			FOR Entry IN tbl.FIRST ..tbl.LAST LOOP
				rec := tbl(Entry);
				result.extend;
				result(NumOfEntries) := CnvPersonRec(rec);
				NumOfEntries := NumOfEntries + 1;
			END LOOP;
		END IF;
		RETURN result;
	END;


	FUNCTION CnvPersonObj (obj IN SamplePerson) 
	RETURN SAMPLE.PERSON IS
		result SAMPLE.PERSON;
	BEGIN
		result.ID := obj.Idd;
		result.NAME := obj.FullName;
		result.DATEOFBIRTH := obj.Dateofbirth;
		RETURN result;
	END;

	FUNCTION CnvArOfPersonCol (col IN SampleArOfPerson)
	RETURN SAMPLE.ARRAYOFPERSON IS
		result SAMPLE.ARRAYOFPERSON;
		rec SamplePerson;
		NumOfEntries NUMBER;
	BEGIN
		NumOfEntries := 0;
		IF col.Count > 0 THEN
			FOR Entry IN col.FIRST ..col.LAST LOOP
				rec := col(Entry);
				result(NumOfEntries) := CnvPersonObj(rec);
				NumOfEntries := NumOfEntries + 1;
			END LOOP;
		END IF;
		RETURN result;
	END;

END SAMPLE_LAYER0;
/