Test the impact of the stored procedure status on execution, and test the Stored Procedure status.

Test the impact of the stored procedure status on execution, and test the Stored Procedure status.
Purpose:

Test the impact of the stored procedure status on the operation of the stored procedure.

Environment preparation:

1. Create a test table test_drop_dep

Create table test_dep (name varchar2 (20 ));
Insert into test_dep (name) values ('abc ');
Commit;
2. Create a stored procedure that depends on the test_drop_dep table.

Create or replace procedure test_drop_dep

As
V_count number;
Begin
Select count (*) into v_count from test_dep;
Dbms_output.put_line ('before sleep time = '| to_char (sysdate, 'hh24: mi: ss '));
Dbms_lock.sleep (30 );
Dbms_output.put_line ('behind sleep time = '| to_char (sysdate, 'hh24: mi: ss '));
Dbms_output.put_line ('the ROWCOUNT = '| to_char (v_count ));
End;
/

Test Case 1:

Session1 executes the stored procedure, session2 deletes the table test_drop_dep, and then queries the status of the stored procedure.

Session1 execute the Stored Procedure test_drop_dep;

Session2: Delete the table test_drop_dep and query the Stored Procedure status.

Drop table test_dep;

Select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';
OBJECT_NAME STATUS
-------------------------------------
TEST_DROP_DEP INVALID

Test Case 1 conclusion:

After the objects that the stored procedure depends on expire (delete), the stored procedure is immediately marked as invalid. Even if the process is being executed, the running stored procedure is executed normally.

Test Case 2: session1 executes the stored procedure, session2 deletes the table test_drop_dep, creates the table test_drop_dep, and executes the stored procedure.

Session1 execute the Stored Procedure test_drop_dep;
/*
SQL> execute test_drop_dep;
Before sleep time = 10:06:47
Behind sleep time = 10:07:17
The rowcount = 1
PL/SQL procedure successfully completed.
*/
Session2: Delete the table test_drop_dep, create the table test_drop_dep, and execute the Stored Procedure

Drop table test_dep;
Select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';
Create table test_dep (name varchar2 (20 ));
Insert into test_dep (name) values ('abc ');
Commit;
Select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';
Execute test_drop_dep;
Select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';

/*

SQL> drop table test_dep;
Table dropped.
SQL> select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';
OBJECT_NAME STATUS
-------------------------------------
TEST_DROP_DEP INVALID
SQL> create table test_dep (name varchar2 (20 ));
Insert into test_dep (name) values ('abc ');
Table created.
SQL> commit;
1 row created.
SQL>
Commit complete.
SQL> select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';
OBJECT_NAME STATUS
-------------------------------------
TEST_DROP_DEP INVALID
SQL> execute test_drop_dep;
Select object_name, status from dba_objects where object_name = 'test _ DROP_DEP ';

Before sleep time = 10:07:17
Behind sleep time = 10:07:47
The rowcount = 1
PL/SQL procedure successfully completed.
SQL>
OBJECT_NAME STATUS
-------------------------------------
TEST_DROP_DEP VALID

*/

Test Case 2 conclusion:

After a valid stored procedure is executed, the execution is completed, regardless of whether the current status is valid;
The first operation of the invalid stored procedure will execute the compilation. If this process is not completed yet, the compilation will remain in the library cache pin waiting until all the processes are completed.