Getting error "ORA-54033" when calling a procedure

Question

I'm currently getting the following Oracle error when calling a procedure:

ORA-54033: column to be modified is used in a virtual column expression

How can I track down the root cause of this error?

Answer 1

To find the table and column names of all virtual columns in your database you can run the following query:

SELECT c.OWNER, c.TABLE_NAME, c.COLUMN_NAME
  FROM DBA_TAB_COLS c
  WHERE c.VIRTUAL_COLUMN = 'YES' AND
        c.OWNER NOT IN ('SYS', 'XDB')
  ORDER BY c.OWNER, c.TABLE_NAME, c.COLUMN_NAME;

And you can use the following script to dump the DDL of all tables in your database which contain virtual columns to DBMS_OUTPUT:

DECLARE
  lobDDL  CLOB;

  PROCEDURE dump_clob(aCLOB IN CLOB) IS
    nCLOB_length       NUMBER;
    nCLOB_offset       NUMBER := 1;
    nMax_chunk_size    NUMBER := 32767;
    strChunk           VARCHAR2(32767);
  BEGIN
    nCLOB_length := DBMS_LOB.GETLENGTH(aCLOB);

    WHILE nCLOB_offset <= nCLOB_length LOOP
      strChunk := DBMS_LOB.SUBSTR(aCLOB, nMax_chunk_size, nCLOB_offset);

      DBMS_OUTPUT.PUT(strChunk);

      nCLOB_offset := nCLOB_offset + LENGTH(strChunk);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(';');
  END dump_clob;
BEGIN
  FOR aRow IN (SELECT DISTINCT c.OWNER, c.TABLE_NAME
                 FROM DBA_TAB_COLS c
                 WHERE c.VIRTUAL_COLUMN = 'YES' AND
                       c.OWNER NOT IN ('SYS', 'XDB')
                 ORDER BY c.OWNER, c.TABLE_NAME)
  LOOP
    lobDDL := DBMS_METADATA.GET_DDL(object_type => 'TABLE',
                                    name        => aRow.TABLE_NAME,
                                    schema      => aRow.OWNER);

    dump_clob(lobDDL);
  END LOOP;
END;

Answer 2

I got the same error and fixed it by doing as below.

Get the hidden columns with its dependent columns using the below.

SELECT COLUMN_NAME, DATA_DEFAULT, HIDDEN_COLUMN
FROM   USER_TAB_COLS
WHERE  TABLE_NAME = 'YOUR_TABLE_NAME';

You should see something like this below:

SYS_STUMF_$2WEF286CDZ1WPC4V_F5    |SYS_OP_COMBINED_HASH("ID","FIRST_NAME","ANOTHER_COLUMN_NAME") | YES

Drop this hidden column by using the above column names used by it.

exec dbms_stats.drop_extended_stats(user, 'YOUR_TABLE_NAME', '("ID","FIRST_NAME","ANOTHER_COLUMN_NAME") ');

Now run your procedure or alter your columns.

alter table YOUR_TABLE_NAME modify (ID VARCHAR2(10));

Create the hidden column again:

exec dbms_stats.create_extended_stats(user, 'YOUR_TABLE_NAME', '("ID","FIRST_NAME","ANOTHER_COLUMN_NAME")');

Answer 3

I am just sharing my experience here with this issue.

My table has the below virtual column. There are other columns as well, and I am just adding the impacted columns here.

Create Table Virtual_test
(ColumnA varchar2(100),
CLEAN_ColumnA VARCHAR2(4000 BYTE) GENERATED ALWAYS
AS(GET_CLEAN_ColumnA(ColumnA)) VIRTUAL
);

Now when I want to modify the size of ColumnA:

Alter Table Virtual_test
MODIFY ColumnA varchar2(255);

ORA-54033: column to be modified is used in a virtual column expression.

exec dbms_stats.drop_extended_stats(USER,'Virtual_test','(GET_CLEAN_ColumnA(ColumnA))' );

Alter Table Virtual_test
MODIFY ColumnA varchar2(255);

One major mistake I did was this virtual column was not hidden and not system generated in my table.

The step dbms_stats.drop_extended_stats deleted my existing column and dbms_stats.create_extended_stats will create an invisible column with the system generated name.

So I think this won't work with a non-hidden column, and you may need to follow the below steps and drop the column.

Alter Table Virtual_test drop column CLEAN_ColumnA;

Alter Table Virtual_test
MODIFY ColumnA varchar2(255);


Alter Table Virtual_test add
CLEAN_ColumnA VARCHAR2(4000 BYTE) GENERATED ALWAYS
AS(GET_CLEAN_ColumnA(ColumnA)) VIRTUAL;

Answer 4

The virtual column names starting with 'SYS_ST%' are indeed belonging to extended statistics... and since Oracle 12c, the system is trying to recognize and create them automatically...

Just google for "Oracle 12c Automatic Column Group Detection".