Re: EJB3/JPA with Oracle
Olaf Luetkehoelter <[email protected]> Tue, 29 Jul 2014 10:11:22 -0400
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <[email protected]> |
--===============1892092416778790578== Content-Type: multipart/alternative; boundary="Apple-Mail=_4763F7EF-FCF5-4816-9237-52ECDBDE3B74" --Apple-Mail=_4763F7EF-FCF5-4816-9237-52ECDBDE3B74 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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 [email protected] 919-727-6523 On Jul 29, 2014, at 08:37 , Hugh Allen <[email protected]> wrote: > Hi, >=20 > Hope we have some Oracle/JPA experts out there, because Google = searches are not showing me the answer. >=20 > 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. >=20 > Thought this would be a snap but no such luck. Oracle OWNS Java, for = crying out loud. How hard could this be??? >=20 > So I guess this is an Oracle newbie question. >=20 > 1) Resolved the 30 character limit on column names by changing JPA >=20 > 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.) >=20 > 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??? >=20 > 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? >=20 > Guessing many of you use Oracle. Any help on setting up Case = IN-SENSITIVE searches in Oracle? >=20 > Without changing all the queries to use LOWER()? >=20 > Thanks, Hugh >=20 >=20 >=20 >=20 >=20 > _______________________________________________ > Juglist mailing list > [email protected] > http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org --Apple-Mail=_4763F7EF-FCF5-4816-9237-52ECDBDE3B74 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html = charset=3Dus-ascii"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">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.<div><br></div><div>thanks,</div><div>Olaf<br><div><i = style=3D"font-family: 'Lucida Handwriting'; orphans: 2; text-align: = -webkit-auto; widows: 2;">---</i><div apple-content-edited=3D"true"><div = style=3D"color: rgb(0, 0, 0); font-family: Helvetica; font-style: = normal; font-variant: normal; font-weight: normal; letter-spacing: = normal; line-height: normal; orphans: 2; text-align: -webkit-auto; = text-indent: 0px; text-transform: none; white-space: normal; widows: 2; = word-spacing: 0px; -webkit-text-size-adjust: auto; = -webkit-text-stroke-width: 0px; word-wrap: break-word; = -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; = "><div><font face=3D"Lucida Handwriting"><i>Olaf = Luetkehoelter</i></font></div><div><font face=3D"Lucida Sans"><a = href=3D"mailto:[email protected]">[email protected]</a></font></div><div><font = face=3D"Lucida Sans">919-727-6523</font></div></div> </div> <br><div><div>On Jul 29, 2014, at 08:37 , Hugh Allen <<a = href=3D"mailto:[email protected]">[email protected]</a>> = wrote:</div><br class=3D"Apple-interchange-newline"><blockquote = type=3D"cite">Hi,<br><br>Hope we have some Oracle/JPA experts out there, = because Google searches are not showing me the answer.<br><br>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.<br><br>Thought this would be a snap but no such luck. Oracle = OWNS Java, for crying out loud. How hard could this be???<br><br>So I = guess this is an Oracle newbie question.<br><br>1) Resolved the 30 = character limit on column names by changing JPA<br><br>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.)<br><br>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???<br><br>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?<br><br>Guessing many of you use Oracle. Any help on = setting up Case IN-SENSITIVE searches in Oracle?<br><br>Without changing = all the queries to use LOWER()?<br><br>Thanks, = Hugh<br><br><br><br><br><br>______________________________________________= _<br>Juglist mailing list<br><a = href=3D"mailto:[email protected]">[email protected]</a><br>h= ttp://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org<br></bloc= kquote></div><br></div></div></body></html>= --Apple-Mail=_4763F7EF-FCF5-4816-9237-52ECDBDE3B74-- --===============1892092416778790578== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Juglist mailing list [email protected] http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org --===============1892092416778790578==--