How to implement Alpha Numeric Sequence (Increment AAAAZ to AAAB0)?

I faced the issue of generating the sequence of alphanumeric characters in pl/sql.PL/SQL supports the number sequences but not alpha numeric sequences.So decided to write one algorithm to perform the sequence generation


CREATE OR REPLACE FUNCTION batchseq_next(batchseq VARCHAR2) RETURN VARCHAR2 IS
str varchar2(5) :=batchseq;
inc char(1);
i number(2) :=5;
BEGIN
IF(SUBSTR(str, i, 1) = '9') THEN
str := SUBSTR(str, 1, i -1) || 'A';
ELSIF(SUBSTR(str, i, 1) != 'Z') THEN
str := SUBSTR(str, 1, i -1) || CHR((ASCII(SUBSTR(str, i, 1)) + 1));
else
if(SUBSTR(str, instr(str,'Z',1,1)-1, 1) ='9') then
inc := 'A';
else
inc := CHR((ASCII(SUBSTR(str, instr(str,'Z',1,1)-1, 1)) + 1));
end if;
str := SUBSTR(str, 1, instr(str,'Z',1,1)-2) ||inc|| translate (SUBSTR(str, instr(str,'Z',1,1), i),'Z','0');
END IF;
RETURN str;
END batchseq_next;

Some learnings in Hibernate:

1)Any number of associations can be joined using HQL Join.If we are using HQL Join Fetch and didn't specify second association in the HQL separate select statemnets will be fired for fetching the second association. (i.e) implicit association fetch depth is1
eg: Assume the relationships A-->B--> C .
The query "from A INNER JOIN FETCH B where ...." will eagerly fetch B not C. Separate select statements will be fired

2) While writing native SQL query try to use addEntity() to do value object conversion. We can fetch and convert the associations as well .
Eg:
Consider the one-many relation between parent and Child table

session.createSQLQuery("select pid,name,cid,c_pid,marks from PARENT p,CHILD c where p.pid=c.c_pid").addEntity("parent",Parent.class).addJoin("parent", "parent.childs").list(); -- won't eagerly fetch the associated children (Don't know the reason)

But the eager join fetching from child--->Parent is working fine .
session.createSQLQuery("select pid,name,cid,c_pid,marks from PARENT p,CHILD c where p.pid=c.c_pid").addEntity("child",Child.class).addJoin("child", "child.parent").list();

3)Sometimes it is not feasible to use HQL and implicit ValueObject coversion while using the aggregate functions.
I have examined two options to do implicit conversion instead of iterating over the result set.
I) Write native SQL and use addEntity() to perform the implicit conversion.This is not the good approach since the SQL syntax may change vendor to vendor
Ex:
session.createSQlQuery("select * from tbl where .... ").addEntity(entity.class)

II)Create the ValueObject with the required aggregate fields and then use Transformer to convert the ResultSet object to the DTO.
Ex:
HQL : select count(property),sum(property) ....
session.createQuery(buffer.toString()).
setResultTransformer( Transformers.aliasToBean(A.class)).list();