EJB3/JPA with Oracle

Discussion:

Hugh Allen

2014-07-29 12:37:12 UTC

Hi,

Hope we have some Oracle/JPA experts out there, because Google searches are not showing me the answer.

After using MySQL and SQL Server exclusively for years, we are now porting our Java EJB3/JPA app to Oracle, based on requirements from a client.

Thought this would be a snap but no such luck. Oracle OWNS Java, for crying out loud. How hard could this be???

So I guess this is an Oracle newbie question.

1) Resolved the 30 character limit on column names by changing JPA

2) Resolved Oracle's inability to distinguish between NULL and Empty String. (Oracle calls an Empty String an IMPOSSIBLE VALUE! But in fact it is very possible and the DEFAULT in the app, which has NOT NULL for all strings.)

3) But now *CASE SENSITIVE Queries* are the problem. By default all Oracle queries are Case Sensitive! This is a ridiculous default, and there must be some way around it???

Discovered there is an Oracle setting for NLS_COMP but this is a SESSION parameter and JPA can not control Oracle Sessions? How can this be accomplished?

Guessing many of you use Oracle. Any help on setting up Case IN-SENSITIVE searches in Oracle?

Without changing all the queries to use LOWER()?

Thanks, Hugh

Manoj Bharadwaj

2014-07-29 13:00:43 UTC

Permalink

You should be able to configure the session properties in your DataSource
definition.

For example in TomEE, in tomee.xml there would be a "Resource" definition
which has property "ConnectionProperties" where session props can be set. A
similar config will be available in other containers.

I believe in Jboss you may be able to do something similar via
<new-connection-sql>

Give it a shot and do let me know what worked.

Olaf Luetkehoelter

2014-07-29 14:11:22 UTC

Permalink

A common way of implementing case insensitive search (and avoid full table scans) in Oracle is to add another column for each column that you want to search on, copy the to_upper() values of your original fields into the new column, add indexes on the new columns and use to_upper in your select statements.

thanks,
Olaf
---
Olaf Luetkehoelter
ol42-***@public.gmane.org
919-727-6523

Post by Hugh Allen
Hi,
Hope we have some Oracle/JPA experts out there, because Google searches are not showing me the answer.
After using MySQL and SQL Server exclusively for years, we are now porting our Java EJB3/JPA app to Oracle, based on requirements from a client.
Thought this would be a snap but no such luck. Oracle OWNS Java, for crying out loud. How hard could this be???
So I guess this is an Oracle newbie question.
1) Resolved the 30 character limit on column names by changing JPA
2) Resolved Oracle's inability to distinguish between NULL and Empty String. (Oracle calls an Empty String an IMPOSSIBLE VALUE! But in fact it is very possible and the DEFAULT in the app, which has NOT NULL for all strings.)
3) But now *CASE SENSITIVE Queries* are the problem. By default all Oracle queries are Case Sensitive! This is a ridiculous default, and there must be some way around it???
Discovered there is an Oracle setting for NLS_COMP but this is a SESSION parameter and JPA can not control Oracle Sessions? How can this be accomplished?
Guessing many of you use Oracle. Any help on setting up Case IN-SENSITIVE searches in Oracle?
Without changing all the queries to use LOWER()?
Thanks, Hugh
_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

Hugh Allen

2014-07-29 17:43:30 UTC

Permalink

_______________________________________________
Juglist mailing list
Juglist-***@public.gmane.org
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

Scott Snyder

2014-07-29 18:00:02 UTC

Permalink

Perhaps this reference will be of some help.
Lorenzo Alberton - Articles - Database identifiers, quoting and case sensitivity

Lorenzo Alberton - Articles - Database identifiers, quot...
Explaining SQL identifiers and case sensitivity. Lorenzo Alberton.
View on www.alberton.info Preview by Yahoo

