SQL & PL/SQL

Hi all,

I'm trying to create a materialized view with a specific a column size. Something like

create materialized view test_mv
refresh force on demand
as
select id, 
       cast(my_compound_field as nvarchar2(50))
from ( select id, 
              field1 || field2 my_compound_field
       from   my_table);

But Oracle seems to ignore the cast and takes the maximum size it finds for field1 || field2 in the select query. The resulting table has a column nvarchar2(44) instead of nvarchar2(50).

This can give a problem when the view is refreshed... there could be new data that exceeds the current size, i.e. where length(field1 || field2) > 44.

How can I override the column size of a field in a materialized view?

Edit: Some additional info to clarify my case:
field1 and field2 are defined as nvarchar2(25). field1 || field2 can theoretically have a length of 50, but there is currently no data in my table that results in that length, the max is 44. I am afraid that there will be data in the future that exceeds 44, resulting in an error when the MV is refreshed!

Edited by: Pleiadian on Jan 25, 2011 2:06 PM