Linguistic Sorting and String Searching is fetching incorrect data in oracle

Question

I have a table "Audit" in Oracle 11g database. The table has a column "Account_Name". The "Audit"."Account_name" column has the following data: "Guest", "feeder_1", "feeder_2", "feeder_3" etc.

I have created the following index on the column "Audit.Account_Name".

CREATE INDEX AUDIT_IX9_CI ON AUDIT (NLSSORT("ACCOUNT_NAME",'nls_sort=''BINARY_CI'''));

From JDBC code I am first executing the below statements and then executing the actual query to fetch the data from "Audit" table.

connection.prepareStatement("ALTER SESSION SET NLS_COMP=LINGUISTIC"); //line1

connection.prepareStatement("ALTER SESSION SET NLS_SORT=BINARY_CI");  // line2

//This is the code that gets executed to fetch the data

connection.prepareStatement("select account_name from audit where  (ACCOUNT_NAME  like  'f%' ) ");//line3

rs = pst.executeQuery();//line4

while(rs.next()){
     String accountName = rs.getString("account_name");
     System.out.println("account name "+accountName);
}

The above query is fetching all the data from the table including values like "Guest". I'm excepting the query should fetch only rows where Account_name starts with 'f', but looks like by NLS_SORT and NLS_COMP is fetching incorrect data.

I found out that if I comment "line2" it fetches the data correctly. Can anyone suggest why these options are not working properly.

I executed the query from JDBC code on the same connection object.

select PARAMETER, value from nls_session_parameters

NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CURRENCY = $
NLS_ISO_CURRENCY = AMERICA
NLS_NUMERIC_CHARACTERS = .,
NLS_CALENDAR = GREGORIAN
NLS_DATE_FORMAT = DD-MON-RR
NLS_DATE_LANGUAGE = AMERICAN
NLS_SORT = BINARY_CI
NLS_TIME_FORMAT = HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT = DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT = HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT = DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY = $
NLS_COMP = LINGUISTIC
NLS_LENGTH_SEMANTICS = BYTEs
NLS_NCHAR_CONV_EXCP = FALSE 

This issue is raised in production environment. So earlier I have written a standalone Java code using JDBC to check if I get the same issue as in production.

I have now written a Oracle procedure, in the procedure first the NLS_COMP and NLS_SORT parameters are set and then the search query is executed (used a CURSOR here) and later resetting NLS_SORT=BINARY. The results are same, I get the same issue.

explain plan for
  select account_name from audit where account_name like 'f%'


Plan hash value: 1078245361

----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |    10 |  2580 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| AUDIT_IX9     |    10 |  2580 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ACCOUNT_NAME" LIKE U'f%')
       filter("ACCOUNT_NAME" LIKE U'f%')

Note
-----
   - dynamic sampling used for this statement (level=2)

After executing the following query I got Character Set as "WE8MSWIN1252";

select value into char_set from nls_database_parameters where parameter = 'NLS_CHARACTERSET';