Execute package in hibernate

Question

I need to run a package through Hibernate,

create or replace PACKAGE BODY "ZIMFUNC" AS
...
FUNCTION ISNUMBER(value IN VARCHAR)
  RETURN NUMBER DETERMINISTIC 
AS
  RC NUMBER;
BEGIN
  IF (value IS NULL)
  THEN
    RETURN 0;
  END IF;
  RC := TO_NUMBER(value);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
...

I call this package through the Java methods

public Causer criarUsuarioOracle(Causer entity) {
    String user = entity.getUserlogin().trim();             
    try {           
        executeSql(z.getSql4());            
    } catch (DaoException e) {
        System.out.println("O usuario "+entity.getUserlogin()+" foi criado mas ja existe um user do oracle com esse nome.");
        e.printStackTrace();
    }
    entity.setUsercriabd('S');      
    return entity;
}

public void executeSql(String sql) throws DaoException {
    Transaction t = startTransaction();
    try{            
        getSession().createSQLQuery(sql).executeUpdate();
        commitTransaction(t);
    }catch(HibernateException e){
        rollbackTransaction(t);
        e.printStackTrace();
        throw e;
    }finally{
        if (isAutoCommit()) {
            getSession().close();
        }
    }
}

but it gives the following error message.

org.hibernate.QueryException: Space is not allowed after parameter prefix ':' 'create or replace PACKAGE BODY "ZIMFUNC" AS FUNCTION ... (SQL FUNCTION ABOVE) ... END'
at org.hibernate.engine.query.ParameterParser.parse(ParameterParser.java:92)
at org.hibernate.engine.query.ParamLocationRecognizer.parseLocations(ParamLocationRecognizer.java:75)
at org.hibernate.engine.query.QueryPlanCache.buildNativeSQLParameterMetadata(QueryPlanCache.java:149)
at org.hibernate.engine.query.QueryPlanCache.getSQLParameterMetadata(QueryPlanCache.java:79)
at org.hibernate.impl.AbstractSessionImpl.createSQLQuery(AbstractSessionImpl.java:146)
at org.hibernate.impl.SessionImpl.createSQLQuery(SessionImpl.java:1656)
at br.gov.es.dataci.commonshibernate.dao.impl.GenericDao.executeSql(GenericDao.java:484)
at br.gov.es.dataci.controleacesso.persistence.dao.impl.CauserDaoImpl.criarUsuarioOracle(CauserDaoImpl.java:251)
at br.gov.es.dataci.controleacesso.persistence.dao.impl.CauserDaoImpl.main(CauserDaoImpl.java:302)

It understands that the character ':' expects a Hibernate parameter

I inserted two backslashes before ':' in the Java String (SQL FUNCTION), but the error persists.

Can anybody help me?

Please post a [mcve] including the calling java code. and a full stack trace. Please put those additions in the question body, not the comments section. Thanks — OldProgrammer, Mar 12 '18 at 21:04
Don't put "SOLVED" into the title of the question. _Accept_ the answer that solved your problem. That's the only way to mark the question as solved. There is nothing wrong with accepting your own answer. — , Mar 15 '18 at 19:05

score 0 · Accepted Answer · answered Mar 21 '18 at 14:34

I solved the problem following this instructions of Maruisz S

If you don't upgrade Hibernate to 4.1.3. version "Simply use /'/:=/'/ inside the query. Hibernate code treats everything between ' as a string (ignores it). MySQL and Oracle on the other hand will ignore everything inside a blockquote and will evaluate the whole expression to an assignement operator. I know it's quick and dirty, but it get's the job done without stored procedures, interceptors etc."

through the link: How can I use MySQL assign operator(:=) in hibernate native query?

Execute package in hibernate

1 Answers1