PLS-00761: Program unit collation may only be USING_NLS_COMP

Error: During compiling of the procedure

SQL> alter session set default_collation = BINARY;

Session altered.

SQL> select SYS_CONTEXT('USERENV', 'SESSION_DEFAULT_COLLATION') from dual;

SYS_CONTEXT('USERENV','SESSION_DEFAULT_COLLATION')
--------------------------------------------------
BINARY

SQL> create procedure test_proc as begin null; end;
  2  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
0/0      PLS-00761: Program unit collation may only be USING_NLS_COMP

Cause: Change in the collation setting. Oracle suggests “The default collation of a procedure, function, package, type, or trigger must be USING_NLS_COMP. The default_collation_clause explicitly declares the default collation of a PL/SQL unit to be USING_NLS_COMP.”

Solution: While compiling/creating default collation setting should be USING_NLS_COMP for the procedure, function, package, type or trigger and use/execute after that with any other collation.

SQL> alter session set default_collation = USING_NLS_COMP;

Session altered.

SQL> create procedure test_proc1 as begin null; end;
  2  /

Procedure created.