The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question.
Asked: February 21, 2024 - 6:18 am UTC
Last updated: February 26, 2024 - 5:21 am UTC
Version: Oracle Database 19c
Viewed 1000+ times
SQL> conn scott/tiger@db19s Connected. SQL> CREATE OR REPLACE 2 PROCEDURE referenced_procedure (pi_value IN number) IS 3 BEGIN 4 dbms_output.put_line('referenced_procedure'); 5 END; 6 / Procedure created. SQL> SQL> conn scott/tiger@pdb21a Connected. SQL> set serverout on SQL> ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = 'SIGNATURE'; Session altered. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE dependent_procedure IS 2 BEGIN 3 dbms_output.put_line('dependent_procedure'); 4 referenced_procedure@db19s(5); 5 END; 6 / Procedure created. SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS 2 FROM USER_OBJECTS 3 WHERE OBJECT_NAME IN ('DEPENDENT_PROCEDURE'); OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- ----------------------- ------- DEPENDENT_PROCEDURE PROCEDURE VALID SQL> conn scott/tiger@db19s Connected. SQL> CREATE OR REPLACE 2 PROCEDURE referenced_procedure (pi_value IN int) IS 3 BEGIN 4 dbms_output.put_line('referenced_procedure'); 5 END; 6 / Procedure created. SQL> conn scott/tiger@pdb21a Connected. SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS 2 FROM USER_OBJECTS 3 WHERE OBJECT_NAME IN ('DEPENDENT_PROCEDURE'); OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- ----------------------- ------- DEPENDENT_PROCEDURE PROCEDURE VALID -- note: no change to REMOTE_DEPENDENCIES_MODE , so it is TIMESTAMP here SQL> SQL> exec DEPENDENT_PROCEDURE BEGIN DEPENDENT_PROCEDURE; END; * ERROR at line 1: ORA-04062: timestamp of procedure "SCOTT.REFERENCED_PROCEDURE" has been changed ORA-06512: at "SCOTT.DEPENDENT_PROCEDURE", line 4 ORA-06512: at line 1 ========== SQL> SQL> conn scott/tiger@db19s Connected. SQL> CREATE OR REPLACE 2 PROCEDURE referenced_procedure (pi_value IN number) IS 3 BEGIN 4 dbms_output.put_line('referenced_procedure'); 5 END; 6 / Procedure created. SQL> SQL> conn scott/tiger@pdb21a Connected. SQL> set serverout on SQL> ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = 'SIGNATURE'; Session altered. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE dependent_procedure IS 2 BEGIN 3 dbms_output.put_line('dependent_procedure'); 4 referenced_procedure@db19s(5); 5 END; 6 / Procedure created. SQL> SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS 2 FROM USER_OBJECTS 3 WHERE OBJECT_NAME IN ('DEPENDENT_PROCEDURE'); OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- ----------------------- ------- DEPENDENT_PROCEDURE PROCEDURE VALID SQL> SQL> BEGIN 2 dbms_output.put_line('start'); 3 dependent_procedure; 4 dbms_output.put_line('end'); 5 dbms_output.put_line('-----------------------'); 6 END; 7 / start dependent_procedure end ----------------------- PL/SQL procedure successfully completed. SQL> SQL> conn scott/tiger@db19s Connected. SQL> CREATE OR REPLACE 2 PROCEDURE referenced_procedure (pi_value IN int) IS 3 BEGIN 4 dbms_output.put_line('referenced_procedure'); 5 END; 6 / Procedure created. SQL> SQL> conn scott/tiger@pdb21a Connected. SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS 2 FROM USER_OBJECTS 3 WHERE OBJECT_NAME IN ('DEPENDENT_PROCEDURE'); OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- ----------------------- ------- DEPENDENT_PROCEDURE PROCEDURE VALID SQL> SQL> ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = 'SIGNATURE'; Session altered. SQL> SQL> exec DEPENDENT_PROCEDURE PL/SQL procedure successfully completed. SQL> SQL>
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library