SQL*Plus error logging – workaround for ROLLBACK issue

In my previous post Oracle - SQL*Plus error logging , as a NOTE I stated an issue that whenever ROLLBACK is issued in the session, the feature fails to log the errors.
Thanks to Jacek Gebal for his blog “Oracle Thoughts”. I was really impressed by the workaround.

Workaround is simple but tricky.

Three simple steps :
1. Rename table SPERRORLOG to a new table.
1. Create a view as “SPERRORLOG”
2. Create a procedure with autonomous transaction.
3. Create an INSTEAD OF trigger to call above procedure. Instead of insert on SPERRORLOG, rather insert into the new table.

SQL> ALTER TABLE sperrorlog RENAME TO sperrorlog_new;
Table altered.

SQL> CREATE VIEW sperrorlog AS SELECT * FROM sperrorlog_new;

View created.

SQL> CREATE OR REPLACE PROCEDURE p_sperrorlog(
2       username VARCHAR2, timestamp TIMESTAMP, script VARCHAR2,
3       identifier VARCHAR2, message VARCHAR2, statement VARCHAR2
4 ) IS
5    PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7    INSERT INTO sperrorlog_new
8    VALUES (username, timestamp, script, identifier, message, statement);
9    COMMIT;
10 END;
11 /

Procedure created.

SQL> SHOW ERRORS;
No errors.

SQL> CREATE OR REPLACE TRIGGER r_sperrorlog
2       INSTEAD OF INSERT ON sperrorlog FOR EACH ROW
3    CALL p_sperrorlog(
4       :NEW.username,:NEW.timestamp, :NEW.script,
5       :NEW.identifier, :NEW.message, :NEW.statement)
6 /

Trigger created.

SQL> SHOW ERRORS;
No errors.

Let’s test it and see.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT

--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

SQL> ROLLBACK;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT

--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

It works! Now we could capture the errors even if ROLLBACK is issued.

WHEN OTHERS THEN NULL – A bug

A when others is almost always a BUG unless it is immediately followed by a RAISE.                    

– Quote from Thomas Kyte’s excellent thread on similar topic

Remember, for errors, RAISE –> CATCH –> HANDLE

The most common mistake that pl/sql developers often do is not handling the exceptions properly. Irrespective of the fact that the code is robust, having a weak exception handler could break the entire code and make the entire business logic prone to errors. We all know how painful it is to debug a huge piece of code just to find the line where the error occurs. Yes, it is time consuming and could be avoided most of the times if good coding practices are followed.

At first instance, why do we need an exception handler? To catch the errors, log them(optional), and finally do something about them. Isn’t it? If not, then why do we need the overhead of catching them and ultimately not doing anything?

In this post, I am restricting the scope to the ultimate bug in any pl/sql code, none other than “WHEN OTHERS THEN NULL”. Yes, it’s a bug waiting for its chance to break the atomicity of a procedural call.

Let’s see why.

A simple test case to show how WHEN OTHERS hides the actual error if not re-raised:

SQL> set serveroutput on;

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2           o_val NUMBER;
3        BEGIN
4           SELECT i_val INTO o_val FROM dual;
5           DBMS_OUTPUT.PUT_LINE(o_val);
6        EXCEPTION
7           WHEN OTHERS THEN
8              NULL;
9        END;
10      /
Procedure created

SQL> SHOW ERRORS;
No errors for PROCEDURE P_TEST_OTHERS

SQL> BEGIN
2          p_test_others('a');
3       END;
4       /
PL/SQL procedure successfully completed

So the above code did not throw any error and it says the procedure successfully completed. But, is it?

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10   END;
11   /
Procedure created

SQL>
SQL> BEGIN
2       p_test_others('a');
3    END;
4    /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
PL/SQL procedure successfully completed

So, we do see an error being captured. However, it is only as good as knowing that the error exists, but what needs to be done with it? If such a code is a part of any job, the job would simply complete successfully. Do you to proceed blindly with errors?

