package body invalidated

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Mon, 20 Nov 2000 10:36:09 -0500
Message-Id: <10686.122504@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_01C05307.9C858060
Content-Type: text/plain;

charset="iso-8859-1"

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 := NULL) return

varchar2;

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

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

        ADDRSTYLE_HTML constant integer := 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) := NULL;
        begin
                if p_type = 0 then
                        select SIMPLE_NAME into v_display from person
                        where person_id = p_id;
                elsif p_type = 1 then
                        select NAME_PRIMARY into v_display from org
                        where org_internal_id = p_id;
                end if;
                if v_display is null then
                        if p_onNotFound is not null then
                                v_display := p_onNotFound;
                        else
                                v_display := '"' || 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 := 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 = 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 := '';
                else
                        v_namePrefix := p_name_Prefix || ' ';
                end if;
                if p_name_Middle is NULL then
                        v_nameMiddle := '';
                else
                        v_nameMiddle := ' ' || p_name_Middle;
                end if;
                if p_name_Suffix is NULL then
                        v_nameSuffix := '';
                else
                        v_nameSuffix := ' ' || p_name_Suffix;
                end if;

                if p_style = PNAMESTYLE_SHORT then
                        return substr(p_name_first, 1, 1) || ' ' ||
p_name_Last;
                elsif p_style = PNAMESTYLE_SIMPLE then
                        return p_name_First || v_nameMiddle || ' ' ||
p_name_Last || v_nameSuffix;
                elsif p_style = PNAMESTYLE_COMPLETE then
                        return v_namePrefix || p_name_First || v_nameMiddle
|| ' ' || p_name_Last || v_nameSuffix;
                elsif p_style = 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 := p_line1;
                if not(p_line2 is NULL) and (length(p_line2) > 0) then
                        v_address := v_address || '<br>' || p_line2;
                end if;
                v_address := v_address || '<br>' || p_city || ', ' ||
p_state || ' ' || p_zip;
                return v_address;
        end;

end Pkg_Entity;

Alex Hillman

------_=_NextPart_001_01C05307.9C858060
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>package body invalidated</TITLE>
</HEAD>
<BODY>

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(
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_type in =

integer,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_id in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&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 =

FACE=3D"Arial">&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;&nbsp; p_style in =

integer,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Prefix =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_First =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Middle =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Last =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Suffix =

in varchar2) return varchar2;


        function = createAddress(
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_style in =

integer,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_line1 in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_line2 in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_city in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_state in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; 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
          &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
       &n=
bsp; 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 =

FACE=3D"Arial">&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;&nbsp; v_display =

:=3D p_onNotFound;
          &nb= sp;           &nb= sp;  else
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&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 =

FACE=3D"Arial">&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;&nbsp; return =

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

FACE=3D"Arial">&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;&nbsp; return =

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

FACE=3D"Arial">&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;&nbsp; return =

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

FACE=3D"Arial">&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;&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 =

FACE=3D"Arial">&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;&nbsp; p_style in =

integer,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Prefix =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_First =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Middle =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_name_Last =

in varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&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
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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; return substr(p_name_first, 1, 1) || ' ' || p_name_Last;</FONT=

>
          &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
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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; 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;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp; return p_name_Last || ', ' || substr(p_name_first, 1, =
1);</FONT>

          &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 =

FACE=3D"Arial">&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;&nbsp; p_style in =

integer,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_line1 in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_line2 in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_city in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_state in =

varchar2,
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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;&nbsp; p_zip in =

varchar2) return varchar2 is
          &nb= sp;      v_address varchar2(512);
         begin
          &nb= sp;      v_address :=3D p_line1;
          &nb= sp;      if not(p_line2 is NULL) and (length(p_= line2) > 0) then
 <FONT SIZE=3D2 =

FACE=3D"Arial">&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; v_address :=3D v_address || '&lt;br&gt;' || p_line2;</FONT>

          &nb= Received on Mon Nov 20 2000 - 09:36:09 CST