ORA-20199 From DDL Of Version Enabled Table With a VARCHAR2(CHAR) Primary Key (Doc ID 1314140.1)

Last updated on APRIL 18, 2011

Applies to:

Workspace Manager - Version: 10.2.0.5 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

On a multibyte characterset database, e.g. like AL32UTF8, Oracle Workspace Manage (OWM) version enabled tables with a primary key of VARCHAR2(CHAR) will not accept DLL changes:

SQL> CREATE TABLE OWM_DDL_TEST_CHAR (
 2   ID_COL VARCHAR2(17 CHAR),
 3   VALUE NUMBER(*,0),
 4   CONSTRAINT "PK_OWM_DDL_TEST_CHAR" PRIMARY KEY ("ID_COL") );

Table created.

SQL> EXECUTE DBMS_WM.ENABLEVERSIONING(table_name => 'OWM_DDL_TEST_CHAR', hist => 'VIEW_WO_OVERWRITE');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_WM.BEGINDDL(table_name => 'OWM_DDL_TEST_CHAR');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE OWM_DDL_TEST_CHAR_LTS MODIFY (VALUE NUMBER(*,0) NOT NULL);

Table altered.

SQL> EXECUTE DBMS_WM.COMMITDDL(table_name => 'OWM_DDL_TEST_CHAR');
BEGIN DBMS_WM.COMMITDDL(table_name => 'OWM_DDL_TEST_CHAR'); END;
*
ERROR at line 1:
ORA-20199: primary key columns cannot be added/dropped/modified/reordered for version enabled tables
ORA-06512: at "WMSYS.LT", line 12340
ORA-06512: at line 1

The same DDL works if the primary key is of VARCHAR2(BYTE) datatype:

SQL> CREATE TABLE OWM_DDL_TEST_BYTE (
 2   ID_COL VARCHAR2(17 BYTE),
 3   VALUE NUMBER(*,0),
 4   CONSTRAINT "PK_OWM_DDL_TEST_BYTE" PRIMARY KEY ("ID_COL") );

Table created.

SQL> EXECUTE DBMS_WM.ENABLEVERSIONING(table_name => 'OWM_DDL_TEST_BYTE', hist => 'VIEW_WO_OVERWRITE');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_WM.BEGINDDL(table_name => 'OWM_DDL_TEST_BYTE');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE OWM_DDL_TEST_BYTE_LTS MODIFY (VALUE NUMBER(*,0) NOT NULL);

Table altered.

SQL> EXECUTE DBMS_WM.COMMITDDL(table_name => 'OWM_DDL_TEST_BYTE');

PL/SQL procedure successfully completed.

Changes

This only occurs on a multibyte characterset database.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms