Thursday , March 28 2024

Case Insensitive Queries in Oracle

 

The default is case sensitive, so the following query returns no lowercase. In this case, the returned result will contain missing data.

If the result of the query must return both upper and lower case records, the following changes must be made.

NLS_COMP = LINGUISTIC and NLS_SORT = BINARY_AI

If it is desired to be valid for all users, it can be changed as a parameter.

If you only want to apply to specific users, you can do this with the database logon trigger.

Change parameter for all users:

The database must then be restarted.

Database Logon Trigger for spesific user:

If you want it to be valid only for a user , you can do this with logon trigger as follows at the session level , which can only be valid during the session when the user logs in.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories