step 1 - create a table like this:
CREATE TABLE "TEST1"."JWTEST"
(
C0 NUMBER(38) not null PRIMARY KEY,
C1 VARCHAR2(4000) DEFAULT 'abcd...(total 4000 characters)...abcd',
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
);
step 2 - get ddl of the table
SELECT DBMS_METADATA.GET_DDL('TABLE', 'JWTEST', 'TEST1') FROM DUAL;
and I got this:
CREATE TABLE "TEST1"."jwtest2"
( "C0" NUMBER(38,0) NOT NULL ENABLE,
"C1" VARCHAR2(4000) DEFAULT ,
PRIMARY KEY ("C0")
USING INDEX ENABLE
) ;
As you can see, the default value for the "C1" column has disappeared...What caused it?
What caused it?
The query works (fiddle) with a default 3998 characters long; it does not work with a default 3999 or 4000 characters long.
An educated guess is that the DDL statement is being generated in the procedure and, within the procedure, there is a local variable that stores the default value that is defined as a VARCHAR2(4000) and it is also storing the quotes around the literal; this means that 2 characters are taken up by the quotes and if the default plus surrounding quotes exceeds 4000 bytes then it will raise an exception and the default is omitted from the DDL from the generated statement.
You could:
default to 3998 characters; or