Re: Oracle Packages: Is there a way to...

From: Marc Mazerolle <informaze_at_sympatico.ca>
Date: Mon, 14 Jun 1999 20:10:24 GMT
Message-ID: <3765630D.A872C7C0@sympatico.ca>

Why not do it this way :

---

CREATE OR REPLACE PACKAGE BODY XYZ AS FUNCTION ISNULL(inpt IN DATE) return varchar2 is begin

    if (inpt is null) then

        return "Y";
    else

        return "N";
    end if;
end;
FUNCTION ISNULL(inpt IN NUMBER) return varchar2 is begin

    if (inpt is null) then

        return "Y";
    else

        return "N";
    end if;
end;
FUNCTION ISNULL(inpt IN VARCHAR2) return varchar2 is begin

    if (inpt is null) then

        return "Y";
    else

        return "N";
    end if;
end;
PROCEDURE P_XYZ

        (
                  o_id                 OUT t_id
                , o_desc               OUT t_desc
                , o_active             OUT t_boolean
                , o_end_dt             OUT t_date
                , o_srt_seq_num        OUT t_srt_seq_num
                , o_last_maint_dt      OUT t_date
                , o_last_maint_user_id OUT t_lupd_user
        )

IS
        CURSOR tbl_cur  IS

                SELECT id
                         , desc
                         , end_dt
                         , num
                         , dte
                         , user_id
                FROM table_xyz
BEGIN
        FOR row IN tbl_cur
        LOOP
                o_id(i) := row.pymt_meth_id;
                o_desc(i) := row.pymt_meth_desc;
                o_active(i) := ISNULL(row.end_dt);
                o_dte(i) := row.dte;
                o_num(i) := row.num;
                o_dte(i):= row.last_maint_dt;
                o_user_id(i):= row.user_id;
        i := i + 1;
        END LOOP;

END;
END; This should cover most cases.....

stemp1ar_at_my-deja.com wrote:

> Hello and Thank you...
>
> What I would like to do is setup a generic package. Is there a way to
> do this, and am I going in the right direction?
>
> Is there a way to test for boolean in an IF statement as written
> below...(o_active := IF( ISNULL(row.end_dt), "Y", "N");)
>
> ------------------------------------------------------------
> CREATE OR REPLACE PACKAGE BODY XYZ AS
> PROCEDURE P_XYZ
> (
> o_id OUT t_id
> , o_desc OUT t_desc
> , o_active OUT t_boolean
> , o_end_dt OUT t_date
> , o_srt_seq_num OUT t_srt_seq_num
> , o_last_maint_dt OUT t_date
> , o_last_maint_user_id OUT t_lupd_user
> )
>
> IS
> CURSOR tbl_cur IS
>
> SELECT id
> , desc
> , end_dt
> , num
> , dte
> , user_id
> FROM table_xyz
> BEGIN
> FOR row IN tbl_cur
> LOOP
> o_id(i) := row.pymt_meth_id;
> o_desc(i) := row.pymt_meth_desc;
> o_active(i) := IF( ISNULL(row.end_dt), "Y", "N");
> o_dte(i) := row.dte;
> o_num(i) := row.num;
> o_dte(i):= row.last_maint_dt;
> o_user_id(i):= row.user_id;
> i := i + 1;
> END LOOP;
> END;
> END;
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Jun 14 1999 - 15:10:24 CDT