CREATE TABLE TEST_LOG (A VARCHAR2(10)); 

INSERT TEST_LOG VALUES ('TESTED');

GRANT SELECT ON TEST_LOG to APP_GEN ;

CREATE SYNONYM TEST_LOG FOR APP_COMMON.TEST_LOG; 

SELECT * from TEST_LOG; 

-- it returns the value 

CREATE DATABASE LINK "APPL_LINK"
USING 'APPL' ;

CREATE SYNONYM TEST_LOG FOR TEST_LOG@APPL_LINK;

SELECT * from TEST_LOG;
--it returns the value.

DBMS scheduler  -- > Job action : 
BEGIN 
     PACK_DT.PACK_Process('344'); 
END;

--PACK_DT.PACK_Process
---------------------
CREATE OR REPLACE PACKAGE BODY PACK_DT IS

PROCEDURE PACK_Process (
      cde IN   VARCHAR2
  ) IS
I_ A VARCHAR2(10);

BEGIN
      DBMS_OUTPUT.put_line ('First process');
      SELECT A INTO I_A from TEST_LOG;
      DBMS_OUTPUT.put_line (I_A);

EXCEPTION
      WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('Error process');
      END;
END;

SYS@orcl_11gR2> create user app_common_db identified by app_common_db
  2  /

User created.

SYS@orcl_11gR2> create user app_gen_db identified by app_gen_db
  2  /

User created.

SYS@orcl_11gR2> grant connect, resource to app_common_db, app_gen_db
  2  /

Grant succeeded.

SYS@orcl_11gR2> grant create database link to app_gen_db
  2  /

Grant succeeded.

SYS@orcl_11gR2> grant create synonym to app_gen_db
  2  /

Grant succeeded.

SYS@orcl_11gR2> grant create job to app_gen_db
  2  /

Grant succeeded.

SYS@orcl_11gR2> grant execute on dbms_lock to app_gen_db
  2  /

Grant succeeded.

SYS@orcl_11gR2> connect app_common_db/app_common_db
Connected.
APP_COMMON_DB@orcl_11gR2> CREATE TABLE TEST_LOG (A VARCHAR2(10))
  2  /

Table created.

APP_COMMON_DB@orcl_11gR2> INSERT INTO TEST_LOG VALUES ('TESTED')
  2  /

1 row created.

APP_COMMON_DB@orcl_11gR2> GRANT SELECT ON TEST_LOG to APP_GEN_DB
  2  /

Grant succeeded.

APP_COMMON_DB@orcl_11gR2> grant insert on test_log to app_gen_db
  2  /

Grant succeeded.

APP_COMMON_DB@orcl_11gR2> connect app_gen_db/app_gen_db
Connected.
APP_GEN_DB@orcl_11gR2> CREATE DATABASE LINK orcl@loopback
  2  connect to app_common_db
  3  identified by app_common_db
  4  USING 'orcl'
  5  /

Database link created.

APP_GEN_DB@orcl_11gR2> CREATE SYNONYM TEST_LOG FOR TEST_LOG@orcl@loopback
  2  /

Synonym created.

APP_GEN_DB@orcl_11gR2> SELECT * from TEST_LOG
  2  /

A
----------
TESTED

1 row selected.

APP_GEN_DB@orcl_11gR2> CREATE OR REPLACE PACKAGE PACK_DT
  2  IS
  3    PROCEDURE PACK_Process
  4  	 (cde IN VARCHAR2);
  5  END PACK_DT;
  6  /

Package created.

APP_GEN_DB@orcl_11gR2> CREATE OR REPLACE PACKAGE BODY PACK_DT
  2  IS
  3    PROCEDURE PACK_Process
  4  	 (cde IN VARCHAR2)
  5    IS
  6  	 I_A	 VARCHAR2(10);
  7    BEGIN
  8  	 DBMS_OUTPUT.put_line ('First process');
  9  	 SELECT A INTO I_A from TEST_LOG where rownum = 1;
 10  	 DBMS_OUTPUT.put_line (I_A);
 11  	 insert into test_log values (cde);
 12  	 commit;
 13    EXCEPTION
 14  	 WHEN OTHERS THEN
 15  	   DBMS_OUTPUT.put_line ('Error process');
 16    END PACK_Process;
 17  END PACK_DT;
 18  /

Package body created.

APP_GEN_DB@orcl_11gR2> exec pack_dt.pack_process ('testing')
First process
TESTED

PL/SQL procedure successfully completed.

APP_GEN_DB@orcl_11gR2> begin
  2    dbms_scheduler.create_job
  3  	 (job_name   => 'test_job',
  4  	  job_type   => 'PLSQL_BLOCK',
  5  	  job_action => 'BEGIN PACK_DT.PACK_Process (''344''); END;',
  6  	  enabled    => TRUE);
  7  end;
  8  /

PL/SQL procedure successfully completed.

APP_GEN_DB@orcl_11gR2> exec dbms_lock.sleep (10)

PL/SQL procedure successfully completed.

APP_GEN_DB@orcl_11gR2> exec null

PL/SQL procedure successfully completed.

APP_GEN_DB@orcl_11gR2> select * from test_log
  2  /

A
----------
TESTED
testing
344

3 rows selected.

APP_GEN_DB@orcl_11gR2>