How to define column types of a materialized view

Dear all,

I created a materialized view from a base table:

SQL> desc tb_orders

 Name                          Null?    Type


 ----------------------------- -------- --------------------

 INVOICE_DATE                  NOT NULL DATE
 INVOICE_NO                    NOT NULL CHAR(5)
 SEQ_NO                        NOT NULL NUMBER(3)
 REP_NO                        NOT NULL VARCHAR2(8)
 FSC                                    VARCHAR2(9)
 POC                           NOT NULL VARCHAR2(5)
 PRICE_YEAR                    NOT NULL NUMBER(4)
 PRICE_CAMP                    NOT NULL NUMBER(2)
 OP_YEAR                       NOT NULL NUMBER(4)
 OP_CAMP                       NOT NULL NUMBER(2)
 CUSTOMER_SERVED                        NUMBER(3)
 ORDER_SOURCE                           CHAR(1)
 FOR_NO                                 NUMBER(2)
 SELLING_PRICE                 NOT NULL NUMBER(7)
 UNITS                                  NUMBER(10)
 NTA                                    NUMBER(15,3)
 UNITS_OF_RETURN                        NUMBER(10)
 NTA_OF_RETURN                          NUMBER(15,3)
 COST                                   NUMBER(13,3)
 PROCESS_DATE                  NOT NULL DATE

CREATE MATERIALIZED VIEW mv_fsr_base AS

    SELECT

        SF_MAKE_YEAR_CAMP(op_year, op_camp) op_time,
        SUBSTR(invoice_no,1,2) invoice_prefix,
        fsc,
        SUM(units) units,
        SUM(nta) nta
    FROM
        tb_orders
    GROUP BY 
        op_year,
        op_camp,
        SUBSTR(invoice_no,1,2),
        fsc;

SQL> desc mv_fsr_base
 Name                          Null?    Type


 ----------------------------- -------- --------------------

 OP_TIME                                VARCHAR2(4000)
 INVOICE_PREFIX                         VARCHAR2(4)
 FSC                                    VARCHAR2(9)
 UNITS                                  NUMBER
 NTA                                    NUMBER

Note that the OP_TIME column of the materialized view is of VARCHAR2(4000) but it should be VARCHAR2(8) or CHAR(8), the INVOICE_PREFIX is VARCHAR2(4), it should be VARCHAR2(2) or CHAR(2), how do you manage data types?

Thanks,
Dino Received on Thu Sep 27 2001 - 01:23:16 CDT