set cursor_sharing=force caused the app break

[tamilselvan]

cursor_sharing = force

will not work always as you expected because the lengths of columns returned to the application will vary. For example, the query expected varchar2(5) but oracle may return 30 characters. That's why your application failed.

You should not use "FORCE" in your prod env.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select substr(object_name,1,4) xxx from dba_objects where rownum < 3;

XXX
--------------------------------------------------------------------------------
/100
/100

SQL> alter session set cursor_sharing=exact ;

Session altered.

SQL> select substr(object_name,1,4) xxx from dba_objects where rownum < 3;

XXX
----
/100
/100 

Tamil