The 0RA-01400 error is reported when the materialized view is refreshed.

The 0RA-01400 error is reported when the materialized view is refreshed.

Background: materialized view is one of the three major tools for data warehouse refresh. In actual work, it is used for data extraction on heterogeneous platforms. Today, after creating a materialized view, manually refresh the materialized view, the error of ORA-12008 and ORA-01400 is reported, the error is as follows:

Troubleshooting:

1. Check the scripts of the materialized view. The following are the scripts that reference the relevant table:

SELECT "BillId ",

Nvl ("Status", null) as "Status ",

Nvl ("ChargeEmp", null) as "ChargeEmp ",

"ChargeDate"

FROM dbo. TBILLADJ @ ewms; // sqlserver data //

2. query the relevant SQL Server database tables

3. Checklist content

4. Cause: it is clear that the ChargeEmp limit is not empty, but it may be due to historical reasons that the null value is allowed during early settings.

However, when creating a materialized view, if there is no special description, the materialized view will impose corresponding restrictions based on the existing table;

So when refreshing, the data does not comply with the current materialized view specification, so the error of ORA-01400 is reported.

5. solution:

The Oracle function NVL is referenced here. The usage is as follows:

NULL indicates a NULL value or an invalid value.

NVL (expr1, expr2): If expr1 is NULL, expr2 is returned. If not NULL, expr1 is returned. Note that the two types must be consistent.

NVL2 (expr1, expr2, expr3): If expr1 is not NULL, expr2 is returned. If it is NULL, expr3 is returned. If the expr2 and expr3 types are different, expr3 will be converted to the expr2 type.

NULLIF (expr1, expr2): returns NULL for equality, returns expr1 for Equality

The detailed modification script is as follows:

SELECT "BillId ",

Nvl ("Status", null) as "Status ",

Nvl ("ChargeEmp", null) as "ChargeEmp ",

"ChargeDate"

FROM dbo. TBILLADJ @ ewms;

Conclusion: The process of problem handling is often a learning process, and the learning speed is usually faster than the learning speed from books;

Appendix: script for manual refresh of Materialized View:

Begin

Dbms_mView.Refresh ('mv _ name ');

End;

Oracle performance problems caused by improper definition of materialized views

Oracle materialized view test

Connection-based materialized views of Oracle [refresh regularly]

Oracle materialized view creation reports ORA-00942 error resolution

Refresh the materialized view of Oracle stored procedures

Use of Oracle Materialized View