Getting Literal string error while creating Materialized View

Question

Table has a column with nvarchar datatype. This column store a data in this format: yyyy-mm-dd hh:mm:ss. And I need to fetch data in this format dd-mm-yyyy. So I am using this to_char(to_date(columnname,'yyyy-mm-dd hh:mm:ss'),'dd-mm-yyyy'). I am getting correct data while fetching from select statement but when I am creating Materialized view then its throwing an error "literals string does not matching format string".

Please advise on this.

Answer 1

Format mask you use is wrong; minutes are mi, not mm (that's month).

No : yyyy-mm-dd hh:mm:ss
Yes: yyyy-mm-dd hh:mi:ss
                   --

---

Apart from that: saying that query returns desired result, are you sure that it returned all rows? GUIs (SQL Developer, TOAD, ...) usually return the first 50, 100, 500 (or whichever number it might be) rows - not all of them.

Error you got suggests that NOT ALL rows in that table contain data in format you think they have (i.e. yyyy-mm-dd hh:mi:ss).

Here's example: this is source table; note that 2nd row doesn't contain data in format you specified:

SQL> create table test (id, col) as
  2    (select 1, '2024-03-28 08:32:13' from dual union all
  3     select 2, '15.01.2024 13:23:32'  from dual
  4    );

Table created.

SQL> select * From test;

        ID COL
---------- -------------------
         1 2024-03-28 08:32:13
         2 15.01.2024 13:23:32   --> this

Let's create a view:

SQL> create or replace view v_test as
  2    select id, to_date(col, 'yyyy-mm-dd hh24:mi:ss') col
  3    from test;

View created.

Can I fetch from it? Nope; error just as the one you got:

SQL> select * From v_test;
ERROR:
ORA-01861: literal does not match format string



no rows selected

SQL>

---

What to do? It depends on database you use. If it is 12c or newer (12.2; not sure for 12.1), apply on conversion error to to_date function. Otherwise, you'll have to sanitize source data and make sure that all rows contain values in that format. Then move on with creating the (materialized) view.

Answer 2

You should find strings with date format errors.

For example:

-- Create test table
create table test (id, col) as
      (select 1, '2024-03-28 08:32:13' from dual union all
       select 2, '15.01.2024 13:23:32'  from dual
     );

-- Create test MV for detecting incorrect date format
create materialized view test_mv as
select id, 
to_date(col DEFAULT '9999-12-31 00:00:00' ON CONVERSION ERROR, 'yyyy-mm-dd hh24:mi:ss') col_err
from test;

-- Check incorrect ID
select * 
from test_mv
join test on test.id=test_mv.id
where test_mv.col_err = to_date('9999-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
;

ON CONVERSION ERROR support from 12.2