Which two statements are true regarding procedures?

Question

Procedure PROCESS_EMP references table EMP. Procedure UPDATE_EMP updates rows of table EMP through procedure PROCESS_EMP. There is a remote procedure QUERY_EMP that queries the EMP table through the local procedure PROCESS_EMP. The dependency mode is set to TIMESTAMP in this session. Which two statements are true ?

1) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated.

2) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

3) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

4) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time.

5) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time.

As far as I know about signature is combination of return type and list of arguments.

1) If signature of PROCESS_EMP changes, it has nothing to do with EMP table as it itself is referencing EMP.

2) This is correct option. UPDATE_EMP is using PROCESS_EMP for updation of EMP table. If internal logic of PROCESS_EMP changes, then how it affects UPDATE_EMP ? If UPDATE_EMP is using PROCESS_EMP, then it should be calling it. Changes in internal logic is contained in body which is in PROCESS_EMP, not in UPDATE_EMP.

3) I thought this must be correct. But it is wrong. If the changes in signature are made in PROCESS_EMP. Like if return type or number of arguments or both are modified, then the way of calling would be affected. So, UPDATE_EMP should be affected. Why is it false ?

4) and 5) 4 point is incorrect. I did not get this "first time" and "second time". Why second time ? Why not first time ? 5 point is correct.*

Answer 1

3rd and 5th points are right.

You have the reasons fir the 3rd point.

Reasons for 5th point to be true:

• The dependency mode is set to TIMESTAMP.

• When the internal logic of the procedure PROCESS_EMP is modified and successfully recompiles, its TIMESTAMP changes. You can check that by querying the USER_OBJECTS view.

• All the procedures dependent on PROCESS_EMP are immediately invalidated. You can check this also by querying the USER_OBJECTS view. Hence when these procedures, like UPDATE_EMP, are recompiled when they are called for the first time.

• But any remote procedures, like QUERY_EMP, that are dependent on PROCESS_EMP are not immediately invalidated. These are invalidated after they are invoked for the first time after the dependent procedure is recompiled. Hence QUERY_EMP does not recompile when invoked for the first time. You can check this by querying the USER_OBJECTS view on the remote database before and after invoking QUERY_EMP.

• When QUERY_EMP is invoked for the second time, as its status is invalid, it has to be recompiled and as there is no change in the signature of PROCESS_EMP it successfully recompiles and executes.

For more information read Oracle Docs here and here.

Answer 2

3 and 5.

But don't take my word for it, let's test it.

Create test objects

--Remote dependency mode
alter session set remote_dependencies_mode = timestamp;

--Table
drop table emp purge;
create table emp(id number, name varchar2(100));
insert into emp values(1, 'asdf');
commit;

--Database link - you don't need two databases, you can link to yourself
drop database link myself;
create database link myself connect to <user> identified by <password> using '<database>';
select * from dual@myself;

--Procedures
create or replace procedure process_emp(action_type in varchar2) is
    v_count number;
begin
    if action_type = 'U' then
        update emp set name = upper(name);
    elsif action_type = 'Q' then
        select count(*) into v_count from emp;
    end if;
end;
/

create or replace procedure update_emp is
begin
    process_emp('U');
end;
/

create or replace procedure query_emp is
begin
    process_emp@myself('Q');
end;
/

--Initial object times
select object_name, last_ddl_time, status
from user_objects
where object_name in ('PROCESS_EMP', 'UPDATE_EMP', 'QUERY_EMP')
order by last_ddl_time;

PROCESS_EMP 2013-04-02 00:25:47 VALID
UPDATE_EMP  2013-04-02 00:25:48 VALID
QUERY_EMP   2013-04-02 00:25:49 VALID

Test

1) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated.

False - nothing to even test there. There's no such thing as an invalidated table. (Except maybe for object-relational tables, but I don't think that applies here.)

2) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

False - UPDATE_EMP is still valid, and does not recompile. The LAST_DDL_TIME does not change. Normally, a procedure is only known by its signature - if that doesn't change then there's no need for anything else to change.

create or replace procedure process_emp(action_type in varchar2) is
    v_count number;
begin
    if action_type = 'U' then
        update emp set name = upper(name);
    elsif action_type = 'Q' then
        select count(*)+1 into v_count from emp;
    end if;
end;
/

select object_name, last_ddl_time, status
from user_objects
where object_name in ('PROCESS_EMP', 'UPDATE_EMP', 'QUERY_EMP')
order by last_ddl_time;

UPDATE_EMP  2013-04-02 00:25:48 VALID
QUERY_EMP   2013-04-02 00:25:49 VALID
PROCESS_EMP 2013-04-02 00:29:20 VALID

3) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

True - Changing the parameters potentially changes the way the procedure is called. Most changes will cause a failure, but there are many cases where the dependent procedures will recompile and everything will be fine. For example, if you add a defaulted parameter:

create or replace procedure process_emp(action_type in varchar2, new_param in varchar2 default null) is
    v_count number;
begin
    if action_type = 'U' then
        update emp set name = upper(name);
    elsif action_type = 'Q' then
        select count(*)+1 into v_count from emp;
    end if;
end;
/

--Now the object is invalid:
select status from user_objects where object_name in ('UPDATE_EMP');
INVALID

--But no need to worry, just run it and it will automatically recompile and work correctly:
begin
    update_emp;
end;
/

select status from user_objects where object_name in ('UPDATE_EMP');
VALID

4) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time.

False - QUERY_EMP is still VALID, but will fail when invoked the first time, and will not recompile. In fact, even changing the parameters, as we've done above, will not invalidate this.

select object_name, last_ddl_time, status
from user_objects
where object_name in ('QUERY_EMP')
order by last_ddl_time;

QUERY_EMP   2013-04-02 00:25:49 VALID

begin
    query_emp@myself;
end;
/

ORA-04062: timestamp of procedure "JHELLER.PROCESS_EMP" has been changed
ORA-06512: at "JHELLER.QUERY_EMP", line 3
ORA-06512: at line 2

View program sources of error stack?

5) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time.

True - QUERY_EMP will get invalidated (although not until after the first time), and then recompiles correctly the second time. The query runs, and the LAST_DDL_TIME is now updated.

begin
    query_emp@myself;
end;
/

select object_name, last_ddl_time, status
from user_objects
where object_name in ('QUERY_EMP')
order by last_ddl_time;

QUERY_EMP   2013-04-02 00:37:01 VALID