You need to re-raise the error.

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10         RAISE;
11    END;
12    /
Procedure created
SQL>
SQL> BEGIN
2          p_test_others('a');
3       END;
4       /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
BEGIN
p_test_others('a');
END;
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "P_TEST_OTHERS", line 10
ORA-06512: at line 2

So where to use WHEN OTHERS? Confined to the following cases :

• Error logging.
     when others
        then
        log_the_error(required_parameters);
        raise;
     end;
• To catch all other exceptions apart from the Named System Exceptions and Named Programmer-Defined Exceptions.
• Send an email regarding the error captured(Might be helpful during development stage or unit testing).
• To close all the opened resources such as Cursors, files etc.

ORA-06503: PL/SQL: Function returned without value

An important thing regarding function, you would agree with me that, at least once a PL/SQL developer must have heard that “A function MUST ALWAYS RETURN a VALUE of proper datatype”. Having been said that a million times on various platforms, still developers make this mistake.

ORA-06503: PL/SQL: Function returned without value
Cause: A call to PL/SQL function completed, but no RETURN statement was executed.
Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

DB version : 11.2.0.2.0

Let’s see the various scenarios of this error :

Without a RETURN statement in the function body and without exception handler(most stupid way):


SQL> set serveroutput on;

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(100) from dual;
select f_test(100) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 8

Now, in the above code, the mathematical logic was correct, hence there was no SQL error to override the PL/SQL error. Let’s see how ORA-01476 will override the ORA-06503 error.


SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 5

Well, that’s quite obvious, isn’t it?

2.  Without a RETURN statement in the exception handler(most common mistake) :


SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 NULL;
14 END;
15 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 14


—————————————-/ OFF TOPIC /—————————————-

This is somewhat important to share.

EXCEPTION WHEN OTHERS THEN NULL;
–> is itself a bug in the code waiting for its chance to break the code.

At least a good developer would remember that WHEN OTHERS should be always followed by a RAISE. Re-raising the error would show us the root cause, rather than the confusing “ORA-06503: PL/SQL: Function returned without value” error.


SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 NULL;
14 RAISE;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 14


—————————————-/ OFF TOPIC /—————————————-

Now let’s put a RETURN statement at required places and the code should work fine without any error :



SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Came inside Exception handler');
14 RETURN 0;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;

F_TEST(0)
---------
0

Came inside Exception handler

Bottom line is that :

• A function MUST ALWAYS RETURN a value of proper datatype, no matter from the body or exception.
• We must do something with the error not just return junk. We must RAISE/log error and handle it, do something about the error so that underlying process has no impact.
• Lastly, not to forget, EXCEPTION WHEN OTHERS THEN NULL; –> is itself a bug in the code waiting for its chance to break the code.

Oracle – Insert Comma separated string values into Table

This is a small demonstartion of how to insert the values of a comma separated string to a table as different rows. This is not something new, Tom Kyte has already demonstrated it lot of times in AskTom. However, I just want to keep it simple, just to insert values of a comma separated string into a table.

Let’s see the demo :


SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DROP TABLE TEST
2 /

Table dropped
SQL> CREATE TABLE TEST (COL1 VARCHAR2(10))
2 /

Table created
SQL> DECLARE
2 L_INPUT VARCHAR2(4000) := 'THIS,IS,A,COMMA,SEPARATED,STRING';
3 L_COUNT BINARY_INTEGER;
4 L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
5 BEGIN
6 DBMS_UTILITY.COMMA_TO_TABLE(LIST => REGEXP_REPLACE(L_INPUT,
7 '(^|,)',
8 '\1x'),
9 TABLEN => L_COUNT,
10 TAB => L_ARRAY);
11 DBMS_OUTPUT.PUT_LINE(L_COUNT);
12 FOR I IN 1 .. L_COUNT LOOP
13 DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) ||
14 ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
15 INSERT INTO TEST VALUES (SUBSTR(L_ARRAY(I), 2));
16 COMMIT;
17 END LOOP;
18 END;
19 /

6
Element 1 of array contains: THIS
Element 2 of array contains: IS
Element 3 of array contains: A
Element 4 of array contains: COMMA
Element 5 of array contains: SEPARATED
Element 6 of array contains: STRING

