The @formula annotation should warn to use the samples it gives as this makes your application database dependent.
The @formula annotation javadoc contains a sample containing a string function "substring" that does not work on oracle. Oracle supports "substr". The javadoc for @formula should contain a hint that these sql expressions always are database dependent and there is no way to configure a database dependent sql expression per database to use "substring" on mssql and "substr" on oracle. (right?)
oracle
In the meantime I found a way to solve the problem for substring function missing on Oracle by this approach. It saves this architecture for me.
I created a hbm file that contains this content:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="org.generic">
<database-object>
<create>
CREATE OR REPLACE FUNCTION substring (strIn clob, StartPos integer, Len integer) RETURN varchar2 IS
BEGIN
RETURN substr( strin, startpos, len );
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END substring;
</create>
<drop></drop>
<dialect-scope name="org.hibernate.dialect.Oracle10gDialect" />
<dialect-scope name="org.hibernate.dialect.Oracle12cDialect" />
</database-object>
</hibernate-mapping>
So at setup time a function only on oracle is created that maps to the substr function available on Oracle.It works fine so far. However for the sake of maintainability a @formula annotation that allows to specify the database dependend string would be much better. Currently you cannot see the dependency to the database specific function looking at the annotation code.
Imho a hint like this at the javadoc of the @formula annotation could help to make use of it.