PLS-00761: Program unit collation may only be USING_NLS_COMP

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.