PL/SQL procedure successfully completed
SQL> SELECT * FROM TEST
2 /

COL1

THIS
IS
A
COMMA
SEPARATED
STRING

6 rows selected

SQL>

Hope it helps!

How to generate trace file – SQL Trace and TKPROF in Oracle

It is a frequently asked question in almost all the Oracle forums. There have had been numerous questions/posts regarding “But how to generate the trace file?” Well, it might seem a heck of a task, however, looking it step by step will make you understand that it is actually not that difficult.

Usually, database application developers do not have the required/necessary permissions/privileges to do all the steps that I will mention below. However, most of the steps could be done by application developer. A few steps will need a DBA privilege.

Let me take you through the steps :

Turning the tracing event ON with a proper level

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; 

The different levels of tracing event:-

0 – No trace. Like switching sql_trace off.

2 – The equivalent of regular sql_trace.

4 – The same as 2, but with the addition of bind variable values.

8 – The same as 2, but with the addition of wait events.

12 – The same as 2, but with both bind variable values and wait events.

Giving a proper name/number for the trace file identifier

 SQL> alter session set tracefile_identifier = 'test_plan1';

 Execute the SQL query for which you want the trace

 SELECT col1, col2…..FROM t1, t2…..WHERE…..;

 Turning the tracing event OFF

 SQL> alter session set events '10046 trace name context off';

 Finding the directory where the trace files are generated(this is the directory which is in “init.ora” file) 

SQL> SHOW PARAMETER user_dump_dest;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      /opt/oracle/diag/rdbms/lalit/trace

 Finding the trace file in the UNIX directory 

lalit@Orcl:DEV$ cd /opt/oracle/diag/rdbms/lalit/trace

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ ls -lrt *test_plan1.trc

-rw-r—–    1 oracle   dba         1076036 Jan 01 06:46 lalit_ora_30539942_test_plan1.trc

-rw-r—–    1 oracle   dba         1903274 Jan 01 06:51 lalit_ora_33030344_test_plan1.trc

So now we have the trace file, just give the trace file name to DBA to get the tkprof output and take read access on tkprof.out file

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ls -lrt *.out

-rw-r–r–    1 oracle   dba           17273 Jan 01 06:52 tkprof.out

 Analyze the tkprof.out file to find the issue with the SQL query.

From 10g onwards all the tracing options have been centralized into DBMS_MONITOR package. You can look for the package in docshttp://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm

Oracle - SQL*Plus error logging

SQL*Plus New feature release 11.1

One of the most important things that a developer does apart from just code development is, debugging. Isn't it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it's own user defined error logging table(s).

Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it's a really helpful feature, at least during the unit testing of the code.

I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :

NOTE : SQL*Plus error logging is set OFF by default. So, you need to "set errorlogging on" to use the SPERRORLOG table.

SP2 Error

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> truncate table sperrorlog;

Table truncated.

SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.27.29.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

ORA Error

SQL> truncate table sperrorlog;

Table truncated.

SQL> select * from dula;
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.36.08.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

select * from dula
ORA-00942: table or view does not exist

Like shown above, you can capture PLS errors too. 

If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -

truncate table sperrorlog;
selct * from dual;
select * from dula;

SQL> @D:\sperrorlog_test.sql;

Table truncated.

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.17.000000 AM

D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM

D:\sperrorlog_test.sql

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM

D:\sperrorlog_test.sql

select * from dula
ORA-00942: table or view does not exist

SQL>

Check Oracle documentation on SPERRORLOG.

In addition to above, if you want to be particularly specific about each session's error to be spooled into a file you could do this -

SQL> set errorlogging on identifier my_session_identifier

Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value "my_session_identifier". Now you just need to do this -

SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';

To spool the session specific errors into a file, just do this -

SQL> spool error.log
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
SQL> spool off

NOTE : Whenever ROLLBACK is issued in the session, the feature fails to log the errors. Let’s look at the issue.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
--------------------------------------------------------------------------------
SCRIPT

--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 01.23.48.000000 AM

ORA-01722: invalid number

