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
Last activity: Sep 21, 2023, 01:09 PM