Create Table DDL incorrect when adding blob column after a pre-existing virtual column

Toad for Oracle & DevOps Toolkit
1

TOAD 12.7.1.11. Oracle 11.2.0.4

Given: A pre-existing table with a virtual column showing as the last column in the table. I want to add a blob column.

The last column created on my pre-existing table, SIDR_MEDIA, is a virtual column (MEDIA_TYPE_CD_V). This column has been there for months. After adding a blob column thusly…

ALTER TABLE sidr_media ADD (SIDR_MEDIA_BLOB BLOB)

LOB (SIDR_MEDIA_BLOB) STORE AS SECUREFILE SIDR_MEDIA_BLOB (TABLESPACE SIDR_LOBS);

… Oracle responds that I have successfully created the column.

The Columns tab in Schema Browser indicates I have a new blob column.

Checking the Schema Browser Scripts tab, I review the CREATE TABLE DDL and note the following

CREATE TABLE SIDR_MEDIA (

…

MEDIA_TYPE_CD_V VARCHAR2(60 BYTE) Generated Always as (CAST(“RIMFIREDB”.“GETCODETXT”(‘MEDIA_TYPE_CID’,“MEDIA_TYPE_CID”) AS VARCHAR2 (60))),

SIDR_MEDIA_BLOB BLOB

)

XMLTYPE MEDIA_TYPE_CD_V STORE AS CLOB SIDR_MEDIA_BLOB (

TABLESPACE …)

Note that I now have an XMLTYPE “STORE AS CLOB” line for my virtual column and no LOB “STORE AS SECUREFILE” entry.

Anyone have any idea what is going on. Is this a TOAD DDL generation issue or is this an Oracle issue when adding a lob after a virtual column. I’ll research further on my end. I’m throwing it out here just in case someone has seen this before.

Thanks in advance.

2

Check DBMS_METADATA.GET_DDL to see if there is a discrepancy between Oracle and Toad.

3

I don’t think the virtual column has anything to do with the problem you are seeing.

Take a look at your database’s DB_SECUREFILE setting.

docs.oracle.com/…/initparams063.htm

I just tried this here and it worked as expected. First I added the VIRT_COL, then I added the A_BLOB col, and below is what I get in SB-Tables-Script.

CREATE TABLE JDORLON.DEPT

(

DEPTNO NUMBER(2),

DNAME VARCHAR2(14 BYTE),

LOC VARCHAR2(13 BYTE),

VIRT_COL NUMBER GENERATED ALWAYS AS (4),

A_BLOB BLOB

)

LOB (A_BLOB) STORE AS SECUREFILE (

TABLESPACE USERS

ENABLE STORAGE IN ROW

CHUNK 8192

NOCACHE

LOGGING

STORAGE (

INITIAL 104K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;