oracle-databaseoracle19cmaterialized-viewsvarchar2

create oracle materialized view with fields having varchar2(char)

I have a requirement to create a materialized view in oracle. The base table is already there and in the MV, i just have to extract only a set of fields. The base table is having VARCHAR2(CHAR) for the fields but when i create the MV, it considers the fields as VARCHAR2(BYTE). Any leads on how to keep the same datatype in MV? I tried with and without specifying the fields in DDL, but in both ways it didn't work. I am using Oracle 19c

Code:

CREATE MATERIALIZED VIEW MY_MV
as
select field1,field2,field3 from MY_TABLE;

Code:

CREATE MATERIALIZED VIEW MY_MV (field1, field2, field3)
as
select field1,field2,field3 from MY_TABLE;

One way was doing the cast(field_name as varchar2(Char)) but then for 50 fields i have to do the cast, Is it the default behaviour?

Solution

  • It shouldn't be changing the semantics from the base table. Are you perhaps pulling from a remote database?

    Try setting the default semantics in your session:

    alter session set nls_length_semantics=CHAR;

    then create your mview.