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

On Sun, 06 Jan 2002 18:06:46 +0800, Dino Hsu <nospam.dino1_at_ms1.hinet.net> 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
>

This is just the way it works.

Sorry

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jan 06 2002 - 06:09:34 CST