Home » SQL & PL/SQL » SQL & PL/SQL » Is there a way to create a materialized view with datatype and length?
Is there a way to create a materialized view with datatype and length? [message #685008] Fri, 08 October 2021 08:00 Go to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
In 19c in the Oracle Cloud, we have some complex MV that are creating columns with a VARCHAR2(100 BYTE) while the underlying column in the query is a VARCHAR2(100 CHAR). This is sometimes causing a failure in the refresh because of multibyte characters in the table when we are close to that 100 length.

Dropping and recreating the MV fixes this because Oracle is smart enough to see the data in the table now and makes the MV with VARCHAR2(400 CHAR), but I would really like for it to create it with a VARCHAR2(100 CHAR).

I can alter the MV to what I want it to be then recompile, but that is a fix AFTER it happens. I'd like to be proactive rather than reactive.
Re: Is there a way to create a materialized view with datatype and length? [message #685009 is a reply to message #685008] Fri, 08 October 2021 08:09 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ok, I don't know why I didn't think of this sooner, but I just tested creating an MV with CASTing the column as VARCHAR2(100 CHAR) and that worked. I guess my brain works best in the morning and when I was trying to figure this out, it was afternoon.
Re: Is there a way to create a materialized view with datatype and length? [message #685010 is a reply to message #685009] Fri, 08 October 2021 09:30 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Actually, that did not work. Yeah a test worked, but when I tried it on the actual SELECT, it did not. I even removed MV out of the equation and just tried to do a CTAS and it did not work.
...
But then, I found the issue. I was doing a UNION ALL in my SELECT, and the second SELECT was hardcoding a string. Once I CAST that to VARCHAR2(100 CHAR), it worked, even with the MV creation.

So, I guess this was more of a lesson now that I have answered my own question.
Re: Is there a way to create a materialized view with datatype and length? [message #685011 is a reply to message #685010] Fri, 08 October 2021 12:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
What is remote side character set?

SY.
Re: Is there a way to create a materialized view with datatype and length? [message #685012 is a reply to message #685011] Fri, 08 October 2021 13:06 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
remote is 11.2.0.4 WE8MSWIN1252. Local is 19c AL32UTF8.
But even when table is local, the underlying table is VARCHAR2(100 CHAR), but the MV or table creates as VARCHAR2(100 BYTE), but it is because the UNION to hardcoded string.
Re: Is there a way to create a materialized view with datatype and length? [message #685013 is a reply to message #685012] Fri, 08 October 2021 14:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
joy_division wrote on Fri, 08 October 2021 14:06
remote is 11.2.0.4 WE8MSWIN1252. Local is 19c AL32UTF8.
But even when table is local, the underlying table is VARCHAR2(100 CHAR), but the MV or table creates as VARCHAR2(100 BYTE), but it is because the UNION to hardcoded string.
And it doesn't ring a bell? You have multi byte character character set on local and single byte character set on remote. So Oracle converts from AL32UTF8 to WE8MSWIN1252. So it doesn't matter how many bytes character occupied locally - it will occupy one byte on remote. And you will have problems when value on local uses chartacter that can't be converted to WE8MSWIN1252.

SY.
Re: Is there a way to create a materialized view with datatype and length? [message #685014 is a reply to message #685013] Fri, 08 October 2021 14:42 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, I fully understand.

Let's take the remote ad local out of it. As I mentioned, I have a table defined with a VARCHAR column as CHAR, but a CTAS creates the new tables' VARCHAR2 column as BYTE, because of the UNION.

My original question was can I somehow get the MV to create with CHAR instead of BYTE.

So this leads to me next question. In a single byte character set such as WE8MSWIN1252, is there a way to find out how many bytes it will take up in a multibyte character set? LENGTHB gives the same result as LENGTH in the single bytes character set?
Re: Is there a way to create a materialized view with datatype and length? [message #685016 is a reply to message #685014] Fri, 08 October 2021 15:37 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
1. What UNION? Post.
2. Sure - use cast in MV query:

SQL> create table tbl(col varchar2(8 byte));

Table created.

SQL> create materialized view tbl_mv as select * from tbl;

Materialized view created.

SQL> select column_name,data_length,char_length,char_used from user_tab_columns where table_name = 'TBL_MV';

COLUMN_NAM DATA_LENGTH CHAR_LENGTH C
---------- ----------- ----------- -
COL                  8           8 B

SQL> drop materialized view tbl_mv;

Materialized view dropped.


SQL> create materialized view tbl_mv as select cast(col as varchar2(8 char)) col from tbl;

Materialized view created.

SQL> select column_name,data_length,char_length,char_used from user_tab_columns where table_name = 'TBL_MV';

COLUMN_NAM DATA_LENGTH CHAR_LENGTH C
---------- ----------- ----------- -
COL                 32           8 C

SQL>
3. Use CONVERT:

SQL> WITH T AS (SELECT  UNISTR('abc\00e5\00f1\00f6') STR FROM DUAL)
  2  SELECT  LENGTHB(STR) L1,
  3          LENGTHB(CONVERT(STR,'WE8MSWIN1252')) L2
  4    FROM  T
  5  /

        L1         L2
---------- ----------
        12          6

SQL>
SY.
Previous Topic: Retrieve font name available in OS using sql plus
Next Topic: Select Query suggestion
Goto Forum:
  


Current Time: Thu Mar 28 15:30:34 CDT 2024