RE: package body invalidated

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Mon, 20 Nov 2000 14:17:43 -0500
Message-Id: <10686.122531@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C05326.920080F0
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

The package body does not have any errors. When I compile it it become = valid
again.

Alex Hillman

-----Original Message-----
From: Ron Rogers [mailto:RROGERS_at_galottery.org] Sent: Monday, November 20, 2000 11:56 AM To: Multiple recipients of list ORACLE-L Subject: Re: package body invalidated

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_birthDate);
                        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;
                v_address :=3D v_address || '<br>' || p_city || ', ' ||
p_state || ' ' || p_zip;
                return v_address;
        end;

end Pkg_Entity;

Alex Hillman
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: Ron Rogers
INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C05326.920080F0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0">

<TITLE>RE: package body invalidated</TITLE>
</HEAD>
<BODY>

The package body does not have any errors. When I = compile it it become valid again.

Alex Hillman

-----Original Message----- From: Ron Rogers [<A = HREF=3D"mailto:RROGERS_at_galottery.org">mailto:RROGERS_at_galottery.org</A>]<= /FONT>

<BR><FONT SIZE=3D2>Sent: Monday, November 20, 2000 11:56 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: package body invalidated</FONT>
</P>
<BR>

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

<BR><FONT SIZE=3D2>intermittantly and I have no idea why.</FONT>
<BR><FONT SIZE=3D2>Oracle 8.1.6.2&nbsp; Sun Solaris 2.7</FONT>
</P>

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(
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_type in =

integer,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_id in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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(
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_style in =

integer,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Prefix in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_First in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Middle in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Last in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Suffix in =

varchar2) return varchar2;

function = createAddress(
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_style in =

integer,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_line1 in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_line2 in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_city in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_state in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_zip in varchar2) =

return varchar2;

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

<P><FONT SIZE=3D2>end Pkg_Entity;</FONT>
</P>
<BR>

Create or Replace Package Body PRO_TEST.Pkg_Entity = as

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

procedure = validateEntityId(p_type in integer, p_id in varchar2) is         = begin
           &nb= sp;     NULL;         = end;

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

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_display :=3D =

p_onNotFound;
           &nb= sp;           &nb= sp; else
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_display :=3D =

'"' || p_id || '" (' || p_type || ') not found';           &nb= sp;           &nb= sp; end if;
           &nb= sp;     end if;           &nb= sp;     return v_display;         = end;

function = getPersonAge(p_birthDate in date) return varchar2 is           &nb= sp;     v_ageMonths number;         = begin
           &nb= sp;     if p_birthDate is null then           &nb= sp;           &nb= sp; return null;
           &nb= sp;     else
           &nb= sp;           &nb= sp; v_ageMonths :=3D MONTHS_BETWEEN(SysDate, p_birthDate);           &nb= sp;           &nb= sp; if v_ageMonths > 12 then
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return =

to_char(trunc(v_ageMonths/12));
           &nb= sp;           &nb= sp; elsif v_ageMonths > 1 then <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return =

to_char(trunc(v_ageMonths)) || '
 months';
           &nb= sp;           &nb= sp; elsif v_ageMonths =3D 1 then
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return =

to_char(trunc(v_ageMonths)) || '
 month';
           &nb= sp;           &nb= sp; else
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return =

to_char(trunc(v_ageMonths * 30)) || ' days';
           &nb= sp;           &nb= sp; end if;
           &nb= sp;     end if;         = end;

function = createPersonName(
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_style in =

integer,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Prefix in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_First in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Middle in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Last in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_name_Suffix in =

varchar2) return varchar2
 is
           &nb= sp;     v_namePrefix varchar2(32);           &nb= sp;     v_nameMiddle varchar2(64);           &nb= sp;     v_nameSuffix varchar2(32);         = begin
           &nb= sp;     if p_name_Prefix is NULL then           &nb= sp;           &nb= sp; v_namePrefix :=3D '';
           &nb= sp;     else
           &nb= sp;           &nb= sp; v_namePrefix :=3D p_name_Prefix || ' ';           &nb= sp;     end if;           &nb= sp;     if p_name_Middle is NULL then           &nb= sp;           &nb= sp; v_nameMiddle :=3D '';
           &nb= sp;     else
           &nb= sp;           &nb= sp; v_nameMiddle :=3D ' ' || p_name_Middle;           &nb= sp;     end if;           &nb= sp;     if p_name_Suffix is NULL then           &nb= sp;           &nb= sp; v_nameSuffix :=3D '';
           &nb= sp;     else
           &nb= sp;           &nb= sp; v_nameSuffix :=3D ' ' || p_name_Suffix;           &nb= sp;     end if;

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

function = createAddress(
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_style in =

integer,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_line1 in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_line2 in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_city in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_state in =

varchar2,
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_zip in varchar2) =

return varchar2 is
           &nb= sp;     v_address varchar2(512);         = begin
           &nb= sp;     v_address :=3D p_line1; <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp; if not(p_line2 is NULL) and =
(length(p_line2) &gt; 0) then</FONT>

<FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; v_address :=3D v_address || '&lt;br&gt;' || p_line2;</FONT>

&nb= sp;     end if;           &nb= sp;     v_address :=3D v_address || ' ' || = p_city || ', ' ||
 p_state || ' ' || p_zip;           &nb= sp;     return v_address;         = end;

<P><FONT SIZE=3D2>end Pkg_Entity;</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Alex Hillman</FONT>

-- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Ron Rogers</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: RROGERS_at_galottery.org</FONT>
</P>

Fat City Network Services    -- (858) = Received on Mon Nov 20 2000 - 13:17:43 CST