Truncate table from remote database
                                                 Last update (2008-02-17 22:29:17)
                                                                                                                   Date added (2008-02-17 22:27:58)

Summary
If 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 Truncate_Remote_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
   Truncate_Remote_Table@db_link(¡remote_table_name¢);
END;
/
Reviews
Filters
Search