DECLARE
vView VARCHAR2(32767);
vTrg VARCHAR2(32767);
vKey VARCHAR2(32767);
vKey1 VARCHAR2(32767);
vUpd VARCHAR2(32767);
vDel VARCHAR2(32767);
vIns VARCHAR2(32767);
vVals VARCHAR2(32767);
vPriv VARCHAR2(32767);
vWhr VARCHAR2(32767);

BEGIN

-- Localizar las tablas.
FOR I IN (SELECT UC.TABLE_NAME, COUNT(UC.TABLE_NAME)
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
AND upper(UC.OWNER) = 'TEST'
HAVING COUNT(UC.TABLE_NAME) > 2
GROUP BY UC.TABLE_NAME) LOOP

-- Se empieza a crear los scripts.
vView  := 'CREATE OR REPLACE VIEW ' || SUBSTR(I.TABLE_NAME,1,24) || '_V AS ' || CHR(10) || ' SELECT ';
vTrg := 'CREATE OR REPLACE TRIGGER ' || SUBSTR(I.TABLE_NAME,1,24) || '_V_TRG ' || CHR(10) || ' INSTEAD OF UPDATE OR DELETE OR INSERT ON ' || I.TABLE_NAME || '_V ' || CHR(10) || ' /* ' || CHR(10) || ' ** Este trigger ha sido generado de manera automatica ' || CHR(10) || ' */ ' || CHR(10) || 'BEGIN ' || CHR(10) || ' IF UPDATING THEN ' || CHR(10) || '   UPDATE ' || I.TABLE_NAME || ' ' || CHR(10);

-- Ahora veamos como está definida cada columna. Primero limpiemos las variables.
vKey  := NULL;
vKey1 := NULL;
vUpd := NULL;
vDel := NULL;
vVals := NULL;
vWhr := NULL;


FOR J IN (SELECT COLUMN_NAME
 FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
 AND UC.CONSTRAINT_TYPE = 'P'
 AND UC.TABLE_NAME = I.TABLE_NAME) LOOP


 -- vKey contiene las columnas concatenadas con un
 -- punto entre ellas. Más tarde verá por qué.
 IF vKey IS NULL THEN
  vKey := to_char(J.COLUMN_NAME);
 ELSE
  vKey := vKey || '||''.''||' || to_char(J.COLUMN_NAME);
 END IF;

 -- vDel es para la parte de borrado.
 IF vDel IS NULL THEN
  vDel := CHR(10) || '   DELETE ' || I.TABLE_NAME || CHR(10) || '   WHERE ' || J.COLUMN_NAME || ' = :OLD.' || J.COLUMN_NAME || CHR(10);
 ELSE
  vDel := vDel || '   AND ' || J.COLUMN_NAME || ' = :OLD.' || J.COLUMN_NAME || CHR(10);
 END IF;

 -- vWhr es la claúsula where para la actualización.
 IF vWhr IS NULL THEN
  vWhr := CHR(10) || 'WHERE ' || J.COLUMN_NAME || ' = :OLD.' || J.COLUMN_NAME || CHR(10);
 ELSE
  vWhr := vWhr || ' AND ' || J.COLUMN_NAME || ' = :OLD.' || J.COLUMN_NAME || CHR(10);
 END IF;
END LOOP;

-- Ahora obtengamos todas las columnas de la tabla.
FOR J IN (SELECT COLUMN_NAME
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = I.TABLE_NAME) LOOP

 -- vKey1 Contiene las columnas separadas por comas.
 IF vKey1 IS NULL THEN
  vKey1 := J.COLUMN_NAME;
 ELSE
  vKey1 := vKey1 || ', ' || CHR(10) || J.COLUMN_NAME;
 END IF;

 -- vVals se va a utilizar en la sección de insersiones.
 IF vVals IS NULL THEN
  vVals := '  :NEW.' || J.COLUMN_NAME;
 ELSE
  vVals := vVals || ',' || CHR(10) || '  :NEW.' || J.COLUMN_NAME;
 END IF;

 -- vUpd es para la parte de actualizaciones.
 IF vUpd IS NULL THEN
  vUpd := '   SET ' || J.COLUMN_NAME || ' = :NEW.' || J.COLUMN_NAME || ' ';
 ELSE
  vUpd := vUpd ||', ' || CHR(10) || '   ' || J.COLUMN_NAME || ' = :NEW.' || J.COLUMN_NAME || ' ';
 END IF;

END LOOP;


-- Ahora que se tienen todas las partes, lo que se debe hacer es unir todas ellas.
vView  := vView || ' ' || vKey || ' VIEW_ID, ' || CHR(10) || vKey1 || CHR(10) || ' FROM ' || I.TABLE_NAME;
vTrg := vTrg || vUpd || vWhr || '; ' || CHR(10) || ' ELSIF DELETING THEN' || vDel || '   ; ' || CHR(10) || ' ELSE /* inserting */ ' || CHR(10) || '   INSERT INTO ' || I.TABLE_NAME || CHR(10) || '   (' || vKey1 || ') ' || CHR(10) || '   VALUES (' || vVals || '); ' || CHR(10) || ' END IF;' || CHR(10) || 'END;';

-- Finalmente, se ejecutan las sentencias.
EXECUTE IMMEDIATE vView;
EXECUTE IMMEDIATE vTrg;

-- Antes de finalizar necesitamos otorgar permisos en la vista creada.
FOR J IN (SELECT GRANTEE, PRIVILEGE
 FROM DBA_TAB_PRIVS
 WHERE TABLE_NAME = I.TABLE_NAME
 AND PRIVILEGE IN ('SELECT','UPDATE','DELETE')) LOOP

 vPriv := 'GRANT ' || J.PRIVILEGE || ' ON ' || SUBSTR(I.TABLE_NAME,1,24) || '_V TO ' || J.GRANTEE;
 EXECUTE IMMEDIATE vPriv;
END LOOP;
END LOOP;
END;
/