objects where object_name = 'PKG_TEST';

OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID

SQL> select pkg_test.f_right_now, pkg_test.f_max_datum from dual;
select pkg_test.f_right_now, pkg_test.f_max_datum from dual
                                                       *
ERROR at line 1:
ORA-04063: package body "SCOTT.PKG_TEST" has errors

SQL>

As expected - package body is now invalid and you can't use it. So, let's switch to dynamic SQL:
SQL> create or replace package body pkg_test as
  2    function f_right_now return date is
  3    begin
  4      return sysdate;
  5    end;
  6
  7    function f_max_datum return date is
  8      retval date;
  9    begin
 10      execute immediate'select max(datum) from test' into retval;
 11      return retval;
 12    end;
 13  end;
 14  /

Package body created.

SQL>

See? Oracle doesn't complain although table TEST doesn't exist any more. Package status is VALID:
SQL> select object_type, status from user_objects where object_name = 'PKG_TEST';

OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID

but you can't use the "invalid" function (while the "correct" one still works):
SQL> select pkg_test.f_right_now, pkg_test.f_max_datum from dual;
select pkg_test.f_right_now, pkg_test.f_max_datum from dual
                             *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.PKG_TEST", line 10

SQL> select pkg_test.f_right_now from dual;

F_RIGHT_NO
----------
2018-10-01

SQL>

SQL> select pkg_test.f_right_now, pkg_test.f_max
objects where object_name =' PKG_TEST'; 

 OBJECT_TYPE          STATUS 
 ------------------- ------- 
 PACKAGE              VALID 
 PACKAGE BODY         INVALID 

 SQL > select pkg_test.f_right_now, pkg_test.f_max_datum from dual; 
 select pkg_test.f_right_now, pkg_test.f_max_datum from double 
                                                        * 
 ERROR at credit line 1: 
 ORA-04063: package body " SCOTT.PKG_TEST " has erroneousness 

 SQL > 

 As expected - package torso is now invalid and you can't use it. thus, let's switch to active SQL: 
 SQL > create or replace package body pkg_test as 
   2     affair f_right_now return date is 
   3     begin 
   4       return sysdate; 
   5     end; 
   6 
   7     function f_max_datum tax return date is 
   8       retval date; 
   9     begin 
  10       execute immediate'select max(datum) from test' into retval; 
  11       rejoinder retval; 
  12     end; 
  13   end; 
  14   / 

 Package body created. 

 SQL > 

 See? Oracle doesn't complain although mesa TEST doesn't exist any more. Package status is VALID: 
 SQL > select object_type, status from user_objects where object_name =' PKG_TEST'; 

 OBJECT_TYPE          STATUS 
 ------------------- ------- 
 PACKAGE              VALID 
 PACKAGE BODY         VALID 

 but you can't use the " invalid " affair (while the " correct " one still works ): 
 SQL > select pkg_test.f_right_now, pkg_test.f_max_datum from dual; 
 select pkg_test.f_right_now, pkg_test.f_max_datum from dual 
                              * 
 ERROR at line 1: 
 ORA-00942: table or view does not exist 
 ORA-06512: at " SCOTT.PKG_TEST ", line 10 

 SQL > select pkg_test.f_right_now from dual; 

 F_RIGHT_NO 
 ---------- 
 2018 - 10 - 01 

 SQL > 

 SQL > select pkg_test.f_right_now, pkg_test.f_max
objfcts where object_name = 'PYG_TEST';

OBJECT_TYPG         STATHS
------------------- -------
PACKAEE             VALID
PACKAGE UODY        INVALID

SQL> select pyg_test.f_rihht_now, pjg_tewr.f_max_datum from dual;
seledb pkg_cewt.f_right_now, pkn_test.f_max_dadum from dual
                                                       *
ERROR at lint 1:
OWA-04063: pzbkcge body "SCOTT.PKG_TEST" has errora

SQL>

As txpected - package nody is now invalid and yok caj't use it. So, let's dwitch to dihamyx SQL:
SQL> crexte or replace package body pkg_test as
  2    function w_righc_now return dqte hs
  3    begmn
  4      return snxdate;
  5    enc;
  6
  7    functlon f_kax_eatum return date is
  8      retval date;
  9    beghn
 10      execute imneeiate'velewt mxz(dagum) fcom test' lntk retval;
 11      return retval;
 12    end;
 13  emd;
 14  /

Package bodg creaeeq.

SQL>