The SQL:2008 and SQL-99 standards define databases to be case insensitive for identifiers unless they are quoted. Lower case characters may be used in identifiers and keywords, but are considered to be their upper case counterparts.
In other words, delimited identifiers are case sensitive ("table_name" != "Table_Name"), while non quoted identifiers are not, and are transformed to upper case (table_name => TABLE_NAME). The SQL-92 standard is even more explicit on how to handle lower/upper case letters in identifiers, while I find it a bit less clear on how delimited identifiers should be treated.

DB2, Oracle and Interbase/Firebird
DB2, Oracle and Interbase/Firebird are 100% compliant with the aforementioned standard. Their behavior is identical: the identifiers are treated in a case-sensitive way when quoted, and uppercased when not quoted.

Post by Hugh Allen
________________________________
Sent: Tuesday, July 29, 2014 1:43 PM
Subject: Re: [Juglist] EJB3/JPA with Oracle
DOUBLING the size of the database??? This seems a non-starter.
So basically, Oracle was written in 197X or so - I was around at the time - EVERYTHING was in CAPS - all mainframe data.
I did my MBA Thesis in 1974 on a mainframe on a 3270 green terminal at Merrill Lynch, where I was working, ALL IN CAPS. Had to explain why I had not used a TYPEWRITER.
Looks like Oracle is still in 1974. They expect us to load everything in the database in UPPER CASE?
This cross-database stuff is harder than it looks. JPA doesn't handle this in any way, with ORACLE, which is THE major database?
MySQL was easy. SQL Server only minor issues. Oracle is a nightmare of old landmines from the 70s.
What am I missing?
Hugh
-----Original Message-----

From: Olaf Luetkehoelter
Sent: Jul 29, 2014 10:11 AM
To: Hugh Allen
Subject: Re: [Juglist] EJB3/JPA with Oracle
A common way of implementing case insensitive search (and avoid full table scans) in Oracle is to add another column for each column that you want to search on, copy the to_upper() values of your original fields into the new column, add indexes on the new columns and use to_upper in your select statements.
thanks,
Olaf
---
Olaf Luetkehoelter
919-727-6523
Hi,

Post by Hugh Allen
Hope we have some Oracle/JPA experts out there, because Google searches are not showing me the answer.
After using MySQL and SQL Server exclusively for years, we are now porting our Java EJB3/JPA app to Oracle, based on requirements from a client.
Thought this would be a snap but no such luck. Oracle OWNS Java,

for crying out loud. How hard could this be???

Post by Hugh Allen

Post by Hugh Allen
So I guess this is an Oracle newbie question.
1) Resolved the 30 character limit on column names by changing JPA
2) Resolved Oracle's inability to distinguish between NULL and Empty String. (Oracle calls an Empty String an IMPOSSIBLE VALUE! But in fact it is very possible and the DEFAULT in the app, which has NOT NULL for all strings.)
3) But now *CASE SENSITIVE Queries* are the problem. By default all Oracle queries are Case Sensitive! This is a ridiculous default, and there must be some way around it???
Discovered there is an Oracle setting for NLS_COMP but this is a

SESSION parameter and JPA can not control Oracle Sessions? How can this be accomplished?

Post by Hugh Allen

Post by Hugh Allen
Guessing many of you use Oracle. Any help on setting up Case IN-SENSITIVE searches in Oracle?
Without changing all the queries to use LOWER()?
Thanks, Hugh
_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

Andrew Oliver

2014-07-29 19:27:47 UTC

Permalink

Yes Oracle sucks and is only exceeded in painfulness by DB2 which actually
has made forward progress in recent years (in that it supports something
other than pessimistic row locking on micros/minis and page locking on big
iron).

1. Simple Solution: https://forum.hibernate.org/viewtopic.php?f=1&t=972808
- backtick your column names in the annotations (but it is a big find and
replace code change)

2. More complex solution:
http://www.petrikainulainen.net/programming/tips-and-tricks/implementing-a-custom-namingstrategy-with-hibernate/

Example (someone wrote something like this but you may or may not like
their actual table name choices):
https://code.google.com/p/hibernate-naming-strategy-for-oracle/source/browse/#svn%2Ftrunk%2Fsrc%2Fde%2Fschauderhaft%2Fhibernate

