PL/SQL: how to create a user-defined function returning appropriate char type

Dear all,

I want to create a user-defined function that returns char(6), but I can't yet.

CREATE OR REPLACE FUNCTION sf_make_year_camp (i_year IN NUMBER, i_camp IN NUMBER)
RETURN CHAR DETERMINISTIC
IS
BEGIN
    RETURN TO_CHAR(i_year, 'FM0000') || TO_CHAR(i_camp,'FM00'); END;
/

It returns VARCHAR2(4000), so I have to explicitly cast it to char(6) in SQL, i.e.:
create materialized view mv_test as
select cast(sf_make_year_camp(op_year, op_camp) as char(6)) from tb_orders;

This is cumbersome. Anyone knows how this can be solved? It seems quite basic, but I have tried for a long time in vein.

Thanks,
Dino Received on Sun Jan 06 2002 - 04:06:46 CST