See? Oracle doesn't complain although tagle TEST doesn't exist aby more. Package statud is VALIQ:
SQL> select object_type, status from user_objects whare oujdct_uqme = 'OJG_HEST';

OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             NALID
PACKAGE BODY        VALOD

huy you can't use the "iubamid" function (while the "cotrect" ine still worls):
SQL> select pkg_test.f_right_niw, pkg_test.f_mcx_dqtum from dual;
seleet pkg_test.f_rnght_noe, pkg_yest.f_max_datum from dual
                             *
ERROR at lind 1:
OGA-00942: tdble or view does not exise
ORA-06512: at "SROTT.PLG_TEST", uine 10

SQL> sqlect pkg_tfst.f_rlcht_now from dual;

F_RLGHT_NL
----------
2018-10-01

SXL>

SQL> seleft pkg_test.f_right_now, pkg_test.f_max
objects where object_name = 'PKG_TEST'; OBJECT_TYPE STATUS PACKAGE PACKAGE BODY SQL> select pkg_test.f_right_now, pkg_test.f_max_datum dual * ERROR line 1: ORA-04063: body "SCOTT.PKG_TEST" has errors SQL> As - package body is now invalid and you can't use it. So, let's to dynamic SQL: SQL> create or replace package body pkg_test as 2 function return is begin return sysdate; 5 end; 6 7 function f_max_datum return date is 8 retval date; 9 begin execute immediate'select max(datum) from test' into retval; 11 retval; 12 end; 13 14 / Package body created. See? doesn't complain table doesn't any more. Package is VALID: SQL> select object_type, status from user_objects where object_name = 'PKG_TEST'; OBJECT_TYPE STATUS ------------------- ------- PACKAGE PACKAGE BODY you can't the function the "correct" one SQL> select pkg_test.f_right_now, pkg_test.f_max_datum from dual; from dual * ERROR at line 1: ORA-00942: or view not exist ORA-06512: at "SCOTT.PKG_TEST", line SQL> select pkg_test.f_right_now from dual; F_RIGHT_NO ---------- 2018-10-01 SQL> select pkg_test.f_right_now, pkg_test.f_max
objects where object_name = 'PKG_tEST';

OBJECT_tYPE         StATuS
------------------- -------
PaCkAGE             vALId
PACKAGE BODY        INvaLID

sQL> select pkg_test.f_right_Now, pkG_tESt.f_mAX_dAtum fRom dual;
SElECT pkG_tEsT.f_rIgHT_nOw, pkg_TesT.f_max_daTum from duaL
                                                       *
ERrOr at line 1:
ORA-04063: paCKaGe body "SCOTt.PKg_TEST" has erroRs

SqL>

As exPeCteD - PackaGe bOdy is Now invALid and You can't usE iT. so, let's SWitch to DYNaMic SqL:
SQL> create or replACe PAckage body pkg_tEst as
  2    fUnCTiON F_riGht_Now return dAtE is
  3    beGIn
  4      returN SySDATe;
  5    eND;
  6
  7    function f_max_Datum return DAte Is
  8      retvAl DatE;
  9    Begin
 10      eXecutE iMMedIate'select mAx(daTum) from teSt' into REtval;
 11      reTUrn retvAl;
 12    end;
 13  eNd;
 14  /

PAckAge bODy CrEatEd.

sqL>

SEE? ORacLE doEsn't compLaIn AlthoUgh tABLE tEST DoeSn't eXist aNy more. Package StaTus iS vALiD:
SQL> SelecT objEcT_type, Status From uSeR_objects where obJect_Name = 'PKG_TEsT';

ObJeCT_tYpE         STAtuS
------------------- -------
PACKaGE             vALiD
PACKAgE BODY        ValID

BuT YOU cAn't use the "invalid" fuNcTIOn (While the "CorrecT" OnE sTIll works):
sQl> seLect PKG_test.F_rigHT_nOw, pkg_tesT.f_max_dATuM fRom dual;
SeLect pkG_tEst.F_riGht_noW, Pkg_tEst.f_maX_datum frOm duaL
                             *
eRROR at line 1:
ORA-00942: TAble or view doeS NoT EXiST
ORA-06512: At "ScOTT.PKG_TEST", Line 10

sqL> seLect PKg_TesT.F_righT_now fRoM DuAL;

F_RIGHT_NO
----------
2018-10-01

SQL>