3. Been awhile but I think you can also override the column names in XML
but it is kind of crappy and I vaguely remember there are limitations with
that: http://java.dzone.com/articles/persisting-entity-classes

-Andy

DOUBLING the size of the database??? This seems a non-starter.
So basically, Oracle was written in 197X or so - I was around at the time
- EVERYTHING was in CAPS - all mainframe data.
I did my MBA Thesis in 1974 on a mainframe on a 3270 green terminal at
Merrill Lynch, where I was working, ALL IN CAPS. Had to explain why I had
not used a TYPEWRITER.
Looks like Oracle is still in 1974. They expect us to load everything in
the database in UPPER CASE?
This cross-database stuff is harder than it looks. JPA doesn't handle this
in any way, with ORACLE, which is THE major database?
MySQL was easy. SQL Server only minor issues. Oracle is a nightmare of old
landmines from the 70s.
What am I missing?
Hugh
-----Original Message-----
From: Olaf Luetkehoelter
Sent: Jul 29, 2014 10:11 AM
To: Hugh Allen
Subject: Re: [Juglist] EJB3/JPA with Oracle
A common way of implementing case insensitive search (and avoid full table
scans) in Oracle is to add another column for each column that you want to
search on, copy the to_upper() values of your original fields into the new
column, add indexes on the new columns and use to_upper in your select
statements.
thanks,
Olaf
*---*
*Olaf Luetkehoelter*
919-727-6523
Hi,
Hope we have some Oracle/JPA experts out there, because Google searches
are not showing me the answer.
After using MySQL and SQL Server exclusively for years, we are now porting
our Java EJB3/JPA app to Oracle, based on requirements from a client.
Thought this would be a snap but no such luck. Oracle OWNS Java, for
crying out loud. How hard could this be???
So I guess this is an Oracle newbie question.
1) Resolved the 30 character limit on column names by changing JPA
2) Resolved Oracle's inability to distinguish between NULL and Empty
String. (Oracle calls an Empty String an IMPOSSIBLE VALUE! But in fact it
is very possible and the DEFAULT in the app, which has NOT NULL for all
strings.)
3) But now *CASE SENSITIVE Queries* are the problem. By default all Oracle
queries are Case Sensitive! This is a ridiculous default, and there must be
some way around it???
Discovered there is an Oracle setting for NLS_COMP but this is a SESSION
parameter and JPA can not control Oracle Sessions? How can this be
accomplished?
Guessing many of you use Oracle. Any help on setting up Case IN-SENSITIVE
searches in Oracle?
Without changing all the queries to use LOWER()?
Thanks, Hugh
_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org
_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

Hugh Allen

2014-07-29 20:42:02 UTC

Permalink

_______________________________________________
Juglist mailing list
Juglist-***@public.gmane.org
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

Andrew Oliver

2014-07-29 21:23:54 UTC

Permalink

<new-connection-sql> in your jboss connection pool config. Other
connection pools have similar stuff.

http://www.orafaq.com/node/999 shows what the query should be on various
Oracle versions. So it is something like this in your *-ds.xml file:

