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.
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; /
