英文:
Missing default value of varchar2(4000) column in DBMS_METADATA.GET_DDL （oracle 11g）
问题
Step 1 - 创建一个类似这样的表格：
CREATE TABLE "TEST1"."JWTEST"
(
C0 NUMBER(38) not null PRIMARY KEY,
C1 VARCHAR2(4000) DEFAULT 'abcd...(总共4000个字符)...abcd',
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
);
Step 2 - 获取表格的DDL：
SELECT DBMS_METADATA.GET_DDL('TABLE', 'JWTEST', 'TEST1') FROM DUAL;
我得到了以下内容：
CREATE TABLE "TEST1"."jwtest2"
( "C0" NUMBER(38,0) NOT NULL ENABLE,
"C1" VARCHAR2(4000) DEFAULT ,
PRIMARY KEY ("C0")
USING INDEX ENABLE
) ;
正如您所看到的，"C1" 列的默认值已经消失了。这是什么原因造成的？
英文:
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?
答案1
得分: 1
查询使用默认的3998个字符工作；使用默认的3999或4000个字符时不起作用。
一个合理的猜测是DDL语句在过程中生成，在过程中有一个本地变量，将default值存储为VARCHAR2(4000)，它还会存储文字周围的引号；这意味着引号占用了2个字符，如果default加上周围的引号超过4000个字节，它将引发异常，并且默认值将从生成的语句中省略。
你可以：
- 将你的
default限制为3998个字符；或者 - 在你的支持合同下向Oracle提出工单，并告知他们这个（潜在的）错误，并要求解决方案。
英文:
> 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:
- Limit your
defaultto 3998 characters; or - Raise a ticket with Oracle, under your support contract, and inform them of the (potential) bug and ask for a solution.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论