Package not invalidated and provides bad results

View All Public Scripts Login to Run Script

Script Name Package not invalidated and provides bad results
Visibility Unlisted - anyone with the share link can access
Description Package P1 contains a constant that is used as default value for record field. Package P2 uses the record from P1. When we add new constant to the beginning of package P1, P2 is not invalidated and suddenly the default value for the record field is the newly added constant! Corruption! To make oracle work again, we have to manually recompile P2.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor PV
Created Thursday May 09, 2024

Statement 1
```
set serveroutput on
```
```
Unsupported Command
```
Statement 2
```
set feedback off
```
```
Unsupported Command
```

Statement 3

CREATE OR REPLACE PACKAGE p1 AS 
 
   field_default               CONSTANT varchar2(4) := '0000'; 
 
   TYPE typ_rec IS RECORD ( 
     field varchar2(4) default field_default 
   ); 
 
   function get_field_value(p_rec typ_rec) return varchar2; 
 
end;

Package created.

Statement 4

CREATE OR REPLACE PACKAGE BODY p1 AS 
 
   function get_field_value(p_rec typ_rec) return varchar2 
   AS 
   begin 
     return p_rec.field; 
   end; 
 
end;

Package Body created.

Statement 5

CREATE OR REPLACE PACKAGE p2 AS 
 
   function get_field_value return varchar2; 
 
end;

Package created.

Statement 6

CREATE OR REPLACE PACKAGE BODY p2 AS 
 
   function get_field_value return varchar2 
   as 
     l_rec p1.typ_rec; 
   begin 
     return p1.get_field_value(l_rec); 
   end; 
 
end;

Package Body created.

Statement 7
```
PROMPT 
```
```
Unsupported Command
```

Statement 8

PROMPT ==============================================

Unsupported Command

Statement 9

PROMPT P1 and P2 were created, everything is ok

Unsupported Command

Statement 10

PROMPT ==============================================

Unsupported Command

Statement 11

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different! ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
GOOD: values are the same: 0000

Statement 12

CREATE OR REPLACE PACKAGE p1 AS 
 
   dummy                       CONSTANT varchar2(4) := 'XXXX'; 
   field_default               CONSTANT varchar2(4) := '0000'; 
 
   TYPE typ_rec IS RECORD ( 
     field varchar2(4) default field_default 
   ); 
 
   function get_field_value(p_rec typ_rec) return varchar2; 
 
end;

Package created.

Statement 13

alter package p1 compile

Package altered.

Statement 14

select object_name, object_type, status, timestamp from user_objects where object_name like 'P_'


OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP
P1 PACKAGE VALID 2024-05-09:07:58:26
P1 PACKAGE BODY VALID 2024-05-09:07:58:26
P2 PACKAGE VALID 2024-05-09:07:58:25
P2 PACKAGE BODY VALID 2024-05-09:07:58:25

4 rows selected.

Statement 15
```
PROMPT 
```
```
Unsupported Command
```

OBJECT_NAME	OBJECT_TYPE	STATUS	TIMESTAMP
P1	PACKAGE	VALID	2024-05-09:07:58:26
P1	PACKAGE BODY	VALID	2024-05-09:07:58:26
P2	PACKAGE	VALID	2024-05-09:07:58:25
P2	PACKAGE BODY	VALID	2024-05-09:07:58:25

Statement 16

PROMPT ==============================================

Unsupported Command

Statement 17

PROMPT P1 specification was changed, P2 is valid and now returns the value of different constant!

Unsupported Command

Statement 18

PROMPT ==============================================

Unsupported Command

Statement 19

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different: ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
ERROR: values are different: 0000 != XXXX

Statement 20

alter package p2 compile

Package altered.

Statement 21
```
PROMPT 
```
```
Unsupported Command
```

Statement 22

PROMPT ==============================================

Unsupported Command

Statement 23

PROMPT After manual recompile of P2, everything works again

Unsupported Command

Statement 24

PROMPT ==============================================

Unsupported Command

Statement 25

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different! ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
GOOD: values are the same: 0000

Statement 26
```
drop package p1
```
```
Package dropped.
```
Statement 27
```
drop package p2
```
```
Package dropped.
```
Statement 28
```
set serveroutput on
```
```
Unsupported Command
```
Statement 29
```
set feedback off
```
```
Unsupported Command
```

Statement 30

CREATE OR REPLACE PACKAGE p1 AS 
 
   field_default               CONSTANT varchar2(4) := '0000'; 
 
   TYPE typ_rec IS RECORD ( 
     field varchar2(4) default field_default 
   ); 
 
   function get_field_value(p_rec typ_rec) return varchar2; 
 
end;

Package created.

Statement 31

CREATE OR REPLACE PACKAGE BODY p1 AS 
 
   function get_field_value(p_rec typ_rec) return varchar2 
   AS 
   begin 
     return p_rec.field; 
   end; 
 
end;

Package Body created.

Statement 32

CREATE OR REPLACE PACKAGE p2 AS 
 
   function get_field_value return varchar2; 
 
end;

Package created.

Statement 33

