# Case insensitive search  with Oracle

**URL:** <https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393>\
**Category:** Classic Servoy\
**Created:** [April 25, 2007, 9:10am UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393 "2007-04-25T09:10:46Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [April 25, 2007, 9:10am UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/1 "2007-04-25T09:10:46Z")

</div>

In Oracle 10g R2 i can do case-insensitive querys by setting two variables :

- NLS\_COMP=LINGUISTIC
- NLS\_SORT=BINARY\_CI

if i set these ( in database and/or environment ) i can do a case - insensitive query :

SQL\> select \* from testcase where k1 = ‘ABCDF’;

## K1

AbCdF

SQL\> select \* from testcase where k1 like ‘ABC%’;

## K1

AbCdF

When i start a default (Oracle) sqlplus session this works fine.  
Servoy does not seem to take notice of (or overrules) these settings.

How can i make sure that Servoy makes use of these settings so i can use case insensitive search for Servoy running on Oracle ??

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [April 25, 2007, 9:25am UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/2 "2007-04-25T09:25:17Z")

</div>

I don’t think Servoy overrides anything. It should be a matter of the JDBC driver.

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [April 25, 2007, 9:27am UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/3 "2007-04-25T09:27:01Z")

</div>

You could also use the # in front of your search criteria, that should make a Servoy find being case insensitive.

---

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [April 25, 2007, 9:46am UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/4 "2007-04-25T09:46:04Z")

</div>

Thanks for your reply.

I know how to use the # character, but i want to make as much use of the  
Oracle database features as i can.  
Also, when i can use this, the user (or a method) does not have to add the # sign.  
This should be transparent to an application (it is in Sqlplus)

I like to split the database and application functions as much as possible.

Oracle will also use a more sensible/better performing execution plan

Maybe Servoy can answer this one ??

Regards,

Hans

---

<div class="post-metadata">

**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\
**Post date:** [April 25, 2007, 3:12pm UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/5 "2007-04-25T15:12:00Z")

</div>

Have a look at the statement fired at the database through the admin pages. If you’re not doing anything yet to modify the search, you’ll likely find a statement like "select X from Y where Z = ‘…’

This is send to the DB through JDBC. If Oracle’s JDBC driver then given back the results without looking at your environment settings, it’s not something Servoy can do about.

There might be an Oracle JDBC driver around that does support these environment variables properly.

Paul

---

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [April 25, 2007, 7:05pm UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/6 "2007-04-25T19:05:15Z")

</div>

Oke,

I tested a litle java program :

import java.sql._;  
import oracle.jdbc.driver._;

class jdbctest  
{  
public static void main (String args )  
throws SQLException  
{

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection  
(“jdbc:oracle:thin:@localhost:1521:xe”,“bis1”,“bis1”);

DatabaseMetaData meta = conn.getMetaData ();

Statement stmt = conn.createStatement();  
ResultSet rs = stmt.executeQuery  
(“SELECT k1 from testcase where k1 like ‘ABC%’”);

System.out.println(“Class is SelectFromPer\n”);

System.out.println(“Found row:”);

while (rs.next()) {

String k1text = rs.getString(1);

System.out.print (" K1=" + k1text);  
System.out.print(" \n");  
}

stmt.close();  
conn.close();  
}  
}

The case-insensitive search did not work with it .

I checked with Oracle and they said that the NLS parameters are not read when using thin jdbc.

So i tryed writing a database logon trigger for the user that connects from this test program and from Servoy :

create or replace trigger set\_nls\_onlogon  
AFTER LOGON ON DATABASE  
DECLARE  
cmmd1 VARCHAR2(100);  
cmmd2 VARCHAR2(100);  
BEGIN  
cmmd1:=‘ALTER SESSION SET NLS\_SORT=BINARY\_CI’;  
cmmd2:=‘ALTER SESSION SET NLS\_COMP=LINGUISTIC’;  
if (user in (‘BIS1’,‘BERP’)) then  
EXECUTE IMMEDIATE cmmd1;  
EXECUTE IMMEDIATE cmmd2;  
end if;  
END set\_nlslogon;

This works fine both with the test program and also when using Servoy !!!

This is a great way to do case insensitive searching in the Servoy-Oralcle environment.

Regards and thanks for the replys.

Hans Nieuwenhuis

---

<div class="post-metadata">

**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\
**Post date:** [April 26, 2007, 9:03am UTC](https://forum.servoy.com/t/case-insensitive-search-with-oracle/7393/7 "2007-04-26T09:03:24Z")

</div>

Nice tip!

Tnx