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

As Sybrand says ... that's not the way it works. But you can accomplish the same thing by returning a VARCHAR2 and encapsulating the returned value with an RPAD.

Daniel Morgan

Dino Hsu wrote:

> 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 - 09:27:52 CST