SQL> SQL> -- RPC can not have commit with 'OUT' parameter 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> drop table t2_a_log; Table dropped. SQL> create table t2_a nologging as select * from test2; Table created. SQL> create table t2_a_log(c1 varchar2(200)); Table created. SQL> SQL> -- Commit with OUT parameter SQL> create or replace procedure remote_commit(result out varchar2) is 2 begin 3 result :='OK'; 4 insert into t2_a values('T',7); 5 commit; 6 exception 7 WHEN OTHERS THEN 8 result := SQLERRM; 9 end remote_commit; 10 / Procedure created. SQL> SQL> -- DDL with OUT parameter 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> create or replace procedure remote_commit2 is 2 result varchar2(200); 3 begin 4 result :='OK'; 5 insert into t2_a values('T',7); 6 insert into t2_a values('T',100); 7 exception 8 WHEN OTHERS THEN 9 begin 10 result := SQLERRM; 11 rollback; 12 insert into t2_a_log values(result); 13 end; 14 commit; 15 end; 16 / Procedure created. SQL> SQL> -- Logging DDL errors remotely SQL> create or replace procedure remote_DDL2 as 2 result varchar2(200); 3 begin 4 execute immediate 'truncate table t2_a'; 5 execute immediate 'truncate table t2_a_bogus'; 6 exception 7 WHEN OTHERS THEN 8 begin 9 result := SQLERRM; 10 insert into t2_a_log values(result); 11 commit; 12 end; 13 end; 14 / Procedure created. SQL> SQL> -- Handle exception from the caller routine SQL> create or replace procedure remote_DDL3 as 2 begin 3 execute immediate 'truncate table t2_a_bogus'; 4 end; 5 / Procedure created. SQL> SQL> conn jyang/jyang@phoenix Connected. SQL> alter system flush shared_pool; System altered. SQL> SQL> set autoprint on SQL> set serveroutput on SQL> variable result varchar2(200); SQL> -- ORA-02064: distributed operation not supported SQL> execute remote_ddl@athena( :result ); PL/SQL procedure successfully completed. RESULT -------------------------------------------------------------------------------- ORA-02064: distributed operation not supported SQL> select * from t2_a@athena; C1 C2 -- ---------- b 2 c 3 e 1 K 6 P 0 G 0 ap 9 u 7 8 rows selected. SQL> SQL> -- ORA-02064: distributed operation not supported SQL> variable result varchar2(200); SQL> execute remote_commit@athena( :result ); PL/SQL procedure successfully completed. RESULT -------------------------------------------------------------------------------- ORA-02064: distributed operation not supported SQL> select * from t2_a@athena; C1 C2 -- ---------- b 2 c 3 e 1 K 6 P 0 G 0 ap 9 u 7 T 7 9 rows selected. SQL> SQL> -- ORA-02021: DDL operations are not allowed on a remote database SQL> truncate table t2_a@athena; truncate table t2_a@athena * ERROR at line 1: ORA-02021: DDL operations are not allowed on a remote database SQL> SQL> -- OK here SQL> exec remote_commit2@athena; PL/SQL procedure successfully completed. SQL> -- The first insert was rolled back. SQL> select * from t2_a@athena; C1 C2 -- ---------- b 2 c 3 e 1 K 6 P 0 G 0 ap 9 u 7 8 rows selected. SQL> -- Error from the 2nd insert was captrued SQL> select * from t2_a_log@athena; C1 -------------------------------------------------------------------------------- ORA-01438: value larger than specified precision allows for this column SQL> SQL> -- OK here SQL> exec remote_ddl2@athena; PL/SQL procedure successfully completed. SQL> -- First DDL succeeded SQL> select * from t2_a@athena; no rows selected SQL> -- 2nd DDL failed, error captured in the temp log table SQL> select * from t2_a_log@athena; C1 -------------------------------------------------------------------------------- ORA-01438: value larger than specified precision allows for this column ORA-00942: table or view does not exist SQL> SQL> -- Error message propagated to the caller SQL> -- RPC failed: ORA-00942: table or view does not exist SQL> -- ORA-06512: at SQL> -- "JYANG.REMOTE_DDL3", line 3 SQL> begin 2 remote_ddl3@athena; 3 exception 4 when others then 5 dbms_output.put_line('RPC failed: '||SQLERRM); 6 end; 7 / RPC failed: ORA-00942: table or view does not exist ORA-06512: at "JYANG.REMOTE_DDL3", line 3 PL/SQL procedure successfully completed. SQL> SQL> SQL> spool off