--step 1
--the User ICT is exist in remote database called newd
--the user will create table, view on table,
--and procedure read form the view
--the user will check that all theses objects are valid
--take code from here
drop table students;
/
create table students
(student_id number,
student_name varchar2(50),
dob date
);
/
insert into students(student_id,student_name,dob) values (1,'aya ahmed','1-jan-1980');
insert into students(student_id,student_name,dob) values (2,'sara mahmoud','1-jan-1980');
insert into students(student_id,student_name,dob) values (3,'nabil alail','1-jan-1980');
commit;
select * from students;
-----------------------------------------------------------------------
STUDENT_ID STUDENT_NAME DOB
---------- -------------------- ---------
1 aya ahmed 01-JAN-80
2 sara mahmoud 01-JAN-80
3 nabil alail 01-JAN-80
create or replace view v1_students
as
select * from students;
create or replace procedure print_all_students
is
begin
for i in (select * from v1_students )
loop
dbms_output.put_line(i.student_id||' '||i.student_name);
end loop;
end;
col object_name for a30
select object_name, object_type, created, status, timestamp
from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
-----------------------------------------------------------------------
OBJECT_NAME OBJECT_TYPE CREATED STATUS TIMESTAMP
---------------------- --------------- --------- ------- -------------------
PRINT_ALL_STUDENTS PROCEDURE 31-MAY-24 VALID 2024-05-31:17:54:32
STUDENTS TABLE 31-MAY-24 VALID 2024-05-31:17:52:42
V1_STUDENTS VIEW 31-MAY-24 VALID 2024-05-31:17:54:26
hr 유저로 로그인
--to read any table from the remote db, you should use table_name@db_link
select * from students@READ_REMOTE;
/*
STUDENT_ID STUDENT_NAME DOB
---------- -------------------- ---------
1 aya ahmed 01-JAN-80
2 sara mahmoud 01-JAN-80
3 nabil alail 01-JAN-80
*/
SELECT name, value
FROM v$parameter
WHERE name='remote_dependencies_mode';
/*
NAME VALUE
------------------------------ --------------------
remote_dependencies_mode TIMESTAMP
*/
alter session set remote_dependencies_mode='SIGNATURE';
SELECT name, value
FROM v$parameter
WHERE name='remote_dependencies_mode';
/*
NAME VALUE
------------------------------ --------------------
remote_dependencies_mode SIGNATURE
*/
drop procedure read_from_remote_db;
/
select * from students@READ_REMOTE;
create or replace procedure read_from_remote_db
is
begin
dbms_output.put_line('executing the remote procedure');
print_all_students@READ_REMOTE;
end;
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME OBJECT_TYPE CREATED STATUS TIMESTAMP
---------------------- ------------ --------- ------- -------------------
READ_FROM_REMOTE_DB PROCEDURE 31-MAY-24 VALID 2024-05-31:21:08:28
*/
--now go and recompile the print_all_students without any changes , in ICT
--변경된 사항이 없으므로 hr.read_from_remote_db 프로시져는 정상동작한다.
exec read_from_remote_db
-- executing the remote procedure
ict 유저로 로그인
--now go and recompile the print_all_students and do some changes in parameters
-- 아래와 같이 파라미터를 추가함, 파라미터가 하는 일은 없음, 기능적인 차이는 없음.
create or replace procedure print_all_students (n number)
is
begin
for i in (select * from v1_students )
loop
dbms_output.put_line(i.student_id||' '||i.student_name);
end loop;
end;
hr 유저로 로그인
exec read_from_remote_db
/*
Error report -
ORA-04062: signature of procedure "ICT.PRINT_ALL_STUDENTS" has been changed
ORA-06512: at "HR.READ_FROM_REMOTE_DB", line 5
ORA-06512: at line 1
04062. 00000 - "%s of %s has been changed"
*Cause: Attempt to execute a stored procedure to serve
an RPC stub which specifies a timestamp or signature that is
different from the current timestamp/signature of the procedure.
*Action: Recompile the caller in order to pick up the new timestamp.
*/
-- exec read_from_remote_db 2번 실행하는것으론 상태가 변하지 않는다. 변수가 추가되어서 수정이 필요함.
-- 여러번 실행해도 상태는 비정상으로 유지됨
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME OBJECT_TYPE CREATED STATUS TIMESTAMP
---------------------- ------------ --------- ------- -------------------
READ_FROM_REMOTE_DB PROCEDURE 31-MAY-24 INVALID 2024-05-31:21:17:32
*/
create or replace procedure read_from_remote_db
is
begin
dbms_output.put_line('executing the remote procedure');
print_all_students@READ_REMOTE(1);
end;
-- exec read_from_remote_db 변수부분 수정하고 다시 컴파일함. 상태가 정상으로 변경됨.
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME OBJECT_TYPE CREATED STATUS TIMESTAMP
---------------------- ------------ --------- ------- -------------------
READ_FROM_REMOTE_DB PROCEDURE 31-MAY-24 VALID 2024-05-31:21:22:08
*/
-- 당연히 해당 프로시져도 정상동작함
exec read_from_remote_db
