virtual column looses details in export-user-objects or edit-table or compare-user-objects

[Bernhard S]

I create a table containing virtual columns:

create table ta_virt_col
(
  a number(10)
, b number(10) generated always as ( cast( 10*a as number(9) ) )
, c            generated always as ( cast(100*a as number(9) ) )
);

When I edit this table or do an export-user-objects of this table in PL/SQL Developer it gets changed to:

create table TA_VIRT_COL
(
  a NUMBER(10),
  b as (CAST(10*"A" AS number(9) )),
  c as (CAST(100*"A" AS number(9) ))
);

This is not the same however. I create the 2nd table in another schema in order to compare both. There's a difference in the data precision of virtual column B:

SQL> select tc.owner, tc.table_name, tc.column_name, tc.data_length, tc.data_precision, tc.data_scale, tc.column_id
  2    from all_tab_cols tc
  3   where tc.owner      like 'SCOTT%'
  4     and tc.table_name like 'TA_VIRT_COL%'
  5   order by tc.column_id, tc.owner, tc.table_name
  6  ;
OWNER    TABLE_NAME      COLUMN_NAME  DATA_LENGTH DATA_PRECISION DATA_SCALE  COLUMN_ID
-------- --------------- ------------ ----------- -------------- ---------- ----------
SCOTT    TA_VIRT_COL     A                     22             10          0          1
SCOTT02  TA_VIRT_COL     A                     22             10          0          1
SCOTT    TA_VIRT_COL     B                     22             10          0          2
SCOTT02  TA_VIRT_COL     B                     22              9          0          2
SCOTT    TA_VIRT_COL     C                     22              9          0          3
SCOTT02  TA_VIRT_COL     C                     22              9          0          3
6 rows selected

SQL>

When I compare both tables using Compare-User-Objects no differences are shown either, which is not correct!

When I view the ddl via dbms_metadata of the first table ignoring storage parameters I get:

CREATE TABLE "SCOTT"."TA_VIRT_COL"
   (	"A" NUMBER(10,0),
	"B" NUMBER(10,0) GENERATED ALWAYS AS (CAST(10*"A" AS number(9) )) VIRTUAL VISIBLE ,
	"C" NUMBER(9,0) GENERATED ALWAYS AS (CAST(100*"A" AS number(9) )) VIRTUAL VISIBLE
   );

For the second table I get:

CREATE TABLE "SCOTT02"."TA_VIRT_COL"
   (	"A" NUMBER(10,0),
	"B" NUMBER(9,0) GENERATED ALWAYS AS (CAST(10*"A" AS number(9) )) VIRTUAL VISIBLE ,
	"C" NUMBER(9,0) GENERATED ALWAYS AS (CAST(100*"A" AS number(9) )) VIRTUAL VISIBLE
   );

The equivalent of this should be generated by PL/SQL Developer as well.

The difference can also be seen in a command window:

SQL> desc scott.ta_virt_col
Name Type       Nullable Default                     Comments
---- ---------- -------- --------------------------- --------
A    NUMBER(10) Y
B    NUMBER(10) Y        CAST(10*"A" AS number(9) )
C    NUMBER(9)  Y        CAST(100*"A" AS number(9) )

SQL> desc scott02.ta_virt_col
Name Type       Nullable Default                     Comments
---- ---------- -------- --------------------------- --------
A    NUMBER(10) Y
B    NUMBER(9)  Y        CAST(10*"A" AS number(9) )
C    NUMBER(9)  Y        CAST(100*"A" AS number(9) )

SQL>

 

[Bernhard S]

A not-null-constraint on a virtual column is ignored by PL/SQL Developer just the same way.
I now create one of my virtual columns with a not-null-constraint:

create table ta_virt_col_nn
(
  a number(10)
, b generated always as ( cast( 10*a as number(10) ) )     null
, c generated always as ( cast(100*a as number(10) ) ) not null
);

When I edit this table or do an export-user-objects of this table in PL/SQL Developer it gets changed to:

create table TA_VIRT_COL_NN
(
  a NUMBER(10),
  b as (CAST(10*"A" AS number(10) )),
  c as (CAST(100*"A" AS number(10) ))
);

This is not the same however. I create the 2nd table in another schema in order to compare both again. There's a difference in the nullability of virtual column C:

SQL> select tc.owner, tc.table_name, tc.column_name, tc.column_id, tc.nullable
  2     --, tc.data_length, tc.data_precision, tc.data_scale
  3    from all_tab_cols tc
  4   where tc.owner      like 'SCOTT%'
  5     and tc.table_name like 'TA_VIRT_COL_NN'
  6   order by tc.column_id, tc.owner, tc.table_name
  7  ;
OWNER    TABLE_NAME      COLUMN_NAME   COLUMN_ID NULLABLE
-------- --------------- ------------ ---------- --------
SCOTT    TA_VIRT_COL_NN  A                     1 Y
SCOTT02  TA_VIRT_COL_NN  A                     1 Y
SCOTT    TA_VIRT_COL_NN  B                     2 Y
SCOTT02  TA_VIRT_COL_NN  B                     2 Y
SCOTT    TA_VIRT_COL_NN  C                     3 N
SCOTT02  TA_VIRT_COL_NN  C                     3 Y
6 rows selected

SQL>

When I compare both tables using Compare-User-Objects no differences are shown either, which is not correct!

When I view the ddl via dbms_metadata of the first table ignoring storage parameters I get:

CREATE TABLE "SCOTT"."TA_VIRT_COL_NN"
   (	"A" NUMBER(10,0),
	"B" NUMBER(10,0) GENERATED ALWAYS AS (CAST(10*"A" AS number(10) )) VIRTUAL VISIBLE ,
	"C" NUMBER(10,0) GENERATED ALWAYS AS (CAST(100*"A" AS number(10) )) VIRTUAL VISIBLE  NOT NULL ENABLE
   );

For the second table I get:

CREATE TABLE "SCOTT02"."TA_VIRT_COL_NN"
   (	"A" NUMBER(10,0),
	"B" NUMBER(10,0) GENERATED ALWAYS AS (CAST(10*"A" AS number(10) )) VIRTUAL VISIBLE ,
	"C" NUMBER(10,0) GENERATED ALWAYS AS (CAST(100*"A" AS number(10) )) VIRTUAL VISIBLE
   );

The equivalent of this should be generated by PL/SQL Developer as well.

The difference can also be seen in a command window:

SQL> desc scott.ta_virt_col_nn
Name Type       Nullable Default                      Comments
---- ---------- -------- ---------------------------- --------
A    NUMBER(10) Y
B    NUMBER(10) Y        CAST(10*"A" AS number(10) )
C    NUMBER(10)          CAST(100*"A" AS number(10) )

SQL> desc scott02.ta_virt_col_nn
Name Type       Nullable Default                      Comments
---- ---------- -------- ---------------------------- --------
A    NUMBER(10) Y
B    NUMBER(10) Y        CAST(10*"A" AS number(10) )
C    NUMBER(10) Y        CAST(100*"A" AS number(10) )

SQL>

 

[Marco Kalter]

We'll check it out.