Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

NLS_LANG client set to AL32UTF8 and abnormal select performance issue

399831Oct 26 2009 — edited Sep 8 2010
Hi,
I use a simple select statement via sqlplus and noticed when changing the NLS_LANG from WE8ISO8859P15 to AL32UTF8 the query runtime is 10x longer.


DB: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
NLS_CHARACTERSET: AL32UTF8


The table has 500.000 rows and some varchar2(32), number(10) and date columns.

The select statement has no where conditions and looks like:

select a, b, c, a_date, b_date, c_date from x

The NLS_LANG for the OCI client is the only change before I connect the instance via sqlplus to perform the query.

The runtime is abnormal and I have no idea since the client and server charset are both AL32UTF8 and normally no conversion should happen.

Many thanks in advance for a clue.

Best regards,
Andreas
This post has been answered by oradba on Oct 28 2009
Jump to Answer

Comments

Hi,

Welcome to OTN. The behavior you are noticing is expected. If you would like to display a custom exception/convertor, check out this doc.

http://docs.oracle.com/cd/E25178_01/web.1111/b31973/af_validate.htm#BABGIEDH

-Arun
SantoshVaza
Hi,
have an af:table with the filtering enabled
on corrosponding field add af:convertNumber tag like 
<af:inputText>
            <af:convertNumber/>
</af:inputText>
So instead of throwing exception it will prompt the user to add a numeric value..

Regards,
User794216-Oracle
Thanks for the replies.
I was aware of those "fixes" except that

- users need to be able to use < or > in the filter criteria, so restricting them to numbers is not a solution
- the custom implemenation is a lot of work for something I would expected ADF to deal with.

I don't get the number exception when I enter "> 100" or "< 100" so the < and > seem to pass succesfully.
But if by accident I enter "= 100" instead of "100" it throws the number exception
So I still feel this should be handled more gracefully out of the box.

Thanks, Ingrid
Here is a simple workaround that I make use of, might help!

Consider I have a bind variable searchString of String type being used in a view criteria. View criteria has a numeral attribute as well as text attribute to compare with :searchString. Now what I do to avoid the exception is that I create another bind variable called searchStringForInt of String type. I set the value of this bind variable as expression type "+searchString != null ? searchString.replaceAll("\\D+","") : null+" and use this :searchStringForInt for comparing with numeral attribute instead of :searchString.
Also FYI, QBE behaviour can be customized. Take a look at http://radio-weblogs.com/0118231/stories/2003/07/11/implementingAViewCriteriaAdapterToCustomizeQueryByExampleFunctionality.html.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 6 2010
Added on Oct 26 2009
#database-key-features, #globalization-support, #performance, #sql
9 comments
19,477 views