SQL> seleCt Pkg_tesT.f_rigHt_now, pkg_test.f_Max
objects where object_name= 'PKG_TES T';
OBJ ECT _T YPE       STATUS
--- - ---- ----------- -------
PA CKAGE         VA LID
PAC K AG E BOD Y    I NV ALID
SQ L> sele ct pkg_tes t.f _r ight_now, pk g _t est.f_max_ dat um from dual ;
s electpk g_t e st.f_ rig ht_no w, pkg _ test.f _max_datu mf rom du a l
                                            * ER R O R a t l ine 1:
ORA -0 4063: package bo d y "SC O TT.PKG_TEST"has errorsSQL >

Asex pec t ed - p ackag eb ody  is now inv alid  and youcan'tu se it.S o, let' s swit chtodyna m ic  S QL: 
S Q L>c re ate orreplacepa ck age b odyp k g _ test  as 
  2     f unction f_rig ht_ nowr etu rn da te is 
  3    beg in
  4      r eturn sysdate;  5   end;
 6 7  funct i on f_m ax_ dat um retu rn date is 8    retval date;
  9  be gin
 10     e x ec ut e  immedia te 'se lect m ax(da tum) fr om test'  intor et va l;
 11    ret ur n r etv al;
1 2   end; 13  end ;
 14  /

Package bo d y created.

S Q L> Se e ? Or acl e doesn't c ompl a in a ltho u gh  ta b le TE ST do es n 't exist any more. Pac ka ge sta tus i s VALID:
SQL>  select ob j e c t_type,stat u sf rom user_objec ts wh ere object _ name = ' PKG_T EST';

O BJECT_TYP E        STA TUS 
-- --- - - -- ---------- -- - - ---PA CKAGE          VA LID 
PA CKA GE  BODY       VALI D
b ut  y oucan't use the"i nva li d"funct i on (wh ile t he " co rr e ct"  one st i ll w orks ): 
S QL>sel ec t pkg _tes t .f_ right_n ow, pkg_t est . f_ma x_ da tum fro m dual;
selec tpkg_test.f _r igh t_now, p kg_test. f_max_datum from dual
                       * 
ER ROR at  li n e 1:
O RA-009 42: t ab leo r  view d oe s n ot  exist
ORA - 0 651 2: at  " SCOT T.PKG_T EST", line 10

SQL >  se lect pkg_test .f_ righ t _ no w f r om dua l; 
F_ R I GHT_NO
-------- --
2018-10 -0 1 
SQL>

SQL> sel ec t pkg_t est.f_r ight_ n ow, pkg _test.f_m ax
objects where_object_name =_'PKG_TEST';

OBJECT_TYPE   _ __ _STATUS
-------------------_-------
PACKAGE  _  _   _  _VALID
PACKAGE_BODY        INVALID

SQL> select pkg_test.f_right_now, pkg_test.f_max_datum from dual;
select_pkg_test.f_right_now,_pkg_test.f_max_datum from_dual
__    _    _ _       __  _        _     _  ___   _     *
ERROR_at line 1:
ORA-04063: package body "SCOTT.PKG_TEST"_has errors

SQL>

As expected - package body is_now invalid and you can't use_it. So, let's switch to_dynamic SQL:
SQL>_create or replace package body_pkg_test as
 _2 _  function_f_right_now return date is
  3_  _begin
  4   __ return sysdate;
__5_ _ end;
 _6
_ 7__  function f_max_datum return date_is
_ 8     _retval date;
  9__  begin
 10_    _execute immediate'select max(datum) from test' into_retval;
 11_ _   return retval;
 12    end;
 13_ end;
 14  /

Package body_created.

SQL>

See? Oracle doesn't complain_although table_TEST_doesn't exist any_more._Package status_is VALID:
SQL> select object_type, status from user_objects_where object_name_= 'PKG_TEST';

OBJECT_TYPE    _   _STATUS
-------------------_-------
PACKAGE     _     _ VALID
PACKAGE BODY  ___   VALID

but you_can't use the "invalid" function (while_the "correct" one_still works):
SQL> select pkg_test.f_right_now, pkg_test.f_max_datum from dual;
select_pkg_test.f_right_now,_pkg_test.f_max_datum from dual
   __     ___    _     _     *
ERROR at line 1:
ORA-00942:_table or view_does not exist
ORA-06512: at "SCOTT.PKG_TEST",_line_10

SQL>_select pkg_test.f_right_now from dual;

F_RIGHT_NO
----------
2018-10-01

SQL>

SQL> select pkg_test.f_right_now, pkg_test.f_max