Re: EJB3/JPA with Oracle
Scott Snyder <sh_snyder-/[email protected]> Tue, 29 Jul 2014 11:00:02 -0700
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <[email protected]> |
--===============5010891236874574142==
Content-Type: multipart/alternative; boundary="-185498972-337957796-1406656802=:73186"
---185498972-337957796-1406656802=:73186
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
=0A=0A=0APerhaps this reference will be of some help.=0ALorenzo Alberton - =
Articles - Database identifiers, quoting and case sensitivity=0A=0A=A0 =0A=
=A0 =A0 =A0 =A0 =A0 =A0 =0ALorenzo Alberton - Articles - Database identifi=
ers, quot...=0AExplaining SQL identifiers and case sensitivity. Lorenzo Alb=
erton. =0AView on=A0www.alberton.info Preview by Yahoo =0A=A0 =0A=0AThe=
=A0SQL:2008=A0and=A0SQL-99 standards=A0define databases to be case insensit=
ive for identifiers unless they are quoted. Lower case characters may be us=
ed in identifiers and keywords, but are considered to be their upper case c=
ounterparts.=0AIn other words,=A0delimited identifiers are case sensitive=
=A0("table_name" !=3D "Table_Name"), while=A0non quoted identifiers are not=
, and are transformed to upper case (table_name =3D> TABLE_NAME). The=A0SQL=
-92 standard=A0is even more explicit on how to handle lower/upper case lett=
ers in identifiers, while I find it a bit less clear on how delimited ident=
ifiers should be treated.=0A=0A=0ADB2, Oracle and Interbase/Firebird=0ADB2,=
Oracle and Interbase/Firebird are 100% compliant with the aforementioned s=
tandard. Their behavior is identical: the identifiers are treated in a case=
-sensitive way when quoted, and uppercased when not quoted.=0A=0A>=0A>=0A>>=
________________________________=0A>> From: Hugh Allen <hughallen@mindsprin=
g.com>=0A>>To: Olaf Luetkehoelter <[email protected]> =0A>>Cc:=A0juglist@lists=
.trijug.org=A0=0A>>Sent: Tuesday, July 29, 2014 1:43 PM=0A>>Subject: Re: [J=
uglist] EJB3/JPA with Oracle=0A>> =0A>>=0A>>=0A>>DOUBLING the size of the d=
atabase???=A0This seems a non-starter.=0A>>=0A>>=0A>>So basically, Oracle w=
as written in 197X or so - I was around at the time - EVERYTHING was in CAP=
S - all mainframe data.=0A>>=0A>>=0A>>I did my MBA Thesis in 1974 on a main=
frame on a 3270 green terminal at Merrill Lynch, where I was working, ALL I=
N CAPS. Had to explain why I had not used a TYPEWRITER.=0A>>=0A>>=0A>>Looks=
like Oracle is still in 1974. They expect us to load everything in the dat=
abase in UPPER CASE?=0A>>=0A>>=0A>>This cross-database stuff is harder than=
it looks. JPA doesn't handle this in any way, with ORACLE, which is THE ma=
jor database?=0A>>=0A>>=0A>>MySQL was easy. SQL Server only minor issues. O=
racle is a nightmare of old landmines from the 70s.=0A>>=0A>>=0A>>What am I=
missing?=0A>>=0A>>=0A>>Hugh=0A>>=0A>>=0A>>-----Original Message----- =0A>>=
>From: Olaf Luetkehoelter =0A>>>Sent: Jul 29, 2014 10:11 AM =0A>>>To: Hugh =
Allen =0A>>>Cc: [email protected] =0A>>>Subject: Re: [Juglist] EJB3/=
JPA with Oracle =0A>>>=0A>>>A common way of implementing case insensitive s=
earch (and avoid full table scans) in Oracle is to add another column for e=
ach column that you want to search on, copy the to_upper() values of your o=
riginal fields into the new column, add indexes on the new columns and use =
to_upper in your select statements.=0A>>>=0A>>>=0A>>>thanks,=0A>>>Olaf=0A>>=
>=0A>>>---=0A>>>Olaf Luetkehoelter=0A>>>[email protected]=0A>>>919-727-6523=0A=
>>>=0A>>>On Jul 29, 2014, at 08:37 , Hugh Allen <[email protected]> =
wrote:=0A>>>=0A>>>Hi,=0A>>>>=0A>>>>Hope we have some Oracle/JPA experts out=
there, because Google searches are not showing me the answer.=0A>>>>=0A>>>=
>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.=0A>>=
>>=0A>>>>Thought this would be a snap but no such luck. Oracle OWNS Java,=
=0A for crying out loud. How hard could this be???=0A>>>>=0A>>>>So I guess =
this is an Oracle newbie question.=0A>>>>=0A>>>>1) Resolved the 30 characte=
r limit on column names by changing JPA=0A>>>>=0A>>>>2) Resolved Oracle's i=
nability to distinguish between NULL and Empty String. (Oracle calls an Emp=
ty String an IMPOSSIBLE VALUE! But in fact it is very possible and the DEFA=
ULT in the app, which has NOT NULL for all strings.)=0A>>>>=0A>>>>3) But no=
w *CASE SENSITIVE Queries* are the problem. By default all Oracle queries a=
re Case Sensitive! This is a ridiculous default, and there must be some way=
around it???=0A>>>>=0A>>>>Discovered there is an Oracle setting for NLS_CO=
MP but this is a=0A SESSION parameter and JPA can not control Oracle Sessio=
ns? How can this be accomplished?=0A>>>>=0A>>>>Guessing many of you use Ora=
cle. Any help on setting up Case IN-SENSITIVE searches in Oracle?=0A>>>>=0A=
>>>>Without changing all the queries to use LOWER()?=0A>>>>=0A>>>>Thanks, H=
ugh=0A>>>>=0A>>>>=0A>>>>=0A>>>>=0A>>>>=0A>>>>______________________________=
_________________=0A>>>>Juglist mailing list=0A>>>>[email protected]=
=0A>>>>http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org=0A=
>>>>=0A>>>=0A>>=0A>>_______________________________________________=0A>>Jug=
list mailing list=0A>>[email protected]=0A>>http://lists.trijug.org/=
mailman/listinfo/juglist_lists.trijug.org=0A>>=0A>>=0A>>=0A>=0A>
---185498972-337957796-1406656802=:73186
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
<html><body><div style=3D"color:#000; background-color:#fff; font-family:ve=
rdana, helvetica, sans-serif;font-size:10pt"><div id=3D"yiv8738275098"><div=
class=3D"qtdSeparateBR"><br><br></div><div class=3D"yiv8738275098yqt582457=
6281" id=3D"yiv8738275098yqtfd59807"><div><div style=3D"color: rgb(0, 0, 0)=
; font-family: verdana, helvetica, sans-serif; font-size: 10pt; background-=
color: rgb(255, 255, 255);"><div class=3D"yiv8738275098" id=3D"yiv873827509=
8yui_3_16_0_1_1406255979191_403540" style=3D""><span class=3D"yiv8738275098=
" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403539" style=3D"">Perhaps =
this reference will be of some help.</span></div><div class=3D"yiv873827509=
8" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403545" style=3D"backgroun=
d-color:transparent;"><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_=
3_16_0_1_1406255979191_403544" style=3D""><a rel=3D"nofollow" shape=3D"rect=
" class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403=
543" target=3D"_blank"
href=3D"http://www.alberton.info/dbms_identifiers_and_case_sensitivity.htm=
l#.U9fduY1dVL4" style=3D"">Lorenzo Alberton - Articles - Database identifie=
rs, quoting and case sensitivity</a><br clear=3D"none" class=3D"yiv87382750=
98" style=3D""></span></div><div class=3D"yiv8738275098" id=3D"yiv873827509=
8enhancrCard_0" style=3D"width: 450px; font-family: Georgia, Times, 'Times =
New Roman', serif; margin-top: 5px; margin-bottom: 5px;"><table class=3D"yi=
v8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403549" cellspac=
ing=3D"0" cellpadding=3D"0" border=3D"0" style=3D"display:block;width:450px=
;height:170px;"><tbody class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_=
0_1_1406255979191_403548" style=3D"width:450px;"><tr class=3D"yiv8738275098=
" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403718" style=3D""><td cols=
pan=3D"8" rowspan=3D"1" class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16=
_0_1_1406255979191_403717" style=3D"height:1px;font-size:1px;border-collaps=
e:collapse;background-color:rgb(229, 229,
229);"><div class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_140625=
5979191_403716" style=3D"height:1px;line-height:0px;"> </div></td></tr=
><tr class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_=
403547" style=3D""><td colspan=3D"1" rowspan=3D"5" class=3D"yiv8738275098" =
style=3D"width:1px;font-size:1pt;border-collapse:collapse;background-color:=
rgb(229, 229, 229);"><div class=3D"yiv8738275098" style=3D"width:1px;font-s=
ize:1pt;"> </div></td><td colspan=3D"1" rowspan=3D"5" class=3D"yiv8738=
275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403546" style=3D"vert=
ical-align:middle;width:168px;height:168px;border-collapse:collapse;backgro=
und-color:rgb(0, 0, 0);"><div align=3D"center" class=3D"yiv8738275098" id=
=3D"yiv8738275098yui_3_16_0_1_1406255979191_403552" style=3D"width:168px;">=
<a rel=3D"nofollow" shape=3D"rect" class=3D"yiv8738275098" id=3D"yiv8738275=
098yui_3_16_0_1_1406255979191_403551" target=3D"_blank"
href=3D"http://www.alberton.info/dbms_identifiers_and_case_sensitivity.htm=
l#.U9fduY1dVL4" style=3D"text-decoration:none;color:rgb(0, 0, 0);"><img cla=
ss=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403550" =
alt=3D"image" src=3D"http://www.alberton.info/images/mysql5_certified_170.p=
ng" width=3D"168" height=3D"98" style=3D"display:block;margin:auto;"></a></=
div></td><td colspan=3D"1" rowspan=3D"5" class=3D"yiv8738275098" id=3D"yiv8=
738275098yui_3_16_0_1_1406255979191_403715" style=3D"width:1px;font-size:0p=
t;border-collapse:collapse;background-color:rgb(229, 229, 229);"><div class=
=3D"yiv8738275098" style=3D"width:1px;font-size:1pt;"> </div></td><td =
colspan=3D"1" rowspan=3D"5" class=3D"yiv8738275098" id=3D"yiv8738275098yui_=
3_16_0_1_1406255979191_403553" style=3D"width:14px;font-size:0pt;border-col=
lapse:collapse;"><div class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0=
_1_1406255979191_403570" style=3D"width:14px;font-size:14pt;"> </div><=
/td><td colspan=3D"2" rowspan=3D"1"
class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_4037=
20" style=3D"height:6px;font-size:0pt;border-collapse:collapse;"><div class=
=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403719" st=
yle=3D"height:6px;font-size:6pt;"> </div></td><td colspan=3D"1" rowspa=
n=3D"5" class=3D"yiv8738275098" style=3D"width:20px;font-size:0pt;border-co=
llapse:collapse;"><div class=3D"yiv8738275098" style=3D"width:20px;font-siz=
e:20pt;"> </div></td><td colspan=3D"1" rowspan=3D"5" class=3D"yiv87382=
75098" width=3D"1" style=3D"width:1px;font-size:1pt;border-collapse:collaps=
e;background-color:rgb(229, 229, 229);"><div class=3D"yiv8738275098" style=
=3D"width:1px;font-size:1pt;"> </div></td></tr><tr class=3D"yiv8738275=
098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403556" style=3D""><td c=
olspan=3D"2" rowspan=3D"1" class=3D"yiv8738275098" id=3D"yiv8738275098yui_3=
_16_0_1_1406255979191_403555" style=3D"width:245px;vertical-align:middle;">=
<div class=3D"yiv8738275098"
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403554" style=3D"line-height=
:16.5px;height:135px;width:245px;"><div class=3D"yiv8738275098" id=3D"yiv87=
38275098yui_3_16_0_1_1406255979191_403558" style=3D"word-wrap:break-word;">=
<span class=3D"yiv8738275098" style=3D""></span><span class=3D"yiv873827509=
8" style=3D""></span><a rel=3D"nofollow" shape=3D"rect" class=3D"yiv8738275=
098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403559" target=3D"_blank=
" href=3D"http://www.alberton.info/dbms_identifiers_and_case_sensitivity.ht=
ml#.U9fduY1dVL4" style=3D"text-decoration:none;color:rgb(0, 0, 0);line-heig=
ht:18px;font-size:18px;display:block;"><span class=3D"yiv8738275098" id=3D"=
yiv8738275098yui_3_16_0_1_1406255979191_403560" style=3D"margin:0px 0px 3px=
;line-height:21px;max-height:43px;overflow:hidden;display:inline-block;">Lo=
renzo Alberton - Articles - Database identifiers, quot...</span></a><div cl=
ass=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403557"
style=3D"line-height:20px;color:rgb(153, 153, 153);max-height:81px;overflo=
w:hidden;">Explaining SQL identifiers and case sensitivity. Lorenzo=0A Albe=
rton.</div></div></div></td></tr><tr class=3D"yiv8738275098" style=3D""><td=
colspan=3D"2" rowspan=3D"1" class=3D"yiv8738275098" style=3D"height:4px;fo=
nt-size:0pt;border-collapse:collapse;"><div class=3D"yiv8738275098" style=
=3D"height:4px;font-size:4pt;"></div></td></tr><tr class=3D"yiv8738275098" =
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403747" style=3D""><td colspa=
n=3D"1" rowspan=3D"1" class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0=
_1_1406255979191_403746" style=3D"vertical-align: middle; font-family: Aria=
l, 'Helvetica Neue', Helvetica, sans-serif;"><div class=3D"yiv8738275098" i=
d=3D"yiv8738275098yui_3_16_0_1_1406255979191_403745" style=3D"font-size:0pt=
;"><a rel=3D"nofollow" shape=3D"rect" class=3D"yiv8738275098" id=3D"yiv8738=
275098yui_3_16_0_1_1406255979191_403744" target=3D"_blank" href=3D"http://w=
ww.alberton.info/dbms_identifiers_and_case_sensitivity.html#.U9fduY1dVL4" s=
tyle=3D"text-decoration:none;color:black;cursor:pointer;"><span class=3D"yi=
v8738275098"
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403743" style=3D"display:inl=
ine-block;line-height:11px;max-width:145px;min-width:85px;overflow:hidden;m=
ax-height:13px;"><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_=
0_1_1406255979191_403742" style=3D"vertical-align:middle;font-size:9px;colo=
r:rgb(153, 153, 153);">View on <span class=3D"yiv8738275098" style=3D"=
font-weight:bold;">www.alberton.info</span></span></span></a></div></td><td=
colspan=3D"1" rowspan=3D"1" class=3D"yiv8738275098" style=3D"vertical-alig=
n: middle; width: 100px; font-family: Arial, 'Helvetica Neue', Helvetica, s=
ans-serif;"><div class=3D"yiv8738275098" style=3D"max-width:100px;min-width=
:80px;overflow:hidden;text-align:right;line-height:11px;max-height:13px;fon=
t-size:0pt;"><span class=3D"yiv8738275098" style=3D"vertical-align:middle;f=
ont-size:9px;color:rgb(153, 153, 153);">Preview by Yahoo</span></div></td><=
/tr><tr class=3D"yiv8738275098" style=3D""><td colspan=3D"2" rowspan=3D"1" =
class=3D"yiv8738275098"
style=3D"height:9px;font-size:0pt;border-collapse:collapse;"><div class=3D=
"yiv8738275098" style=3D"height:9px;font-size:9pt;"></div></td></tr><tr cla=
ss=3D"yiv8738275098" style=3D""><td colspan=3D"8" rowspan=3D"1" class=3D"yi=
v8738275098" style=3D"height:1px;font-size:1px;border-collapse:collapse;bac=
kground-color:rgb(229, 229, 229);"><div class=3D"yiv8738275098" style=3D"he=
ight:1px;line-height:0px;"> </div></td></tr></tbody></table></div><div=
class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_4037=
11" style=3D"background-color:transparent;"><br clear=3D"none" class=3D"yiv=
8738275098" style=3D""></div><div class=3D"yiv8738275098" id=3D"yiv87382750=
98yui_3_16_0_1_1406255979191_403713" style=3D"background-color:transparent;=
"><span class=3D"yiv8738275098" style=3D"color: rgb(98, 98, 98); font-famil=
y: Helvetica, Arial, Verdana, sans-serif; line-height: 20px; text-align: ju=
stify;">The </span><b class=3D"yiv8738275098" style=3D"">SQL:2008</b><=
span class=3D"yiv8738275098" style=3D"color:
rgb(98, 98, 98); font-family: Helvetica, Arial, Verdana, sans-serif; line-=
height: 20px; text-align: justify;"> and </span><b class=3D"yiv87=
38275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403712" style=3D"">=
SQL-99 standards</b><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_=
16_0_1_1406255979191_403714" style=3D"color: rgb(98, 98, 98); font-family: =
Helvetica, Arial, Verdana, sans-serif; line-height: 20px; text-align: justi=
fy;"> define databases to be case insensitive for identifiers unless t=
hey are quoted. Lower case characters may be used in identifiers and keywor=
ds, but are considered to be their upper case counterparts.</span><br clear=
=3D"none" class=3D"yiv8738275098" style=3D"color: rgb(98, 98, 98); font-fam=
ily: Helvetica, Arial, Verdana, sans-serif; line-height: 20px; text-align: =
justify;"><span class=3D"yiv8738275098" style=3D"color: rgb(98, 98, 98); fo=
nt-family: Helvetica, Arial, Verdana, sans-serif; line-height: 20px; text-a=
lign:
justify;">In other words, </span><b class=3D"yiv8738275098" id=3D"yiv=
8738275098yui_3_16_0_1_1406255979191_403748" style=3D"">delimited identifie=
rs are case sensitive</b><span class=3D"yiv8738275098" style=3D"color: rgb(=
98, 98, 98); font-family: Helvetica, Arial, Verdana, sans-serif; line-heigh=
t: 20px; text-align: justify;"> ("table_name" !=3D "Table_Name"), whil=
e </span><b class=3D"yiv8738275098" style=3D"">non quoted identifiers =
are not</b><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_14=
06255979191_403749" style=3D"color: rgb(98, 98, 98); font-family: Helvetica=
, Arial, Verdana, sans-serif; line-height: 20px; text-align: justify;">, an=
d are transformed to upper case (table_name =3D> TABLE_NAME). The <=
/span><a rel=3D"nofollow" shape=3D"rect" class=3D"yiv8738275098" target=3D"=
_blank" href=3D"http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt" =
style=3D"text-decoration: none; color: rgb(204, 102, 0); font-family: Helve=
tica, Arial, Verdana,
sans-serif; line-height: 20px; text-align: justify; background-color: rgb(=
255, 255, 255);"><b class=3D"yiv8738275098" style=3D"">SQL-92 standard</b><=
/a><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979=
191_403750" style=3D"color: rgb(98, 98, 98); font-family: Helvetica, Arial,=
Verdana, sans-serif; line-height: 20px; text-align: justify;"> is eve=
n more explicit on how to handle lower/upper case letters in identifiers, w=
hile I find it a bit less clear on how delimited identifiers should be trea=
ted.</span><br clear=3D"none" class=3D"yiv8738275098" style=3D""></div><div=
class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_4037=
51" style=3D"color: rgb(204, 102, 0); font-family: Helvetica, Arial, Verdan=
a, sans-serif; background-color: transparent;"><span class=3D"yiv8738275098=
" style=3D"color:rgb(98, 98, 98);line-height:20px;text-align:justify;"><br =
clear=3D"none" class=3D"yiv8738275098" style=3D""></span></div><h3 class=3D=
"yiv8738275098"
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403752" style=3D"margin-top:=
15px; margin-bottom: 3px; font-size: 1.25em; font-weight: normal; clear: b=
oth; line-height: 1.5em; font-family: Arial, Verdana, sans-serif; text-alig=
n: justify;">DB2, Oracle and Interbase/Firebird</h3><div class=3D"yiv873827=
5098" style=3D"color: rgb(98, 98, 98); font-family: Helvetica, Arial, Verda=
na, sans-serif; background-color: transparent;"><span class=3D"yiv873827509=
8" style=3D"line-height:20px;text-align:justify;"></span></div><div class=
=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_10_1406255979191_4" style=
=3D""><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_10_140625=
5979191_26" style=3D""></span></div><div class=3D"yiv8738275098" id=3D"yiv8=
738275098yui_3_16_0_1_1406255979191_403561" style=3D"margin-bottom: 0.8em; =
color: rgb(98, 98, 98); font-family: Helvetica, Arial, Verdana, sans-serif;=
line-height: 20px; text-align: justify;">DB2, Oracle and Interbase/Firebir=
d 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 qu=
oted.</div><div class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_10_14=
06255979191_19" style=3D""></div><blockquote class=3D"yiv8738275098" id=3D"=
yiv8738275098yui_3_16_0_10_1406255979191_9" style=3D"border-left:2px solid =
rgb(16, 16, 255);margin-left:5px;margin-top:5px;padding-left:5px;"><div cla=
ss=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403536" =
style=3D"font-family: verdana, helvetica, sans-serif; font-size: 10pt;"><di=
v class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403=
535" style=3D"font-family: HelveticaNeue, 'Helvetica Neue', Helvetica, Aria=
l, 'Lucida Grande', sans-serif; font-size: 12pt;"><div class=3D"yiv87382750=
98" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403538" style=3D""><div c=
lass=3D"yiv8738275098" id=3D"yiv8738275098" style=3D""><div class=3D"yiv873=
8275098"
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403542" style=3D""><div clas=
s=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403541" s=
tyle=3D"color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif; f=
ont-size: 10pt; background-color: rgb(255, 255, 255);"><div class=3D"yiv873=
8275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403562" style=3D""><=
br clear=3D"none" class=3D"yiv8738275098" style=3D""></div><blockquote clas=
s=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403567" s=
tyle=3D"border-left:2px solid rgb(16, 16, 255);margin-left:5px;margin-top:5=
px;padding-left:5px;"> <div class=3D"yiv8738275098" id=3D"yiv8738275098yui=
_3_16_0_1_1406255979191_403566" style=3D"font-family: verdana, helvetica, s=
ans-serif; font-size: 10pt;"> <div class=3D"yiv8738275098" id=3D"yiv8738275=
098yui_3_16_0_1_1406255979191_403565" style=3D"font-family: HelveticaNeue, =
'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size:=
12pt;"> <div
class=3D"yiv8738275098" id=3D"yiv8738275098yqt56050" style=3D""><div class=
=3D"yiv8738275098" dir=3D"ltr" id=3D"yiv8738275098yui_3_16_0_1_140625597919=
1_403564" style=3D""> <hr class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_=
16_0_1_1406255979191_403892" size=3D"1" style=3D""> <font class=3D"yiv8738=
275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403563" size=3D"2" fa=
ce=3D"Arial" style=3D""> <b class=3D"yiv8738275098" style=3D""><span class=
=3D"yiv8738275098" style=3D"font-weight:bold;">From:</span></b> Hugh Allen =
<[email protected]><br clear=3D"none" class=3D"yiv8738275098" =
style=3D""> <b class=3D"yiv8738275098" style=3D""><span class=3D"yiv8738275=
098" style=3D"font-weight:bold;">To:</span></b> Olaf Luetkehoelter <ol42=
@nc.rr.com> <br clear=3D"none" class=3D"yiv8738275098" style=3D""><b cla=
ss=3D"yiv8738275098" style=3D""><span class=3D"yiv8738275098" style=3D"font=
-weight:bold;">Cc:</span></b> [email protected] <br clear=
=3D"none" class=3D"yiv8738275098" style=3D""> <b
class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_4035=
69" style=3D""><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_=
1_1406255979191_403568" style=3D"font-weight:bold;">Sent:</span></b> Tuesda=
y, July 29, 2014 1:43 PM<br clear=3D"none" class=3D"yiv8738275098" style=3D=
""> <b class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_140625597919=
1_403895" style=3D""><span class=3D"yiv8738275098" id=3D"yiv8738275098yui_3=
_16_0_1_1406255979191_403894" style=3D"font-weight:bold;">Subject:</span></=
b> Re: [Juglist] EJB3/JPA with Oracle<br clear=3D"none" class=3D"yiv8738275=
098" style=3D""> </font> </div> <div class=3D"yiv8738275098" id=3D"yiv87382=
75098yui_3_16_0_1_1406255979191_403758" style=3D""><br clear=3D"none" class=
=3D"yiv8738275098" style=3D""><div class=3D"yiv8738275098" id=3D"yiv8738275=
098" style=3D""><style type=3D"text/css">#yiv8738275098 -- DIV {margin:0=
px;}#yiv8738275098 </style><div class=3D"yiv8738275098" id=3D"yiv8738275098=
yui_3_16_0_1_1406255979191_403757" style=3D""><div
class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_4037=
56" style=3D"font-size: 13px; color: rgb(0, 0, 0); font-family: arial, sans=
-serif;">DOUBLING the size of the database??? <span class=3D"yiv873827=
5098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403891" style=3D"font-s=
ize:10pt;">This seems a non-starter.</span><div class=3D"yiv8738275098" id=
=3D"yiv8738275098yui_3_16_0_1_1406255979191_403761" style=3D""><br clear=3D=
"none" class=3D"yiv8738275098" style=3D""></div><div class=3D"yiv8738275098=
" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403890" style=3D"">So basic=
ally, Oracle was written in 197X or so - I was around at the time -=0A EVER=
YTHING was in CAPS - all mainframe data.</div><div class=3D"yiv8738275098" =
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403760" style=3D""><br clear=
=3D"none" class=3D"yiv8738275098" style=3D""></div><div class=3D"yiv8738275=
098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403759" style=3D"">I did=
my MBA Thesis in 1974 on a mainframe on a 3270 green terminal at Merrill L=
ynch, where I was working, ALL IN CAPS. Had to explain why I=0A had not use=
d a TYPEWRITER.</div><div class=3D"yiv8738275098" id=3D"yiv8738275098yui_3_=
16_0_1_1406255979191_403755" style=3D""><br clear=3D"none" class=3D"yiv8738=
275098" style=3D""></div><div class=3D"yiv8738275098" id=3D"yiv8738275098yu=
i_3_16_0_1_1406255979191_403889" style=3D"">Looks like Oracle is still in 1=
974. They expect us to load everything in the database in UPPER CASE?</div>=
<div class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv8738=
275098" style=3D""></div><div class=3D"yiv8738275098" id=3D"yiv8738275098yu=
i_3_16_0_1_1406255979191_403888" style=3D"">This cross-database stuff is ha=
rder than it looks. JPA doesn't handle this in any way, with ORACLE, which =
is THE major database?</div><div class=3D"yiv8738275098" style=3D""><br cle=
ar=3D"none" class=3D"yiv8738275098" style=3D""></div><div class=3D"yiv87382=
75098" id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403887" style=3D"">MyS=
QL was easy. SQL Server only minor issues. Oracle is a nightmare of old lan=
dmines from the 70s.</div><div
class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv87382750=
98" style=3D""></div><div class=3D"yiv8738275098" style=3D"">What am I miss=
ing?</div><div class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=
=3D"yiv8738275098" style=3D""></div><div class=3D"yiv8738275098" id=3D"yiv8=
738275098yui_3_16_0_1_1406255979191_403886" style=3D"">Hugh<br clear=3D"non=
e" class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv873827=
5098" style=3D""><blockquote class=3D"yiv8738275098" id=3D"yiv8738275098yui=
_3_16_0_1_1406255979191_403885" style=3D"padding-left: 5px; margin-left: 0p=
x; border-left-color: rgb(0, 0, 255); border-left-width: 2px; border-left-s=
tyle: solid; font-weight: normal; font-style: normal; text-decoration: none=
; font-size: 10pt; font-family: arial, sans-serif; color: black;">-----Orig=
inal Message-----=0A<br clear=3D"none" class=3D"yiv8738275098" style=3D"">F=
rom: Olaf Luetkehoelter =0A<br clear=3D"none" class=3D"yiv8738275098" style=
=3D"">Sent: Jul 29, 2014 10:11 AM=0A<br clear=3D"none" class=3D"yiv87382750=
98" style=3D"">To: Hugh Allen =0A<br clear=3D"none" class=3D"yiv8738275098"=
style=3D"">Cc: [email protected]=0A<br clear=3D"none" class=3D"yiv8=
738275098" style=3D"">Subject: Re: [Juglist] EJB3/JPA with Oracle=0A=0A<br =
clear=3D"none" class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=
=3D"yiv8738275098" style=3D""> A common way of implementing case insensitiv=
e search (and avoid full table scans) in Oracle is to add another column fo=
r each column that you want to search on, copy the to_upper() values of you=
r original fields into the new column, add indexes on the new columns and u=
se to_upper in your select statements.<div class=3D"yiv8738275098" style=3D=
""><br clear=3D"none" class=3D"yiv8738275098" style=3D""></div><div class=
=3D"yiv8738275098" style=3D"">thanks,</div><div class=3D"yiv8738275098" id=
=3D"yiv8738275098yui_3_16_0_1_1406255979191_403899" style=3D"">Olaf<br clea=
r=3D"none" class=3D"yiv8738275098" style=3D""><div class=3D"yiv8738275098" =
id=3D"yiv8738275098yui_3_16_0_1_1406255979191_403898" style=3D""><i class=
=3D"yiv8738275098" style=3D"font-family: 'Lucida Handwriting'; orphans: 2; =
widows: 2;">---</i><div class=3D"yiv8738275098" style=3D""><div class=3D"yi=
v8738275098" style=3D"color: rgb(0, 0, 0);
font-family: Helvetica; font-style: normal; font-variant: normal; font-wei=
ght: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-=
indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spa=
cing: 0px; word-wrap: break-word;"><div class=3D"yiv8738275098" style=3D"">=
<font class=3D"yiv8738275098" face=3D"Lucida Handwriting" style=3D""><i cla=
ss=3D"yiv8738275098" style=3D"">Olaf Luetkehoelter</i></font></div><div cla=
ss=3D"yiv8738275098" style=3D""><font class=3D"yiv8738275098" face=3D"Lucid=
a Sans" style=3D""><a rel=3D"nofollow" shape=3D"rect" class=3D"yiv873827509=
8" ymailto=3D"mailto:[email protected]" target=3D"_blank" href=3D"mailto:ol42@=
nc.rr.com" style=3D"">[email protected]</a></font></div><div class=3D"yiv87382=
75098" style=3D""><font class=3D"yiv8738275098" face=3D"Lucida Sans" style=
=3D"">919-727-6523</font></div></div>=0A</div>=0A<br clear=3D"none" class=
=3D"yiv8738275098" style=3D""><div class=3D"yiv8738275098" id=3D"yiv8738275=
098yqt69338" style=3D""><div class=3D"yiv8738275098" id=3D"yiv8738275098yui=
_3_16_0_1_1406255979191_403897" style=3D""><div class=3D"yiv8738275098" sty=
le=3D"">On Jul 29, 2014, at 08:37 , Hugh Allen <<a rel=3D"nofollow" shap=
e=3D"rect" class=3D"yiv8738275098" ymailto=3D"mailto:[email protected]=
om" target=3D"_blank" href=3D"mailto:[email protected]" style=3D"">h=
[email protected]</a>> wrote:</div><br clear=3D"none" class=3D"yiv=
8738275098" style=3D""><blockquote class=3D"yiv8738275098" id=3D"yiv8738275=
098yui_3_16_0_1_1406255979191_403896" type=3D"cite" style=3D"">Hi,<br clear=
=3D"none" class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yi=
v8738275098" style=3D"">Hope we have some Oracle/JPA experts out there, bec=
ause Google searches are not showing me the answer.<br clear=3D"none" class=
=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv8738275098" st=
yle=3D"">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 clear=3D"none" class=3D"yiv8738275=
098" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D"">Thoug=
ht this would be a snap but no such luck. Oracle OWNS Java,=0A for crying o=
ut loud. How hard could this be???<br clear=3D"none" class=3D"yiv8738275098=
" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D"">So I gue=
ss this is an Oracle newbie question.<br clear=3D"none" class=3D"yiv8738275=
098" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D"">1) Re=
solved the 30 character limit on column names by changing JPA<br clear=3D"n=
one" class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv8738=
275098" style=3D"">2) Resolved Oracle's inability to distinguish between NU=
LL 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 clear=3D"none" class=3D"yiv8738275098" style=3D""><br=
clear=3D"none" class=3D"yiv8738275098" style=3D"">3) But now *CASE SENSITI=
VE Queries* are the problem. By default all Oracle queries are Case Sensiti=
ve! This is a ridiculous default, and there must be some way around it???<b=
r clear=3D"none"
class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv87382750=
98" style=3D"">Discovered there is an Oracle setting for NLS_COMP but this =
is a=0A SESSION parameter and JPA can not control Oracle Sessions? How can =
this be accomplished?<br clear=3D"none" class=3D"yiv8738275098" style=3D"">=
<br clear=3D"none" class=3D"yiv8738275098" style=3D"">Guessing many of you =
use Oracle. Any help on setting up Case IN-SENSITIVE searches in Oracle?<br=
clear=3D"none" class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=
=3D"yiv8738275098" style=3D"">Without changing all the queries to use LOWER=
()?<br clear=3D"none" class=3D"yiv8738275098" style=3D""><br clear=3D"none"=
class=3D"yiv8738275098" style=3D"">Thanks, Hugh<br clear=3D"none" class=3D=
"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=
=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D""><br clear=3D"no=
ne" class=3D"yiv8738275098" style=3D""><br clear=3D"none" class=3D"yiv87382=
75098" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D"">___=
____________________________________________<br clear=3D"none" class=3D"yiv=
8738275098" style=3D"">Juglist mailing list<br clear=3D"none"
class=3D"yiv8738275098" style=3D""><a rel=3D"nofollow" shape=3D"rect" clas=
s=3D"yiv8738275098" ymailto=3D"mailto:[email protected]" target=3D"_=
blank" href=3D"mailto:[email protected]" style=3D"">[email protected]=
ijug.org</a><br clear=3D"none" class=3D"yiv8738275098" style=3D"">http://li=
sts.trijug.org/mailman/listinfo/juglist_lists.trijug.org<br clear=3D"none" =
class=3D"yiv8738275098" style=3D""></blockquote></div></div><br clear=3D"no=
ne" class=3D"yiv8738275098" style=3D""></div></div></blockquote></div></div=
></div></div><br clear=3D"none" class=3D"yiv8738275098" style=3D""><div cla=
ss=3D"yiv8738275098" id=3D"yiv8738275098yqt54843" style=3D"">______________=
_________________________________<br clear=3D"none" class=3D"yiv8738275098"=
style=3D"">Juglist mailing list<br clear=3D"none" class=3D"yiv8738275098" =
style=3D""><a rel=3D"nofollow" shape=3D"rect" class=3D"yiv8738275098" ymail=
to=3D"mailto:[email protected]" target=3D"_blank" href=3D"mailto:Jug=
[email protected]" style=3D"">[email protected]</a><br
clear=3D"none" class=3D"yiv8738275098" style=3D""><a rel=3D"nofollow" shap=
e=3D"rect" class=3D"yiv8738275098" target=3D"_blank" href=3D"http://lists.t=
rijug.org/mailman/listinfo/juglist_lists.trijug.org" style=3D"">http://list=
s.trijug.org/mailman/listinfo/juglist_lists.trijug.org</a><br clear=3D"none=
" class=3D"yiv8738275098" style=3D""></div><br clear=3D"none" class=3D"yiv8=
738275098" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D""=
></div></div> </div> </div>=0A </blockquote><div class=3D"yiv8738275098" st=
yle=3D""></div> </div></div></div><br clear=3D"none" class=3D"yiv87382750=
98" style=3D""><br clear=3D"none" class=3D"yiv8738275098" style=3D""></div>=
</div> </div> </blockquote><div></div> </div></div></div></div></div></b=
ody></html>
---185498972-337957796-1406656802=:73186--
--===============5010891236874574142==
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
--===============5010891236874574142==--