ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Truncating a Table from a Remote Database[akadia]

Truncating a Table from a Remote Database[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-05-25 21:48:05 0 删除 编辑

Problem

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

/*
Procedure Name: Truncate_Remote_Table
Purpose: To truncate a table on a local database
from a remote database.This procedure is
executed remotely via a dblink and passed
in the table name that exists on the local
database.
Thanks to: Giovanni Jaramillo
*/
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;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60617/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    240
  • 访问量
    173021