Oracle – Using CAST() to NULL columns in materialized views

When building a materialized view on a prebuilt table, it is likely you will encounter ORA-12060: shape of prebuilt table does not match definition query at some point. Generally, two common causes for this error are:

  1. Forgetting the primary key on the prebuilt table for a materialized view using the “WITH PRIMARY KEY” clause
  2. A difference in data type or data length for a column, for example a VARCHAR2( 100 ) in the parent table mapped to a VARCHAR2( 50 ) column in the prebuilt table. This can also happen with NUMBERs that differ in precision and scale.

A very interesting case of this error was encountered when working with some more advanced functionality of materialized views. Materialized views can be created with columns that match their parent table, but are NULLed at refresh. This is handy when you want to maintain the same structure as the parent table, but not show any of the data. For example, if you want to make an updatable materialized view that can push inserted values for a column to its parent, but will clear out all data in that column upon refresh. The CAST() function can be used to accomplish this, but it commonly leads to the ORA-12060: shape of prebuilt table does not match definition query error. Here is an example of a materialized view on a prebuilt table consisting of two columns, one of which should be preserved in the parent table, but cleared in the materialized view:

SQL> CREATE TABLE source_tab( name VARCHAR2( 50 ) NOT NULL, id NUMBER NOT NULL );
Table created.

SQL> CREATE TABLE mat_view( name VARCHAR2( 50 ) NOT NULL, id NUMBER );
Table created.

SQL> CREATE MATERIALIZED VIEW mat_view ON PREBUILT TABLE AS SELECT name, CAST( NULL AS NUMBER ) id FROM source_tab;
CREATE MATERIALIZED VIEW mat_view ON PREBUILT TABLE AS SELECT name, CAST( NULL AS NUMBER ) id FROM source_tab
                                                                    *
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query

This error originates from the id column, but notice that both the parent table and materialized view have the same NUMBER datatype. When no precision and scale are specified for a number, it is actually treated as NULL(*), not as a default precision and scale as one might expect. When introducing the CAST() function to the materialized view, the lack of a consistent, specified precision and scale triggers this error. This can be solved by either of the following:

  1. Use the “WITH REDUCED PRECISION” clause
  2. Specify a consistent precision and scale in the parent table, prebuilt table, and for the CAST() function in the materialized view

The “WITH REDUCED PRECISION” clause is a good place to start since it should be sufficient in most cases:

SQL> CREATE MATERIALIZED VIEW mat_view ON PREBUILT TABLE WITH REDUCED PRECISION AS SELECT name, CAST( NULL AS NUMBER ) id FROM source_tab;
Materialized view created.

The second option, although more complicated, helps to ensure no data is lost between the parent table and materialized view. The following example requires rebuilding the parent table, specifying the precision and scale:

SQL> CREATE TABLE source_tab( name VARCHAR2( 50 ) NOT NULL, id NUMBER( 38, 0 ) NOT NULL );
Table created.

SQL> CREATE TABLE mat_view( name VARCHAR2( 50 ) NOT NULL, id NUMBER( 38, 0 ) );
Table created.

SQL> CREATE MATERIALIZED VIEW mat_view ON PREBUILT TABLE AS SELECT name, CAST( NULL AS NUMBER( 38, 0 ) ) id FROM source_tab;
Materialized view created.

Here is a result of NULLed columns using CAST():

SQL> SELECT *
  2  FROM source_tab;

NAME               ID
---------- ----------
BOB                12
JAMES              38
SARAH              34
NATASHA            94

SQL> EXEC DBMS_SNAPSHOT.REFRESH( 'mat_view', 'C' );
PL/SQL procedure successfully completed.

SQL> SELECT *
  2  FROM mat_view;

NAME               ID
---------- ----------
BOB
JAMES
SARAH
NATASHA
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s