On AskTom there is this thread:
https://asktom.oracle.com/pls/apex/asktom.search?tag=package-recompile-itself The thread describes the problem how calling a remote procedure (proc@db_link) can result in an ORA-04062 "timestamp of procedure has changed". One of the suggested solutions in that thread is to invoke the RPC dynamically using "execute immediate", catching the ORA-04062, and simply invoking it dynamically again. And that solution works, which leads me to my questions:
How can dynamic invocation of an RPC result in an ORA-04062 in the first place? Docs say "Dynamic SQL statements do not create dependencies." I understand how a
non-dynamic RPC stores a timestamp in the bytecode, but where is this timestamp stored in the dynamic case? And what is "recompiled" to make the second dynamic invocation work?
It's not storing the timestamp in the data dictionary permanently - it's just caching it.
You can observe this by comparing flushing the shared pool. When using dynamic invocation, the second call works. But if you have a static call in the package, clearing the shared pool has no effect. The database still needs to resolve the dependency.
Using the example from the other thread:
SQL> create or replace function f return int is
2 begin
3 return 1;
4 end f;
5 /
Function created.
SQL> create or replace package p is
2 procedure p;
3 end p;
4 /
Package created.
SQL> create or replace package body p is
2 procedure p is
3 begin
4 execute immediate 'begin dbms_output.put_line(f@loopback); end;';
5 end p;
6
7 end p;
8 /
Package body created.
SQL>
SQL> exec p.p;
1
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace function f return int is
2 begin
3 return 2;
4 end f;
5 /
Function created.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> exec p.p;
2
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace package body p is
2 procedure p is
3 begin
4 dbms_output.put_line(f@loopback);
5 end p;
6 end p;
7 /
Package body created.
SQL>
SQL> exec p.p;
2
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace function f return int is
2 begin
3 return 3;
4 end f;
5 /
Function created.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> exec p.p;
BEGIN p.p; END;
*
ERROR at line 1:
ORA-04062: timestamp of function "CHRIS.F" has been changed
ORA-06512: at "CHRIS.P", line 4
ORA-06512: at line 1
SQL> exec p.p;
3
PL/SQL procedure successfully completed.