Default Collation and PL/SQL
PLS-00761: Program unit collation may only be USING_NLS_COMP
One of the new features that got introduced in Oracle Database 12.2 is the possibility to define the default collation at schema-, table- or column-level. This is quite nice because we do not have to use NLS_SORT in each and every query anymore.
SYS@GMITESTDB> alter FUNCTION USER_VV.GET_DW_LOANER_STATUS compile;
Warning: Function altered with compilation errors.
SYS@GMITESTDB> show error
Errors for FUNCTION USER_VV.GET_DW_LOANER_STATUS:
LINE/COL ERROR
——– —————————————————————–
0/0 PL/SQL: Compilation unit analysis terminated
0/0 PLS-00761: Program unit collation may only be USING_NLS_COMP
SYS@GMITESTDB> conn USER_VV/Tpwv6XXX
USER_VV@GMITESTDB> select default_collation from user_users;
DEFAULT_COLLATION
——————————–
BINARY_AI
let’s try the same with the user that has a different default collation.
USER_VV@GMITESTDB> alter session set default_collation=USING_NLS_COMP;
Session altered.
USER_VV@GMITESTDB> commit;
Commit complete.
USER_VV@GMITESTDB> alter FUNCTION GLOBUS_DW.GET_DW_LOANER_STATUS compile;
Function altered.
USER_VV@GMITESTDB>
Recent Posts
