Re: package body invalidated

From: Nikunj Gupta <good_dba_at_hotmail.com>
Date: Mon, 20 Nov 2000 22:52:25 -0800
Message-Id: <10686.122583@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_00C2_01C05344.8D0B8D80 Content-Type: text/plain;

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

RE: package body invalidatedHi,

Are you using any views, procedures, packages, fuctions, synonyms etc ??

Are there any snapshots ??

If any of these are there.. just check.. do they get invalid too ??

HTH

Original Message -----=20 From: Alex Hillman=20 To: Multiple recipients of list ORACLE-L=20 Sent: Monday, November 20, 2000 11:26 AM Subject: RE: package body invalidated

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

Alex Hillman=20

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

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

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

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

Create or Replace Package PRO_TEST.Pkg_Entity as=20

function cleanupEntityId(p_id in varchar2) return varchar2;=20

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

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

return=20
varchar2;=20

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

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

          ADDRSTYLE_HTML constant integer :=3D 0;=20

          function getPersonAge(p_birthDate in date) return varchar2;=20

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

varchar2;=20

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

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

end Pkg_Entity;=20

Create or Replace Package Body PRO_TEST.Pkg_Entity as=20

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

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

          function getEntityDisplay(p_type in integer, p_id in varchar2, =

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

p_type=20
|| ') not found';=20

                          end if;=20
                  end if;=20
                  return v_display;=20
          end;=20

          function getPersonAge(p_birthDate in date) return varchar2 is=20
                  v_ageMonths number;=20
          begin=20
                  if p_birthDate is null then=20
                          return null;=20
                  else=20
                          v_ageMonths :=3D MONTHS_BETWEEN(SysDate, =
p_birthDate);=20
                          if v_ageMonths > 12 then=20
                                  return to_char(trunc(v_ageMonths/12)); =

                          elsif v_ageMonths > 1 then=20
                                  return to_char(trunc(v_ageMonths)) || =

'=20
months';=20

                          elsif v_ageMonths =3D 1 then=20
                                  return to_char(trunc(v_ageMonths)) || =

'=20
month';=20

                          else=20
                                  return to_char(trunc(v_ageMonths * =

30)) || '=20
days';=20

                          end if;=20
                  end if;=20
          end;=20

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

varchar2=20
is=20

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

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

v_nameMiddle=20
|| ' ' || p_name_Last || v_nameSuffix;=20

                  elsif p_style =3D PNAMESTYLE_SHORT_SORTABLE then=20
                          return p_name_Last || ', ' || =

substr(p_name_first,=20
1, 1);=20

                  else=20
                          return p_name_Last || v_nameSuffix  || ', ' || =

  p_name_First || v_nameMiddle;=20
                  end if;=20
          end;=20

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

                          v_address :=3D v_address || '<br>' || p_line2; =

                  end if;=20
                  v_address :=3D v_address || '<br>' || p_city || ', ' =

||=20
p_state || ' ' || p_zip;=20

                  return v_address;=20
          end;=20

end Pkg_Entity;=20

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

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

  --------------------------------------------------------------------=20

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

------=_NextPart_000_00C2_01C05344.8D0B8D80 Content-Type: text/html;

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: package body invalidated</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Are you using any views, procedures, =

packages,=20
fuctions, synonyms etc ??</DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Are there any snapshots ??</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>If any of these are there.. just =

check.. do they=20
get invalid too ??</DIV>

<DIV><BR>HTH</DIV>
<DIV>&nbsp;</DIV>
<DIV></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20
 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black">From:=20
 <A title=3Dalex_hillman_at_physia.com =
href=3D"mailto:alex_hillman_at_physia.com">Alex=20 Hillman</A> </DIV>
 <DIV style=3D"FONT: 10pt arial">To: <A = title=3DORACLE-L_at_fatcity.com=20
 href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
 </DIV>
 <DIV style=3D"FONT: 10pt arial">Sent: Monday, November 20, 2000 = 11:26=20
 AM</DIV>
 <DIV style=3D"FONT: 10pt arial">Subject: RE: package body=20 invalidated</DIV>
 <DIV> </DIV>
 The package body does not have any errors. When I = compile it=20
 it become valid again. 
 Alex Hillman -----Original Message----- From: Ron=20
 Rogers [<A=20
 =
href=3D"mailto:RROGERS_at_galottery.org">mailto:RROGERS_at_galottery.org</A>]</= FONT>=20
 Sent: Monday, November 20, 2000 11:56 AM = <FONT=20
 size=3D2>To: Multiple recipients of list ORACLE-L <FONT=20 size=3D2>Subject: Re: package body invalidated If you select * from dba_errors where name =3D'YOUR = PACKAGE' you=20
 will get the errors and the line reference in the error. = <FONT=20
 size=3D2>This will help in your trouble shooting the package. = <FONT=20
 size=3D2>ROR  m=AA=BF=AAm >>> alex_hillman_at_physia.com 11/20/00 = 10:42AM=20
 >>> Package body of one of my = application=20
 packages becomes invalid intermittantly and = I have no=20
 idea why. Oracle 8.1.6.2  Sun Solaris = 2.7=20
 
 Anybody can suggest the possible cause for = invalidation of=20
 package body of this package?

  <P><FONT size=3D2>Create or Replace Package PRO_TEST.Pkg_Entity =
as</FONT> </P>
  <P><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; function=20

cleanupEntityId(p_id in varchar2) return varchar2;         procedure =

validateEntityId(p_type in integer, p_id in varchar2);         function=20 getEntityDisplay( <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_type in integer, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_id in varchar2, <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_onNotFound in varchar2 :=3D NULL) return <FONT=20 size=3D2>varchar2; 
         PRAGMA=20 RESTRICT_REFERENCES(cleanupEntityId, RNDS, WNDS, WNPS, RNPS); = <FONT=20
 size=3D2>        PRAGMA=20 RESTRICT_REFERENCES(getEntityDisplay, WNDS, WNPS, RNPS);         = PNAMESTYLE_SHORT=20
 constant integer :=3D 0; <FONT=20 size=3D2>        PNAMESTYLE_SIMPLE = constant=20
 integer :=3D 1; <FONT=20
 size=3D2>        = PNAMESTYLE_COMPLETE constant=20
 integer :=3D 2; <FONT=20
 size=3D2>        = PNAMESTYLE_SHORT_SORTABLE=20
 constant integer :=3D 3; <FONT=20 size=3D2>        = PNAMESTYLE_SORTABLE constant=20
 integer :=3D 4; 
         = ADDRSTYLE_HTML=20
 constant integer :=3D 0; 
         function=20 getPersonAge(p_birthDate in date) return varchar2;         function=20 createPersonName( <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_style in integer, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Prefix in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_First in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Middle in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Last in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Suffix in varchar2) return varchar2;         function=20 createAddress( <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_style in integer, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_line1 in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_line2 in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_city in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_state in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_zip in varchar2) return varchar2;         PRAGMA=20 RESTRICT_REFERENCES(getPersonAge, WNDS, WNPS, RNPS);

  <P><FONT size=3D2>end Pkg_Entity;</FONT> </P><BR>
  <P><FONT size=3D2>Create or Replace Package Body PRO_TEST.Pkg_Entity =
as</FONT>=20
  </P>
  <P><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; function=20

CleanupEntityId(p_id in varchar2) return varchar2 is <FONT=20 size=3D2>        begin = <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_id is null then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return null; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return upper(p_id); <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if;        =20 end; 
         procedure =

validateEntityId(p_type in integer, p_id in varchar2) is = <FONT=20
 size=3D2>        begin = <FONT=20
 =
size=3D2>          &nbs= p;    =20
 NULL;        =20 end; 
         function=20 getEntityDisplay(p_type in integer, p_id in varchar2, <FONT =

size=3D2>p_onNotFound in varchar2) return varchar2 is <FONT =

=
size=3D2>          &nbs= p;    =20
 v_display varchar2(512) :=3D NULL; <FONT=20 size=3D2>        begin = <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_type =3D 0 then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 select SIMPLE_NAME into v_display from person <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 where person_id =3D p_id; <FONT=20 =
size=3D2>          &nbs= p;    =20
 elsif p_type =3D 1 then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 select NAME_PRIMARY into v_display from org <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 where org_internal_id =3D p_id; <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if v_display is null then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 if p_onNotFound is not null then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 v_display :=3D p_onNotFound; <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 v_display :=3D '"' || p_id || '" (' || p_type || ') not=20
 found'; <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 return v_display; <FONT=20
 size=3D2>        end;         function=20 getPersonAge(p_birthDate in date) return varchar2 is <FONT=20 =
size=3D2>          &nbs= p;    =20
 v_ageMonths number; <FONT=20 size=3D2>        begin = <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_birthDate is null then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return null; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_ageMonths :=3D MONTHS_BETWEEN(SysDate, p_birthDate); = <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 if v_ageMonths > 12 then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 return to_char(trunc(v_ageMonths/12)); <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 elsif v_ageMonths > 1 then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 return to_char(trunc(v_ageMonths)) || ' <FONT=20 size=3D2>months'; <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 elsif v_ageMonths =3D 1 then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 return to_char(trunc(v_ageMonths)) || ' month';=20
 <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 return to_char(trunc(v_ageMonths * 30)) || ' <FONT=20 size=3D2>days'; <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 end if;        =20 end; 
         function=20 createPersonName( <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_style in integer, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Prefix in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_First in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Middle in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Last in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_name_Suffix in varchar2) return varchar2 is=20
 <FONT=20
 =
size=3D2>          &nbs= p;    =20
 v_namePrefix varchar2(32); <FONT=20 =
size=3D2>          &nbs= p;    =20
 v_nameMiddle varchar2(64); <FONT=20 =
size=3D2>          &nbs= p;    =20
 v_nameSuffix varchar2(32); <FONT=20 size=3D2>        begin = <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_name_Prefix is NULL then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_namePrefix :=3D ''; <FONT=20 =
size=3D2>          &nbs= p;    =20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_namePrefix :=3D p_name_Prefix || ' '; <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_name_Middle is NULL then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_nameMiddle :=3D ''; <FONT=20 =
size=3D2>          &nbs= p;    =20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_nameMiddle :=3D ' ' || p_name_Middle; <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_name_Suffix is NULL then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_nameSuffix :=3D ''; <FONT=20 =
size=3D2>          &nbs= p;    =20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_nameSuffix :=3D ' ' || p_name_Suffix; <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if; 
 <FONT=20
 =
size=3D2>          &nbs= p;    =20
 if p_style =3D PNAMESTYLE_SHORT then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return substr(p_name_first, 1, 1) || ' ' || <FONT=20 size=3D2>p_name_Last; <FONT=20 =
size=3D2>          &nbs= p;    =20
 elsif p_style =3D PNAMESTYLE_SIMPLE then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return p_name_First || v_nameMiddle || ' ' || <FONT=20 size=3D2>p_name_Last || v_nameSuffix; <FONT=20 =
size=3D2>          &nbs= p;    =20
 elsif p_style =3D PNAMESTYLE_COMPLETE then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return v_namePrefix || p_name_First || v_nameMiddle ||=20
 ' ' || p_name_Last || v_nameSuffix; <FONT=20 =
size=3D2>          &nbs= p;    =20
 elsif p_style =3D PNAMESTYLE_SHORT_SORTABLE then <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return p_name_Last || ', ' || substr(p_name_first, 1,=20
 1); <FONT=20
 =
size=3D2>          &nbs= p;    =20
 else <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 return p_name_Last || v_nameSuffix  || ', ' || <FONT=20 size=3D2>p_name_First || v_nameMiddle; <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if;        =20 end; 
         function=20 createAddress( <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_style in integer, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_line1 in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_line2 in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_city in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_state in varchar2, <FONT=20 =
size=3D2>          &nbs= p;           &nbsp= ;        =20 p_zip in varchar2) return varchar2 is <FONT=20 =
size=3D2>          &nbs= p;    =20
 v_address varchar2(512); <FONT=20 size=3D2>        begin = <FONT=20
 =
size=3D2>          &nbs= p;    =20
 v_address :=3D p_line1; <FONT=20 =
size=3D2>          &nbs= p;    =20
 if not(p_line2 is NULL) and (length(p_line2) > 0) then = <FONT=20
 =
size=3D2>          &nbs= p;           &nbsp= ;=20
 v_address :=3D v_address || ' ' || p_line2; <FONT=20 =
size=3D2>          &nbs= p;    =20
 end if; <FONT=20
 =
size=3D2>          &nbs= p;    =20
 v_address :=3D v_address || ' ' || p_city || ', ' || = <FONT=20
 size=3D2>p_state || ' ' || p_zip; <FONT=20 =
size=3D2>          &nbs= p;    =20
 return v_address; <FONT=20
 size=3D2>        end; end Pkg_Entity; Alex Hillman -- = <FONT=20
 size=3D2>Please see the official ORACLE-L FAQ: <A target=3D_blank=20 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>-- Author: Ron Rogers = <FONT=20
 size=3D2>  INET: RROGERS_at_galottery.org Fat City Network Services    -- (858) = Received on Tue Nov 21 2000 - 00:52:25 CST