3

There is a difference in behavior between these two variants to create a view with a null number column and then a table from that view: One fails and one succeeds.

At the same time there is no visible difference in the data dictionary between these two variants. Tested in Oracle 19, but this issue has been present since the introduction of the Cast operator long before, maybe since Oracle 8:

Variant 1 using To_Number (Null) produces ORA-01723:

Create or Replace View JV as Select To_Number (Null) NullColumn From Dual Where 7=8;
Select * From JV;
-- no rows selected

Create Table JT as Select * From JV;
-- ORA-01723: zero-length columns are not allowed

Select Data_Length, Char_Length From User_Tab_Columns Where Column_Name=Upper ('NullColumn');
Len CHAR_LENGTH
--- -----------
 22           0

Variant 2 using Cast (Null as Number) works:

Create or Replace View JV as Select Cast (Null as Number) NullColumn From Dual Where 7=8;
Select * From JV;
-- no rows selected

Create Table JT as Select * From JV;
-- Table created.

Select Data_Length, Char_Length From User_Tab_Columns Where Column_Name=Upper ('NullColumn');
-- same as above (and no difference in the other columns not shown here for clarity)

So where is the difference in the data dictionary that produces the error ?
(Because there are no rows returned, the difference cannot be in the data itself as there is none, so it must be in the metadata inside the data dictionary.)

2
  • TO_NUMBER converts ONLY the value , If NULL is specified for string-expression, TO_NUMBER returns null . CAST can convert both the value AND the metadata. With TO_NUMBER , NULL is specified for a column, but the type is not specified so the table can't be created. cast. cast vs to_number Dec 7, 2022 at 18:53
  • @ErgestBasha The type IS specified and visible in the User_Tab_Columns.Data_Type data dictionary column as NUMBER. This means unconstrained number, which is expessed as Data_Length=22 in both cases.
    – Juergen
    Dec 9, 2022 at 9:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.