CTAS disregards number precision and scale

When using a "create table as select" (CTAS) to create a table based on a query, when that query also contains a window function/analytical function in the SELECT-clause, results in the precision and scale being omitted.

The precision/scale are neglected, when running the following example in Oracle 11g:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(1 AS NUMERIC(10,2)) AS FLD1,
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
FROM DUAL;

DROP TABLE X;

CREATE TABLE X AS SELECT * FROM VSA_X;

Table X will have no precision/scale for FLD1. It is bypassed and will simply have NUMBER for its datatype. This is a bug in Oracle.

Note:

• Using NUMBER in stead of NUMERIC makes no difference, the bug is still there.
• Explicitly casting the ROW_NUMBER() function also makes no difference.

Solution:

Put the window function inside a derived table:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(FLD1 AS NUMERIC(10,2)) AS FLD1,
RN
FROM
(
  SELECT
  1 AS FLD1,
  ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
  FROM DUAL
) x;

Keywords: CTAS omits precision, CTAS omits number precision, CTAS bypasses precision, CTAS bypasses number precision.