SQL> rollback;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

no rows selected

Now that’s something which we don’t want to happen. If we use this feature as an automated error logging for any batch process or scheduled jobs, and if our program is designed to rollback the session if an error occurs, then this feature will never work for us. It will never capture the error once the session is rolled back. But we want to know what exactly caused the error.

Please visit SQL*Plus error logging – workaround for ROLLBACK issue to see the workaround for this issue.

Oracle - Case Insensitive Sorts & Compares

Oracle enhanced it’s case-insensitive sorts and compares feature with 10g Release 2. Version 11g has the following types of sorts:

• Binary sort
• Monolingual linguistic sort
• Multilingual linguistic sort

NLSSORT

NLSSORT returns the string of bytes used to sort char.
Both char and 'nlsparam' can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of RAW datatype.
The value of 'nlsparam' can have the form :
'NLS_SORT = sort'
where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparam', then this function uses the default sort sequence for your session. If you specify BINARY, then this function returns char.
If you specify 'nlsparam', then you can append to the linguistic sort name the suffix _ai to request an accent-insensitive sort or _ci to request a case-insensitive sort.
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

-------------------------------------------------------/ Test Cases /-------------------------------------------------------------
Sample data and table used for examples throughout the scope of the document:-
To get us started, here's a simple sample table with a few rows in it.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Connected as Lalit 

SQL> CREATE TABLE NAMES AS SELECT * FROM (
2 WITH DATA AS(
3 SELECT 'Lalit' NAME from dual UNION ALL
4 SELECT 'Srikanth' NAME from dual UNION ALL
5 SELECT 'Rahul' NAME from dual UNION ALL
6 SELECT 'Praveen' NAME from dual UNION ALL
7 SELECT 'Swetha' NAME from dual UNION ALL
8 SELECT 'Karthik' NAME from dual UNION ALL
9 SELECT 'Suresh' NAME from dual)
10 SELECT * from DATA);

Table created

SQL> select * from names;
NAME
--------
Lalit
Srikanth
Rahul
Praveen
Swetha
Karthik
Suresh

7 rows selected

The default: Binary sort

The default sort order is always binary, meaning that the characters are sorted in the order of their character number in the character set.

SQL> SHOW PARAMETER NLS_SORT;  NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY

SQL> SHOW PARAMETER NLS_COMP;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_comp string BINARY

So this is how the default sorting happens -

SQL> SELECT name, ascii(substr(name,1,1)) FROM names order by name;

NAME ASCII(SUBSTR(NAME,1,1))
-------- -----------------------
Karthik 75
Lalit 76
Praveen 80
Rahul 82
Srikanth 83
Suresh 83
Swetha 83
7 rows selected

SQL> SELECT name, ascii(name) FROM names order by name;

NAME ASCII(NAME)
-------- -----------
Karthik 75
Lalit 76
Praveen 80
Rahul 82
Srikanth 83
Suresh 83
Swetha 83

7 rows selected

What happens if the data is case-sensitive, the sorting of the characters changes as per the ASCII value returned. However, internally everything happens in binary format. Let’s see this happening when we alter the case sensitivity-

SQL> update names set name='kARTHIK' where name='Karthik';

1 row updated

SQL> SELECT name, ascii(name) FROM names order by NLSSORT(name,'NLS_SORT=BINARY');

NAME ASCII(NAME)
-------- -----------
Lalit 76
Praveen 80
Rahul 82
Srikanth 83
Suresh 83
Swetha 83
kARTHIK 107

7 rows selected

SQL> SELECT name, ascii(name) FROM names order by NLSSORT(name,'NLS_SORT=BINARY_CI');

NAME ASCII(NAME)
-------- -----------
kARTHIK 107
Lalit 76
Praveen 80
Rahul 82
Srikanth 83
Suresh 83
Swetha 83

7 rows selected

SQL> rollback;

Rollback complete

Let’s see the difference in the binary values when case-sensitivity matters-

SQL> select name, NLSSORT(name,'NLS_SORT=BINARY') BINARY ,NLSSORT(name,'NLS_SORT=BINARY_CI') BINARY_CI FROM names ORDER BY name;

