From the Remedy KB. I'm not a DBA so I can't speak about 10g or ARS7.
Oracle 9i Release 2 supposedly has case-insensitivity features. Does Remedy work with these features?
ARS 5.1.2 6.0
Categories
| Administration | Database |
P R O B L E M
Oracle 9i Release 2 supposedly has case-insensitivity features. Does Remedy work with these features?
S O L U T I O N
WARNING: Most customers have found that setting this slows down the DB so much that what little it gains them in searching is not worth the performance hit. Note that this is not truly "case insensitivity" as it will only support the = (equal to) operator and not LIKE. In reality, this is modifying the default sorting for the session(s) created by arserverd.
In AR Server version 5.1.2, there is a new configuration
parameter to take advantage of the Oracle 9i Release 2 case insensitivity
feature.
To enable the functionality, add the following line to the ar.cfg file
found in the Remedy install directory in a sub folder called Conf:
Db-Case-Insensitive: T
After you've put this parameter in the ar.cfg file, you will have to stop
and restart the Remedy Action Request System Server service. Note:
the arsignal.exe utility will not work, AR Server must be restarted.
IMPORTANT NOTE:
The Oracle 9i Release 2 case insensitivity feature does not support the
use of LIKE in a SQL statement. This means that the feature will only work
in QBE queries if the QBE match setting for a field is set to "Equal".
"Leading" and "Anywhere" searches use the LIKE operator
and will not be case-insensitive.
Also note that Diary fields and character fields > 4000 characters cannot
be used with '='. This will obviously limit the scope of this
functionality to provide case-insensitive functionality throughout ARS.
You can use this feature with >,>=,<,<= from the Advanced Query
Bar. See the Additional Oracle Information below.
Additional Information:
Note the SQL log when starting AR Server. You will see the statements that
enable this functionality on Oracle:
SQL Trace Log -- ON
CONNECT ARAdmin
SELECT BANNER FROM V$VERSION
ALTER SESSION SET NLS_COMP=ANSI
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER
Additional Oracle Information:
Doc ID: Note:233390.1
Subject: How NLS_COMP and NLS_SORT parameters affects on the SQL operations
?
Creation Date: 24-MAR-2003
Last Revision Date: 24-MAR-2003
PURPOSE
To give awareness of the parameters NLS_COMP and NLS_SORT and SQL operations
DESCRIPTION :
Please note that the NLS_COMP & NLS_SORT parameters affect the follow
seven SQL operations only:
WHERE ( = , > , < etc.) , START WITH , IN/OUT , BETWEEN , CASE WHEN
, HAVING and ORDER BY.
All other SQL operators will compare in binary mode only. This behavior
is documented in the Sorting Your Linguistic Data inside the Oracle9i Database
white paper available on OTN.
Example :
Have a look at the following example, using NLS_COMP and NLS_SORT you can
see expected result with the operator "="
but not with the operator "like".
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
SQL> desc sk
Name Null? Type
----------------------------------------- --------
EMPNO NOT NULL NUMBER(5)
NAME VARCHAR2(15)
ADDRESS VARCHAR2(30)
ADDRESS1 VARCHAR2(10)
PIN NUMBER(6)
SQL> ALTER SESSION SET NLS_COMP=ANSI;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
Session altered.
SQL> select name from sk where name ='SHRIKANT';
NAME
---------------
SHRIKANT
shrikant
SQL> select name from sk where name ='shrikant';
NAME
---------------
SHRIKANT
shrikant
SQL> select name from sk where name like '%kant';
NAME
---------------
shrikant
SQL> select name from sk where name like '%KANT';
NAME
---------------
SHRIKANT
SQL>
| Creation Date: | 5/23/2003 11:56:27 AM | |
| Last Modified Date: | 02/27/2006 13:50:08 | |
| Review Date: | 6/4/2003 2:19:46 PM | |
| Author: | Dan Stolarik | |
| Document ID: | KM-000000012018 |
--
Tony Worthington
[EMAIL PROTECTED]
262-703-5911
| Albert Bihler <[EMAIL PROTECTED]>
Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 10/05/2006 07:12 AM
|
|
Michiel Beijen schrieb:
> ** This is always a thread that gets lots of results, I will chime in...
>
> Of course, you can run Oracle on Windows as well.
>
> The Oracle-is-case-sensitive-and-MSSQL-is-not is not true anymore, you
> can configure Oracle 9 or 10 as well to be case insensitive and in MSSQL
> you can choose either case sensitive or case insensitive collations to
> your liking.
I am very interested in that point. I know that there is a NLS_SORT
and NLS_COMP parameter in Oracle 10g. But I don't have enough
understanding of the database to do case insensitive searches using
ARS. Can you please point me towards the right direction?
Best regards,
Albert
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
__20060125_______________________This posting was submitted with HTML in it___
