Re: package body invalidated

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Mon, 20 Nov 2000 11:52:12 -0500
Message-Id: <10686.122511@fatcity.com>

If you select * from dba_errors where name =3D'YOUR PACKAGE' you will get = the errors and the line reference in the error. This will help in your trouble shooting the package. ROR m=AA=BF=AAm

>>> alex_hillman_at_physia.com 11/20/00 10:42AM >>>
Package body of one of my application packages becomes invalid intermittantly and I have no idea why.
Oracle 8.1.6.2 Sun Solaris 2.7

Anybody can suggest the possible cause for invalidation of package body of this package?

Create or Replace Package PRO_TEST.Pkg_Entity as

        function cleanupEntityId(p_id in varchar2) return varchar2;

        procedure validateEntityId(p_type in integer, p_id in varchar2);

        function getEntityDisplay(
                                p_type in integer,
                                p_id in varchar2,
                                p_onNotFound in varchar2 :=3D NULL) return

varchar2;

        PRAGMA RESTRICT_REFERENCES(cleanupEntityId, RNDS, WNDS, WNPS, = RNPS);
        PRAGMA RESTRICT_REFERENCES(getEntityDisplay, WNDS, WNPS, RNPS);

        PNAMESTYLE_SHORT constant integer :=3D 0;
        PNAMESTYLE_SIMPLE constant integer :=3D 1;
        PNAMESTYLE_COMPLETE constant integer :=3D 2;
        PNAMESTYLE_SHORT_SORTABLE constant integer :=3D 3;
        PNAMESTYLE_SORTABLE constant integer :=3D 4;

        ADDRSTYLE_HTML constant integer :=3D 0;

        function getPersonAge(p_birthDate in date) return varchar2;

        function createPersonName(
                                p_style in integer,
                                p_name_Prefix in varchar2,
                                p_name_First in varchar2,
                                p_name_Middle in varchar2,
                                p_name_Last in varchar2,
                                p_name_Suffix in varchar2) return =

varchar2;

        function createAddress(
                                p_style in integer,
                                p_line1 in varchar2,
                                p_line2 in varchar2,
                                p_city in varchar2,
                                p_state in varchar2,
                                p_zip in varchar2) return varchar2;

        PRAGMA RESTRICT_REFERENCES(getPersonAge, WNDS, WNPS, RNPS);

end Pkg_Entity;

Create or Replace Package Body PRO_TEST.Pkg_Entity as

        function CleanupEntityId(p_id in varchar2) return varchar2 is
        begin
                if p_id is null then
                        return null;
                else
                        return upper(p_id);
                end if;
        end;

        procedure validateEntityId(p_type in integer, p_id in varchar2) is
        begin
                NULL;
        end;

        function getEntityDisplay(p_type in integer, p_id in varchar2,
p_onNotFound in varchar2) return varchar2 is
                v_display varchar2(512) :=3D NULL;
        begin
                if p_type =3D 0 then
                        select SIMPLE_NAME into v_display from person
                        where person_id =3D p_id;
                elsif p_type =3D 1 then
                        select NAME_PRIMARY into v_display from org
                        where org_internal_id =3D p_id;
                end if;
                if v_display is null then
                        if p_onNotFound is not null then
                                v_display :=3D p_onNotFound;
                        else
                                v_display :=3D '"' || p_id || '" (' || =

p_type
|| ') not found';

                        end if;
                end if;
                return v_display;
        end;

        function getPersonAge(p_birthDate in date) return varchar2 is
                v_ageMonths number;
        begin
                if p_birthDate is null then
                        return null;
                else
                        v_ageMonths :=3D MONTHS_BETWEEN(SysDate, p_birthDat=
e);
                        if v_ageMonths > 12 then
                                return to_char(trunc(v_ageMonths/12));
                        elsif v_ageMonths > 1 then
                                return to_char(trunc(v_ageMonths)) || '
months';
                        elsif v_ageMonths =3D 1 then
                                return to_char(trunc(v_ageMonths)) || '
month';
                        else
                                return to_char(trunc(v_ageMonths * 30)) || =

'
days';

                        end if;
                end if;
        end;

        function createPersonName(
                                p_style in integer,
                                p_name_Prefix in varchar2,
                                p_name_First in varchar2,
                                p_name_Middle in varchar2,
                                p_name_Last in varchar2,
                                p_name_Suffix in varchar2) return varchar2
is
                v_namePrefix varchar2(32);
                v_nameMiddle varchar2(64);
                v_nameSuffix varchar2(32);
        begin
                if p_name_Prefix is NULL then
                        v_namePrefix :=3D '';
                else
                        v_namePrefix :=3D p_name_Prefix || ' ';
                end if;
                if p_name_Middle is NULL then
                        v_nameMiddle :=3D '';
                else
                        v_nameMiddle :=3D ' ' || p_name_Middle;
                end if;
                if p_name_Suffix is NULL then
                        v_nameSuffix :=3D '';
                else
                        v_nameSuffix :=3D ' ' || p_name_Suffix;
                end if;

                if p_style =3D PNAMESTYLE_SHORT then
                        return substr(p_name_first, 1, 1) || ' ' ||
p_name_Last;
                elsif p_style =3D PNAMESTYLE_SIMPLE then
                        return p_name_First || v_nameMiddle || ' ' ||
p_name_Last || v_nameSuffix;
                elsif p_style =3D PNAMESTYLE_COMPLETE then
                        return v_namePrefix || p_name_First || v_nameMiddle=

|| ' ' || p_name_Last || v_nameSuffix;
                elsif p_style =3D PNAMESTYLE_SHORT_SORTABLE then
                        return p_name_Last || ', ' || substr(p_name_first,
1, 1);
                else
                        return p_name_Last || v_nameSuffix  || ', ' ||
p_name_First || v_nameMiddle;
                end if;
        end;

        function createAddress(
                                p_style in integer,
                                p_line1 in varchar2,
                                p_line2 in varchar2,
                                p_city in varchar2,
                                p_state in varchar2,
                                p_zip in varchar2) return varchar2 is
                v_address varchar2(512);
        begin
                v_address :=3D p_line1;
                if not(p_line2 is NULL) and (length(p_line2) > 0) then
                        v_address :=3D v_address || '<br>' || p_line2;
                end if;

Received on Mon Nov 20 2000 - 10:52:12 CST