column size differing when querying from all_tab_columns

Question

We create a table as follows:

CREATE TABLE  "CHANGEWORKFLOWREQUEST" (
  "DATA" CLOB CONSTRAINT "ChangeWorkflowRequest_data" CHECK ("DATA" IS JSON FORMAT JSON) NOT NULL,
  "OPERATION" VARCHAR2(4000 char) NOT NULL,
  "MODELNAME" VARCHAR2(4000 char) NOT NULL,
  "MODELID" VARCHAR2(4000 char) NOT NULL,
  "STATUS" VARCHAR2(4000 char) default 'pending'  NOT NULL,
  "VERIFICATIONSTATUS" VARCHAR2(4000 char),
  "REMARKS" VARCHAR2(4000 char),
  "_VERIFIEDBY" VARCHAR2(4000 char),
  "_MODIFIERS" CLOB CONSTRAINT "ChangeWorkflowRequest_3245" CHECK ("_MODIFIERS" IS JSON FORMAT JSON),
  "CORRELATIONID" VARCHAR2(4000 char),
  "ID" varchar2(50) default sys_guid() not null,
  "WORKFLOWINSTANCEID" VARCHAR2(4000 char),
  "_OLDVERSION" VARCHAR2(256 char),
  "_VERSION" VARCHAR2(256 char) NOT NULL,
  "_NEWVERSION" VARCHAR2(256 char),
  "_PARENTVERSION" VARCHAR2(256 char),
  PRIMARY KEY("ID")
)  

Note: _OLDVERSION is defined as VARCHAR2(256 CHAR).

But when querying from all_tab_columns we are getting a slightly different result. Below is the query:

        SELECT column_name AS "column",
       data_type   AS "type",
       nullable    AS "nullable",
       data_length AS "dataLength",
       CASE char_used
         WHEN 'C' THEN 1
         WHEN 'B' THEN 0
         ELSE NULL
       END         AS "charUsed"
FROM   all_tab_columns
WHERE  owner = 'JOHN'
       AND table_name = 'CHANGEWORKFLOWREQUEST'; 

This is the data it has returned (in csv):

"column","type","nullable","dataLength","charUsed"
"_OLDVERSION","VARCHAR2","Y",1024,1
"_VERSION","VARCHAR2","N",1024,1
"_NEWVERSION","VARCHAR2","Y",1024,1
"_PARENTVERSION","VARCHAR2","Y",1024,1
"DATA","CLOB","N",4000,
"OPERATION","VARCHAR2","N",4000,1
"MODELNAME","VARCHAR2","N",4000,1
"MODELID","VARCHAR2","N",4000,1
"STATUS","VARCHAR2","N",4000,1
"VERIFICATIONSTATUS","VARCHAR2","Y",4000,1
"REMARKS","VARCHAR2","Y",4000,1
"_VERIFIEDBY","VARCHAR2","Y",4000,1
"_MODIFIERS","CLOB","Y",4000,
"CORRELATIONID","VARCHAR2","Y",4000,1
"ID","VARCHAR2","N",50,0
"WORKFLOWINSTANCEID","VARCHAR2","Y",4000,1

Note: _OLDVERSION has length 1024. The expected value here is 256.

Is the query meant to fetch this information incorrect or insufficient?

Answer 1

Works as designed, the problem is the expectation, not the result from dictionary.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_COLS.html

DATA_LENGTH | NUMBER | NOT NULL | Length of the column (in bytes)

A VARCHAR2(256 CHAR) column may take up to 1024 bytes with a multibyte characterset like AL32UTF8.

You may want to use CHAR_LENGTH instead of DATA_LENGTH.

The maximum amount of data a VARCHAR2 column can store without enabling "Extended Data Types" is 4000 bytes (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html). DATA_LENGTH will be 4000 for a VARCHAR2(3999 CHAR) or VARCHAR2(1000 CHAR) column, too.