GRANT CREATE DATABASE LINK TO RM;
/
CREATE TABLE ZTABELASEXCLUIDAS (NOMETABELA VARCHAR(150),
CONSTRAINT PKZTABELASEXCLUIDAS PRIMARY KEY (NOMETABELA));
/
CREATE TABLE ZALIASORIGEM (NOMEALIAS VARCHAR(60),
CODCOLIGADA NUMBER(5),
CONSTRAINT PKZALIASORIGEM PRIMARY KEY (NOMEALIAS));
/
CREATE OR REPLACE PROCEDURE SP_ATIVAFOREIGNKEYS (P_ACAO IN VARCHAR)
AS
P_NOMETABELA VARCHAR(128);
P_CHAVEESTRANGEIRA VARCHAR(128);
P_CURSORNRO INTEGER;
P_EXECUTE INTEGER;
CURSOR CURSORTABELAS IS SELECT TABLE_NAME
FROM USER_TABLES;
CURSOR CURSORCONSTRAINTS IS SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = P_NOMETABELA
AND CONSTRAINT_TYPE = 'R'; /* SOMENTE FOREIGN KEYS */
BEGIN
OPEN CURSORTABELAS;
FETCH CURSORTABELAS INTO P_NOMETABELA;
LOOP
EXIT WHEN CURSORTABELAS%NOTFOUND;
OPEN CURSORCONSTRAINTS;
FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
LOOP
EXIT WHEN CURSORCONSTRAINTS%NOTFOUND;
P_CURSORNRO := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(P_CURSORNRO, 'ALTER TABLE ' || P_NOMETABELA || ' ' || P_ACAO || ' CONSTRAINT ' || P_CHAVEESTRANGEIRA, DBMS_SQL.NATIVE);
P_EXECUTE :=DBMS_SQL.EXECUTE(P_CURSORNRO);
DBMS_SQL.CLOSE_CURSOR(P_CURSORNRO);
FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
END LOOP;
CLOSE CURSORCONSTRAINTS;
FETCH CURSORTABELAS INTO P_NOMETABELA;
END LOOP;
CLOSE CURSORTABELAS;
END;
/
