Dependencies between objects in different databases. : Database « Table « Oracle PL / SQL

1. Oracle PL / SQL
2. Table
3. Database

Dependencies between objects in different databases.

   
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>

   
    
    
  

Related examples in the same category