You Asked

Dear AskTom team,

I am happy that you again available for questions :-)

I was studying the 'Database Development Guide - 26 Understanding Schema Object Dependency' and focused on the topic '26.10.2 RPC-Signature Dependency Mode'. There is written: 'Changing the data type of a parameter to another data type in the same class does not change the RPC signature, but changing the data type to a data type in another class does.' After studying I tried it out on LiveSQL. Sadly the dependent object always gets invalid after I changed the parameter of the referenced object to another data type in the same class (eg from 'number' to 'integer') - refer to my LiveSQL link.

I tried to understand it but I didn't.
What do I wrong here? Or did I got the documentation wrong?

Thanks for your support!

Greetings, Walter

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/ba4rv4p3uh01mj5e7wq7qrc41

and Connor said...

26.10.2 is about Remote Procedure Call management, ie, calling procedures across a database link.

Here's your example (simplified) slightly running across databases to show the difference

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>

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

PL/SQL demos

PL/SQL docs

Object Dependency with RPC-Signature Dependency Mode

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

PL/SQL demos

PL/SQL docs