TOAD 126.96.36.199. Oracle 188.8.131.52
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))),
XMLTYPE MEDIA_TYPE_CD_V STORE AS CLOB SIDR_MEDIA_BLOB (
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.