9/08/2017

Oracle how to truncate table from remote database

When you try to truncate a table on a remote database, you will get the following error: 

ORA-02021: DDL operations are not allowed on a remote database. 
You have a database link to the remote database so you can see objects there and execute them (e.g. procedures, functions, packages, triggers, etc). The solution is to create the following procedure on the remote database, then execute it from the local one.


CREATE OR REPLACE PROCEDURE Trunc_RMT_Table(p_table_name VARCHAR2) AS

   v_sql_error_code PLS_INTEGER;
   v_sql_error_message VARCHAR2(512);

BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;
EXCEPTION
   WHEN OTHERS THEN
      v_sql_error_code := SQLCODE;
      v_sql_error_message := SQLERRM(v_sql_error_code);
      DBMS_OUTPUT.ENABLE(5000);
      DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
      DBMS_OUTPUT.PUT_LINE(v_sql_error_message);
END Truncate_Remote_Table;
/
To execute the procedure, use the following from the local database:
BEGIN
   Trunc_RMT_Table@db_link(remote_table_name);
END;
/

No comments:

Post a Comment

Popular Posts