Skip to content

ORA-12008 ORA-06512 ORA-01722

Error:

Refreshing or creating view gives ORA-01722: invalid number

Executing the query “call emp.rfrsh_mv()” failed with the following error: “ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2952 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2370 ORA-01722: invalid number ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 85 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 245 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2352 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2908 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3191 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3221 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 15 ORA-06512: at “emp.rfrsh_mv”, line 35 ORA-06512: at “emp.rfrsh_mv”, line 35″. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Solution:

Try to run select query from materialized view outside and see if error is data related. in my case select query itself was returning ORA-01722: invalid number.

The error was that there was a conditional (case) statement that called the TO_NUMBER function against a column with a potential non numeric value ​. when it hit this non-numeric value, it threw the Oracle error. It had not before encountered this value so it had always worked. it was fixed by separating that specific conditional ( it was the second part of a conditional using the OR predicate) into 2 conditional statements so that the TO_NUMBER function would not be called against it if it had that particular non numeric value

No comments yet

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: