Purpose: To test the impact of the stored procedure status on the operation of the stored procedure. Environment preparation: 1. Create a test table named alias (namevarchar2 (20); insertintotest_dep (name) values (ABC); commit; 2. Create a stored procedure named createorreplaceproc that depends on the test_drop_dep table.
Purpose: To test the impact of the stored procedure status on the operation of the stored procedure. Environment preparation: 1. create test table test_drop_dep create table test_dep (name varchar2 (20); insert into test_dep (name) values (ABC); commit; 2. create or replace proc
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
asv_count number;beginselect 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 executes the Stored Procedure
execute test_drop_dep;/*SQL> execute test_drop_dep;BEFORE SLEEP TIME=10:06:47BEHIND SLEEP TIME=10:07:17THE ROWCOUNT =1PL/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 INVALIDSQL> 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 INVALIDSQL> execute test_drop_dep;select object_name,status from dba_objects where object_name='TEST_DROP_DEP';BEFORE SLEEP TIME=10:07:17BEHIND SLEEP TIME=10:07:47THE ROWCOUNT =1PL/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.