CREATE OR REPLACE PACKAGE BODY p2 AS 
 
   function get_field_value return varchar2 
   as 
     l_rec p1.typ_rec; 
   begin 
     return p1.get_field_value(l_rec); 
   end; 
 
end;

Package Body created.

Statement 34

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different! ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
GOOD: values are the same: 0000

Statement 35

CREATE OR REPLACE PACKAGE p1 AS 
 
   dummy                       CONSTANT varchar2(4) := 'XXXX'; 
   field_default               CONSTANT varchar2(4) := '0000'; 
 
   TYPE typ_rec IS RECORD ( 
     field varchar2(4) default field_default 
   ); 
 
   function get_field_value(p_rec typ_rec) return varchar2; 
 
end;

Package created.

Statement 36

alter package p1 compile

Package altered.

Statement 37

select object_name, object_type, status, timestamp from user_objects where object_name like 'P_'


OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP
P1 PACKAGE VALID 2024-05-09:07:59:31
P1 PACKAGE BODY VALID 2024-05-09:07:59:31
P2 PACKAGE VALID 2024-05-09:07:59:30
P2 PACKAGE BODY VALID 2024-05-09:07:59:30

4 rows selected.

OBJECT_NAME	OBJECT_TYPE	STATUS	TIMESTAMP
P1	PACKAGE	VALID	2024-05-09:07:59:31
P1	PACKAGE BODY	VALID	2024-05-09:07:59:31
P2	PACKAGE	VALID	2024-05-09:07:59:30
P2	PACKAGE BODY	VALID	2024-05-09:07:59:30

Statement 38

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different: ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
ERROR: values are different: 0000 != XXXX

Statement 39

alter package p2 compile

Package altered.

Statement 40

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different! ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
GOOD: values are the same: 0000

Statement 41
```
drop package p1
```
```
Package dropped.
```
Statement 42
```
drop package p2
```
```
Package dropped.
```
Statement 43
```
set serveroutput on
```
```
Unsupported Command
```
Statement 44
```
set feedback off
```
```
Unsupported Command
```

Statement 45

CREATE OR REPLACE PACKAGE p1 AS 
 
   field_default               CONSTANT varchar2(4) := '0000'; 
 
   TYPE typ_rec IS RECORD ( 
     field varchar2(4) default field_default 
   ); 
 
   function get_field_value(p_rec typ_rec) return varchar2; 
 
end;

Package created.

Statement 46

CREATE OR REPLACE PACKAGE BODY p1 AS 
 
   function get_field_value(p_rec typ_rec) return varchar2 
   AS 
   begin 
     return p_rec.field; 
   end; 
 
end;

Package Body created.

Statement 47

CREATE OR REPLACE PACKAGE p2 AS 
 
   function get_field_value return varchar2; 
 
end;

Package created.

Statement 48

CREATE OR REPLACE PACKAGE BODY p2 AS 
 
   function get_field_value return varchar2 
   as 
     l_rec p1.typ_rec; 
   begin 
     return p1.get_field_value(l_rec); 
   end; 
 
end;

Package Body created.

Statement 49

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different! ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
GOOD: values are the same: 0000

Statement 50

CREATE OR REPLACE PACKAGE p1 AS 
 
   dummy                       CONSTANT varchar2(4) := 'XXXX'; 
   field_default               CONSTANT varchar2(4) := '0000'; 
 
   TYPE typ_rec IS RECORD ( 
     field varchar2(4) default field_default 
   ); 
 
   function get_field_value(p_rec typ_rec) return varchar2; 
 
end;

Package created.

Statement 51

alter package p1 compile

Package altered.

Statement 52

select object_name, object_type, status, timestamp from user_objects where object_name like 'P_'


OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP
P1 PACKAGE VALID 2024-05-09:08:00:18
P1 PACKAGE BODY VALID 2024-05-09:08:00:18
P2 PACKAGE VALID 2024-05-09:08:00:17
P2 PACKAGE BODY VALID 2024-05-09:08:00:17

4 rows selected.

OBJECT_NAME	OBJECT_TYPE	STATUS	TIMESTAMP
P1	PACKAGE	VALID	2024-05-09:08:00:18
P1	PACKAGE BODY	VALID	2024-05-09:08:00:18
P2	PACKAGE	VALID	2024-05-09:08:00:17
P2	PACKAGE BODY	VALID	2024-05-09:08:00:17

Statement 53

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different: ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
ERROR: values are different: 0000 != XXXX

Statement 54

alter package p2 compile

Package altered.

Statement 55

DECLARE 
  l_rec     p1.typ_rec; 
  l_value_1 varchar2(20); 
  l_value_2 varchar2(20); 
BEGIN 
  l_value_1 := p1.get_field_value(l_rec); 
  l_value_2 := p2.get_field_value(); 
 
  if l_value_1 = l_value_2 
  then 
    dbms_output.put_line('GOOD: values are the same: ' || l_value_1); 
  else 
    dbms_output.put_line('ERROR: values are different! ' || l_value_1 || ' != ' || l_value_2); 
  end if; 
END;

Statement processed.
GOOD: values are the same: 0000

Statement 56
```
drop package p1
```
```
Package dropped.
```
Statement 57
```
drop package p2
```
```
Package dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database