<new-connection-sql>
BEGIN
EXECUTE IMMEDIATE 'alter session set NLS_COMP=''ANSI''';
EXECUTE IMMEDIATE 'alter session set NLS_SORT='alter session set
NLS_SORT=''BINARY_CI''';
END
</new-connection-sql>

-Andy

Thanks Andy.
But we got by the TABLE name Case issue.
Rather is is Case Sensitivity in QUERIES.
- Database has Patient Name Smith
- User searches for SMITH
On MySql and SQL Server, this is a hit and returns search results.
NOTHING returned on Oracle unless search is for Smith with matching
capitalization.
There are Oracle i18n SESSION options for NLS_SORT and NLS_COMP that may
be a solution? But not settable in JPA?
---
You should be able to configure the session properties in your DataSource
definition.
For example in TomEE, in tomee.xml there would be a "Resource" definition
which has property "ConnectionProperties" where session props can be set. A
similar config will be available in other containers.
I believe in Jboss you may be able to do something similar via
<new-connection-sql>
---
Brute force option seems to be to change all HQL to use UPPER() or
LOWER(), but that is HUNDREDS of queries.
Other options? This is nasty.
Thanks, Hugh
-----Original Message-----
From: Andrew Oliver
Sent: Jul 29, 2014 3:27 PM
Subject: Re: [Juglist] EJB3/JPA with Oracle
Yes Oracle sucks and is only exceeded in painfulness by DB2 which actually
has made forward progress in recent years (in that it supports something
other than pessimistic row locking on micros/minis and page locking on big
iron).
1. Simple Solution: https://forum.hibernate.org/viewtopic.php?f=1&t=972808
- backtick your column names in the annotations (but it is a big find and
replace code change)
http://www.petrikainulainen.net/programming/tips-and-tricks/implementing-a-custom-namingstrategy-with-hibernate/
Example (someone wrote something like this but you may or may not like
https://code.google.com/p/hibernate-naming-strategy-for-oracle/source/browse/#svn%2Ftrunk%2Fsrc%2Fde%2Fschauderhaft%2Fhibernate
3. Been awhile but I think you can also override the column names in XML
but it is kind of crappy and I vaguely remember there are limitations with
that: http://java.dzone.com/articles/persisting-entity-classes
-Andy

DOUBLING the size of the database??? This seems a non-starter.
So basically, Oracle was written in 197X or so - I was around at the time
- EVERYTHING was in CAPS - all mainframe data.
I did my MBA Thesis in 1974 on a mainframe on a 3270 green terminal at
Merrill Lynch, where I was working, ALL IN CAPS. Had to explain why I had
not used a TYPEWRITER.
Looks like Oracle is still in 1974. They expect us to load everything in
the database in UPPER CASE?
This cross-database stuff is harder than it looks. JPA doesn't handle
this in any way, with ORACLE, which is THE major database?
MySQL was easy. SQL Server only minor issues. Oracle is a nightmare of
old landmines from the 70s.
What am I missing?
Hugh
-----Original Message-----
From: Olaf Luetkehoelter
Sent: Jul 29, 2014 10:11 AM
To: Hugh Allen
Subject: Re: [Juglist] EJB3/JPA with Oracle
A common way of implementing case insensitive search (and avoid full
table scans) in Oracle is to add another column for each column that you
want to search on, copy the to_upper() values of your original fields into
the new column, add indexes on the new columns and use to_upper in your
select statements.
thanks,
Olaf
*---*
*Olaf Luetkehoelter*
919-727-6523
Hi,
Hope we have some Oracle/JPA experts out there, because Google searches
are not showing me the answer.
After using MySQL and SQL Server exclusively for years, we are now
porting our Java EJB3/JPA app to Oracle, based on requirements from a
client.
Thought this would be a snap but no such luck. Oracle OWNS Java, for
crying out loud. How hard could this be???
So I guess this is an Oracle newbie question.
1) Resolved the 30 character limit on column names by changing JPA
2) Resolved Oracle's inability to distinguish between NULL and Empty
String. (Oracle calls an Empty String an IMPOSSIBLE VALUE! But in fact it
is very possible and the DEFAULT in the app, which has NOT NULL for all
strings.)
3) But now *CASE SENSITIVE Queries* are the problem. By default all
Oracle queries are Case Sensitive! This is a ridiculous default, and there
must be some way around it???
Discovered there is an Oracle setting for NLS_COMP but this is a SESSION
parameter and JPA can not control Oracle Sessions? How can this be
accomplished?
Guessing many of you use Oracle. Any help on setting up Case IN-SENSITIVE
searches in Oracle?
Without changing all the queries to use LOWER()?
Thanks, Hugh
_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org
_______________________________________________
Juglist mailing list
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org

Hugh Allen

2014-07-30 10:22:39 UTC

Permalink

_______________________________________________
Juglist mailing list
Juglist-***@public.gmane.org
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org