Sample Header Ad - 728x90

column size differing when querying from all_tab_columns

0 votes
1 answer
267 views
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?
Asked by deostroll (189 rep)
Sep 20, 2023, 02:31 PM
Last activity: Sep 21, 2023, 01:09 PM