TRUNCATE : Appel de Procédure Stockée via db link

Alors que le delete, lui, fonctionne sans problème, la commande truncate en tant que DDL ne passe pas au travers d'un db link.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1

Sur la base source :
 
SQL> DELETE FROM toto@versqua;

1 row deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> TRUNCATE TABLE toto@versqua;
TRUNCATE TABLE toto@versqua
                    *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

Il existe une solution qui consiste à disposer une procédure stockée sur la base cible qui effectue le truncate. Cette procédure étant appelée depuis la base source via db link.
 

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1
 

SQL> CREATE PROCEDURE p_u_truncate (p_table IN VARCHAR2)
  2     IS
  3     BEGIN
  4        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table;
  5     END;
  6  /

Procedure created.

Sur la base source :

SQL> EXEC p_u_truncate@versqua('TOTO');

PL/SQL procedure successfully completed.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         0

Explication : la procédure stockée sur la base cible est appelée depuis la base source et s'exécute sur la base cible.

OUT : valeur de retour d'une procedure

Les procédures Oracle offrent la possibilité de renvoyer une valeur en sortie au travers d'une variable OUT définie dans la liste des paramètres de la procédure.

CREATE OR REPLACE PROCEDURE p_sqrt(
    a IN NUMBER, b OUT NUMBER
) AS
BEGIN
    b:=a*a;
END;
/

SET SERVEROUTPUT ON SIZE 2000

DECLARE
  entree NUMBER;
  sortie NUMBER;
BEGIN
  entree:=9;
  p_sqrt(entree,sortie);
  dbms_output.put_line(entree||'² = '||sortie);
END;
/

9² = 81

PL/SQL procedure successfully completed.