SQL> 
SQL> -- RPC problem with ORA-4068
SQL> -- When a remote procedure is recreated after it has been executed from another db,
SQL> --   the first subsequent call to the procedure from this db will fail.
SQL> -- I consider this to be a bug.
SQL> 
SQL> -- Error can be avoided by flushing the shared_pool of the calling db,
SQL> --   or by slightly changing the text of calling the procedure
SQL> --      this will create a new entry in the shared pool
SQL> --   or simply by calling the procedure a 2nd time.
SQL> 
SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> 
SQL> drop table t2_a;
drop table t2_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table t2_a nologging as select * from test2;

Table created.

SQL> 
SQL> create or replace procedure remote_ddl(result out varchar2) is
  2  begin
  3  execute immediate 'truncate table t2_a';
  4  exception
  5  WHEN OTHERS THEN
  6     result := SQLERRM;
  7  end remote_ddl;
  8  /

Procedure created.

SQL> 
SQL> conn jyang/jyang@phoenix
Connected.
SQL> 
SQL> variable result1 varchar2(200);
SQL> -- first time failed with ORA-04068
SQL> execute  remote_ddl@athena(:result1);
BEGIN remote_ddl@athena(:result1); END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded 
ORA-04062: timestamp of procedure "JYANG.REMOTE_DDL" has been changed 
ORA-06512: at line 1 


SQL> 
SQL> -- 2nd time OK
SQL> execute remote_ddl@athena(:result1);

PL/SQL procedure successfully completed.

SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> -- Recompile procedure wi no changes
SQL> alter procedure remote_ddl compile;

Procedure altered.

SQL> 
SQL> conn jyang/jyang@phoenix
Connected.
SQL> 
SQL> variable result1 varchar2(200);
SQL> -- first time OK
SQL> execute remote_ddl@athena(:result1);

PL/SQL procedure successfully completed.

SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> 
SQL> -- Recreate procedure
SQL> create or replace procedure remote_ddl(result out varchar2) is
  2  begin
  3  execute immediate 'truncate table t2_a';
  4  exception
  5  WHEN OTHERS THEN
  6     result := SQLERRM;
  7  end remote_ddl;
  8  /

Procedure created.

SQL> 
SQL> conn jyang/jyang@phoenix
Connected.
SQL> 
SQL> variable result1 varchar2(200);
SQL> -- first time failed
SQL> execute remote_ddl@athena(:result1);
BEGIN remote_ddl@athena(:result1); END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded 
ORA-04062: timestamp of procedure "JYANG.REMOTE_DDL" has been changed 
ORA-06512: at line 1 


SQL> 
SQL> -- 2nd time OK
SQL> execute remote_ddl@athena(:result1);

PL/SQL procedure successfully completed.

SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> 
SQL> -- Recreate procedure
SQL> create or replace procedure remote_ddl(result out varchar2) is
  2  begin
  3  execute immediate 'truncate table t2_a';
  4  exception
  5  WHEN OTHERS THEN
  6     result := SQLERRM;
  7  end remote_ddl;
  8  /

Procedure created.

SQL> 
SQL> conn jyang/jyang@phoenix
Connected.
SQL> 
SQL> variable result1 varchar2(200);
SQL> -- Change the text slightly
SQL> -- first time OK
SQL> execute remote_ddl@athena(  :result1  );

PL/SQL procedure successfully completed.

SQL> 
SQL> select sql_text from v$sqlarea where sql_text like '%remote_ddl%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
BEGIN remote_ddl@athena(:result1); END;                                         
BEGIN remote_ddl@athena(  :result1  ); END;                                     
select sql_text from v$sqlarea where sql_text like '%remote_ddl%'               

SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> 
SQL> -- Recreate procedure
SQL> create or replace procedure remote_ddl(result out varchar2) is
  2  begin
  3  execute immediate 'truncate table t2_a';
  4  exception
  5  WHEN OTHERS THEN
  6     result := SQLERRM;
  7  end remote_ddl;
  8  /

Procedure created.

SQL> 
SQL> conn jyang/jyang@phoenix
Connected.
SQL> 
SQL> -- Flush the shared pool
SQL> alter system flush shared_pool;

System altered.

SQL> 
SQL> variable result1 varchar2(200);
SQL> -- first time OK
SQL> execute remote_ddl@athena(:result1);

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> spool off