NAME BINARY BINARY_CI
-------- ------------------- --------------------
Karthik 4B61727468696B00 6B61727468696B00
Lalit 4C616C697400 6C616C697400
Praveen 5072617665656E00 7072617665656E00
Rahul 526168756C00 726168756C00
Srikanth 5372696B616E746800 7372696B616E746800
Suresh 53757265736800 73757265736800
Swetha 53776574686100 73776574686100

7 rows selected

Now let’s see the use of these session parameters.

SQL> select * from names where name = 'Rahul';

NAME
--------
Rahul

SQL> select * from names where name = 'rahul'; --case does not match with actual data
NAME
--------
--no rows returned in this case

So, in such a case usually we tend to use UPPER/LOWER function on both sides of the operator.

SQL> select * from names where UPPER(NAME) = UPPER('rahul');

NAME
--------
Rahul

OR

SQL> select * from names where LOWER(NAME) = LOWER('RAHUL');

NAME
--------
Rahul

Now we got our output, but, the question is did we do it optimally?
What if the NAMES table is a MASTER TABLE of the national data for AADHAR CARD/VOTER ID. It will have billions of rows making the table size to some Gigabytes.
Usually, if the top SQLs have the name column in their predicate, then it is must to have a Balanced-tree index. Or even a bitmap-index if the mapping criteria fulfils for not null and unique conditions. In such a case, using UPPER or LOWER function will skip the Index scan and we hit a FTS case.

Let’s create a normal balance-tree index.

SQL> CREATE INDEX names_indx ON names(NAME);

Index created

Now let’s check the execution plan for the query using the case function (UPPER/LOWER).

SQL> explain plan for SELECT * FROM names WHERE UPPER(name)=UPPER('kArtHIk');

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4048250084
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NAMES | 1 | 6 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT(UPPER("NAME"),'nls_sort=''BINARY_CI''')=HEXTORAW('
6B61727468696B00') )
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected

So, we just ended up with a FTS. A very obvious solution that would strike in the mind is to create a function-based index.

SQL> create index names_fb_indx on names(upper(name));

Index created

SQL> alter session set nls_comp='BINARY';

Session altered

SQL> alter session set nls_sort='BINARY';

Session altered

SQL> explain plan for SELECT * FROM names WHERE UPPER(name)=UPPER('kArtHIk');

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3644882080
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| NAMES | 1 | 12 | 2 (0
|* 2 | INDEX RANGE SCAN | NAMES_FB_INDX | 1 | | 1 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='KARTHIK')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected

Although creating a virtual column in 11g and up would also suffice. But it is similar to a function-based index. But doing either of them is nothing but adding an overhead to the existing design. We need to maintain the indexes in an index table space. It will maintain the rowid, value of the data, but in the same case that of the original data in table.
So, let’s see for a better approach.

SQL> alter session set nls_comp='LINGUISTIC';

Session altered

SQL> alter session set nls_sort='BINARY_CI';

Session altered

SQL> SELECT * FROM names WHERE name='kArtHIk';

NAME
--------
Karthik

SQL> explain plan for SELECT * FROM names WHERE name='kArtHIk';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4048250084
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NAMES | 1 | 6 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6B61727
468696B00') )
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected

Everything seems fine. Task is achieved, but still we can tune it to next level.

Case Insensitive Indexes

A normal index uses the default sort order, by default, and so it's useless in a case-insensitive search - and Oracle won't use it. For large tables, the resulting full table scan can be quite a performance penalty. Fortunately, it's easy to create an index that uses a specific sort order. You simply create a function-based index that uses the NLSSORT function we saw above.

SQL> create index names_ci_indx on names(NLSSORT(name,'NLS_SORT=BINARY_CI'));

Index created

SQL> explain plan for SELECT * FROM names WHERE name='kArtHIk';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2561608627
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| NAMES | 1 | 45 | 2 (0
|* 2 | INDEX RANGE SCAN | NAMES_CI_INDX | 1 | | 1 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6B61727468696B0
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected