Hi all, I'm trying Contact List sample to be run against Oracle. Everything goes well until I make a search by name. When I enter 'Gav' for the first name and 'K' for the last name, Seam (Oracle actually) gives an error that says: ORA-01722: invalid number! I found the reason was "contacts" entity-query found in components.xml:
| <fwk:entity-query name="contacts" | max-results="5"> | <fwk:ejbql>from Contact</fwk:ejbql> | <fwk:order>lastName</fwk:order> | <fwk:restrictions> | <value>lower(firstName) like lower( #{exampleContact.firstName} + '%' )</value> | <value>lower(lastName) like lower( #{exampleContact.lastName} + '%' )</value> | </fwk:restrictions> | </fwk:entity-query> | This query definition creates following SQL that HSQL accepts, but Oracle does not: | select * | from ( | select ... | from CONTACT c | where (lower(c.firstName) like lower('Gav') + '%') | and (lower(c.lastName) like lower('K') + '%') | order by ... | ) where rownum <= ... | What part Oracle does not allow is concatenation operation, '+'. Therefore, following SQL is perfectly acceptable (also by HSQL): | select * | from ( | select ... | from CONTACT c | where (lower(c.firstName) like lower('Gav%')) | and (lower(c.lastName) like lower('K%')) | order by ... | ) where rownum <= ... | What entity-query should I write to let Seam generate Oracle friendly SQL? Regards, Ali Sadik Kumlali View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4078006#4078006 Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4078006 _______________________________________________ jboss-user mailing list jboss-user@lists.jboss.org https://lists.jboss.org/mailman/listinfo/jboss-user