Preserving Datatypes When Reusing Views in Oracle

Views are often used as building blocks for other database objects in SQL databases like Oracle. You might start by prototyping logic in a view, then later build a reporting view on top of it, or eventually turn the data into a physical table for performance or snapshot reasons.

When you create a table from a view or build a new view on top of an existing one, the same surprise often appears: the column data types are not what you expected. You run something simple like:

CREATE TABLE new_table AS
  SELECT * FROM my_view;

… and later discover that some columns have changed. A NUMBER column may have lost its precision or scale, character columns may be longer or shorter than expected, or calculated columns may end up with odd default datatypes.

This post explains how Oracle decides datatypes when you create tables or views from a view, what usually goes wrong, and how to avoid these problems by being explicit.

Why data types “change” when you create a table from a view

When you use CTAS (Create Table As Select), Oracle does not copy column definitions from the source view. Instead, it looks at each expression in the SELECT list and decides the datatype based on Oracle’s SQL expression rules. In other words, Oracle uses what the query returns, not what you intended.

Problems usually appear when a view contains expressions such as CASE, string concatenation, or arithmetic operations, functions like NVL, COALESCE, TO_CHAR, TRUNC, or ROUND, implicit datatype conversions between numbers, strings, and dates, constants such as NULL, 0, or 'X', or set operations like UNION and UNION ALL where the branches use different types. Oracle’s rules are consistent, but the resulting datatypes do not always match the schema you had in mind.

If a view is little more than a wrapper around base table columns, CTAS usually works fine. It is generally safe when the view selects columns directly, avoids expressions and implicit conversions, and does not use UNION or UNION ALL with mismatched types.

Using CAST to control datatypes

The most reliable way to force a specific datatype is to CAST each expression to the type you want.

For example, if your view calculates values and you want a stable and predictable table schema, you can do this:

CREATE TABLE sales_snapshot AS
SELECT
  CAST(order_id AS NUMBER(10))         AS order_id,
  CAST(customer_name AS VARCHAR2(100)) AS customer_name,
  CAST(order_date AS DATE)             AS order_date,
  CAST(amount AS NUMBER(12,2))         AS amount,
  CAST(status AS VARCHAR2(20))         AS status
FROM sales_v;

By casting the columns yourself, you remove any guesswork and prevent Oracle from choosing a datatype you did not intend.

When you should always cast

Some expressions are especially likely to cause datatype issues and should almost always be cast explicitly:

NULL columns in a view
SELECT NULL AS some_col ... becomes an untyped null. CTAS can’t infer a useful type unless you cast: CAST(NULL AS VARCHAR2(30)) AS some_col

NVL / COALESCE
These can promote a column to a different type depending on arguments: CAST(COALESCE(num_col, 0) AS NUMBER(10,2)) AS num_col

CASE expressions
All branches should be the same type, or Oracle will pick a “common type” that may surprise you: CAST( CASE WHEN flag = 'Y' THEN amount ELSE 0 END AS NUMBER(12,2) ) AS amount

Concatenation (||)
This always yields a character type; explicitly size it: CAST(first_name || ' ' || last_name AS VARCHAR2(500)) AS full_name

Date formatting
If you convert dates to strings in the view, CTAS will store strings. If you want DATE, don’t TO_CHAR in the view – or cast back (better: avoid the conversion).

What CTAS still does not copy

Even when the column datatypes are correct, CREATE TABLE AS SELECT does not copy everything. Primary keys, foreign keys, check constraints, indexes, triggers, grants, column comments, and default values are not included and must be recreated manually.

Avoiding datatype drift

Datatype problems in Oracle do not only happen when creating tables from views. They often start earlier, when one view is built on top of another and Oracle silently infers a slightly different datatype. That inferred datatype then carries forward into every downstream view or table.

By casting derived columns early and treating views as real schema objects rather than throwaway queries, you can prevent datatype drift and make sure that both views and tables behave exactly the way you expect.