Mega Code Archive
Dependencies between objects in different databases
<![CDATA[
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE P2 AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('Inside P2!');
4 END P2;
5 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE P1 AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('Inside P1!');
4 P2;
5 END P1;
6 /
SQL>
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ('P1', 'P2');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
P1 TABLE VALID
P1 PACKAGE BODY INVALID
P2 PROCEDURE VALID
P2 PACKAGE BODY INVALID
4 rows selected.
SQL>
SQL>
SQL> ALTER PROCEDURE P2 COMPILE;
Procedure altered.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ('P1', 'P2');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
P1 TABLE VALID
P1 PACKAGE BODY INVALID
P2 PROCEDURE VALID
P2 PACKAGE BODY INVALID
4 rows selected.
SQL>
SQL> CREATE DATABASE LINK loopback USING 'connect_string';
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE P1 AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('Inside P1!');
4 P2@loopback;
5 END P1;
6 /
SQL>
SQL>
SQL> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name IN ('P1', 'P2');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
P1 TABLE VALID
P1 PACKAGE BODY INVALID
P2 PROCEDURE VALID
P2 PACKAGE BODY INVALID
4 rows selected.
SQL>
SQL>
SQL> ALTER PROCEDURE P2 COMPILE;
Procedure altered.
SQL> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name IN ('P1', 'P2');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
P1 TABLE VALID
P1 PACKAGE BODY INVALID
P2 PROCEDURE VALID
P2 PACKAGE BODY INVALID
4 rows selected.
SQL>
]]>