Usando Bulk Collect para activar y desactivar triggers

Si, es así.
Hoy me encontré con la necesidad de tener que habilitar y desabilitar los triggers de unas tablas ya que tenia que usar una sentencia ALTER para agregar algunas columnas con un default, y por supuesto cuando hacia el update del default, saltaba el trigger que controlaba los updates sobre dicha tabla y fallaba todo. Y encima, eran varios.
En fin, los "ingredientes" que usé fueron los siguientes:

La funcion sys_context.
bulk collect.
execute immediate.

Sobra la primera no voy a ahondar mucho, pero nos devuelve el esquema al que estamos conectamos.
El bulk collect nos permite leer varios registros en una tabla de pl con un único acceso a tarves de dicha instrucción. (eso nos cuenta devjoker)
El execute immediate ejecuta una sentencia sql dinamica.

El bloque pl mas o menos es así:

declare
type triggers_rt is record(trigger_name all_triggers.trigger_name%type);
type triggers_tt is table of triggers_rt index by pls_integer;
v_enabled_triggers triggers_tt;
v_owner varchar2(30) := sys_context('USERENV', 'CURRENT_SCHEMA');
v_sql varchar2(2048);
begin
select trigger_name
bulk collect into v_enabled_triggers
from all_triggers
where owner = v_owner
and table_name = 'COL_SUBTIPOS_GARANTIAS_MOVI'
and status = 'ENABLED';

if v_enabled_triggers.first is not null then
for i in v_enabled_triggers.first .. v_enabled_triggers.last loop
begin
v_sql := 'ALTER TRIGGER' ||v_enabled_triggers(i).trigger_name||' DISABLE';
DBMS_OUTPUT.PUT_LINE('ALTER_TABLE: Disabling trigger '||v_enabled_triggers(i).trigger_name);
execute immediate v_sql;
exception
when OTHERS then
DBMS_OUTPUT.PUT_LINE('ALTER_TABLE: Error while running: '||v_sql);
end;
end loop;
end if;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);
END;
/

En definitiva eso desactivará los triggers de esa tabla sin tener que explicitar toda la sentencia alter para cada trigger de la tabla.

En definitiva, espero que les sea útil.

Marianittens.

Record, Vectores y demas....

En este ejemplo tenemos manejos de un TABLE manejado con regiustros y luego manejado por cursor.

Saludos.

PROCEDURE martin_prueba
IS
CURSOR C_cursor IS
SELECT CAPU_CD_PRODUCTO,
CAPU_CARP_CD_RAMO,
CAPU_DE_PRODUCTO,
CAPU_NU_DURACION_CONTRATO,
CAPU_IN_RECALCULO_RECARGO
FROM CART_PRODUCTOS
WHERE ROWNUM<20
ORDER BY CAPU_CD_PRODUCTO ASC;

TYPE ty_cursor is record ( CAPU_CD_PRODUCTO CART_PRODUCTOS.CAPU_CD_PRODUCTO%TYPE,
CAPU_CARP_CD_RAMO CART_PRODUCTOS.CAPU_CARP_CD_RAMO%TYPE,
CAPU_DE_PRODUCTO CART_PRODUCTOS.CAPU_DE_PRODUCTO%TYPE,
CAPU_NU_DURACION_CONTRATO CART_PRODUCTOS.CAPU_NU_DURACION_CONTRATO%TYPE,
CAPU_IN_RECALCULO_RECARGO CART_PRODUCTOS.CAPU_IN_RECALCULO_RECARGO%TYPE
);

TYPE array_cursor IS TABLE OF C_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
TYPE tb_cursor IS TABLE OF ty_cursor INDEX BY BINARY_INTEGER;
tcursor tb_cursor;
tcursor_array_cursor array_cursor;
i number(10) :=0;
-- q C_cursor;
begin
i:=1;
for q in C_cursor loop
--Aca cargo el record
tcursor(i).CAPU_CD_PRODUCTO:=q.CAPU_CD_PRODUCTO;
tcursor(i).CAPU_CARP_CD_RAMO:=q.CAPU_CARP_CD_RAMO;
tcursor(i).CAPU_DE_PRODUCTO:=q.CAPU_DE_PRODUCTO;
tcursor(i).CAPU_NU_DURACION_CONTRATO:=q.CAPU_NU_DURACION_CONTRATO;
tcursor(i).CAPU_IN_RECALCULO_RECARGO:=q.CAPU_IN_RECALCULO_RECARGO;
--Aca cargo el record

--Aca cargo el table del cursor
tcursor_array_cursor(i).CAPU_CD_PRODUCTO:=q.CAPU_CD_PRODUCTO;
tcursor_array_cursor(i).CAPU_CARP_CD_RAMO:=q.CAPU_CARP_CD_RAMO;
tcursor_array_cursor(i).CAPU_DE_PRODUCTO:=q.CAPU_DE_PRODUCTO;
tcursor_array_cursor(i).CAPU_NU_DURACION_CONTRATO:=q.CAPU_NU_DURACION_CONTRATO;
tcursor_array_cursor(i).CAPU_IN_RECALCULO_RECARGO:=q.CAPU_IN_RECALCULO_RECARGO;
--Aca cargo el table del cursor
i:=i+1;
end loop;

--Esto es usando el cursor y el record--
FOR i IN tcursor.FIRST .. tcursor.LAST LOOP
DBMS_OUTPUT.PUT_LINE('CAPU_CD_PRODUCTO=' || TCURSOR(i).CAPU_CD_PRODUCTO);
DBMS_OUTPUT.PUT_LINE('CAPU_CARP_CD_RAMO=' || TCURSOR(i).CAPU_CARP_CD_RAMO);
DBMS_OUTPUT.PUT_LINE('CAPU_DE_PRODUCTO=' || TCURSOR(i).CAPU_DE_PRODUCTO);
DBMS_OUTPUT.PUT_LINE('CAPU_NU_DURACION_CONTRATO=' || TCURSOR(i).CAPU_NU_DURACION_CONTRATO);
DBMS_OUTPUT.PUT_LINE('CAPU_IN_RECALCULO_RECARGO=' || TCURSOR(i).CAPU_IN_RECALCULO_RECARGO);
END LOOP;
--Esto es usando el cursor y el record--

--Esto es usando el cursor y el table of del cursor--
FOR i IN tcursor_array_cursor.FIRST .. tcursor_array_cursor.LAST LOOP
DBMS_OUTPUT.PUT_LINE('CAPU_CD_PRODUCTO=' || tcursor_array_cursor(i).CAPU_CD_PRODUCTO);
DBMS_OUTPUT.PUT_LINE('CAPU_CARP_CD_RAMO=' || tcursor_array_cursor(i).CAPU_CARP_CD_RAMO);
DBMS_OUTPUT.PUT_LINE('CAPU_DE_PRODUCTO=' || tcursor_array_cursor(i).CAPU_DE_PRODUCTO);
DBMS_OUTPUT.PUT_LINE('CAPU_NU_DURACION_CONTRATO=' || tcursor_array_cursor(i).CAPU_NU_DURACION_CONTRATO);
DBMS_OUTPUT.PUT_LINE('CAPU_IN_RECALCULO_RECARGO=' || tcursor_array_cursor(i).CAPU_IN_RECALCULO_RECARGO);
END LOOP;
--Esto es usando el cursor y el table of del cursor--
end;

Uso del MERGE para INSERT o UPDATE

El MERGE sirve para realizar el update y el insert en una misma sentencia cumpliendose entre 2 tablas una cierta condicion que se declara en el ON de la sentencia MERGE.

--Primero creo una tabla.
CREATE TABLE test1 AS
SELECT owner, object_id , object_name
FROM all_objects
WHERE 1=2; --1=2 es para que a la tabla la cree vacia.

--Aca viene el uso del MERGE.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.object_name = b.object_name
WHEN NOT MATCHED THEN
INSERT (owner, object_id, object_name)
VALUES (b.owner, b.object_id, b.object_name);

En caso de que macheen a.object_id = b.object_id realiza solo el update, sino machea entonces realiza el Insert.

Package con un procedure que devuelve un cursor como parametro....

Este es un package que si bien me parece tonto tiene sus vueltas.
Lo interesante es que tiene un procedure dentro de un package que retorna un cursor que se arma(obviamente) al pasarle un valor en este caso esta hardcodeado....
Y otro tema que me parecio a resaltar es la siguiente, la forma:
FOR c_emp_rec IN c_cur LOOP ....NO ANDA
Con lo cual hay que abrirlo asi.
Si o si hay que abrirlo con un fetch (LOOP FETCH c_cur INTO c_emp_rec).

CREATE OR REPLACE PACKAGE empleados_pkg IS

CURSOR c_emp IS
SELECT capu_carp_cd_ramo, capu_cd_producto
FROM cart_productos
where rownum<10;

TYPE empleados_type IS REF CURSOR
RETURN c_emp%ROWTYPE;

PROCEDURE datos_empleados ( par1 IN VARCHAR2,
cursor1 OUT empleados_type );
PROCEDURE parent;
END empleados_pkg;

create or replace PACKAGE BODY empleados_pkg IS

PROCEDURE datos_empleados ( par1 IN VARCHAR2,
cursor1 OUT empleados_type)
IS
BEGIN
IF par1 IS NOT NULL THEN
OPEN cursor1 FOR SELECT capu_carp_cd_ramo, capu_cd_producto
FROM cart_productos
and capu_cd_producto=par1;
ELSE
OPEN cursor1 FOR SELECT capu_carp_cd_ramo, capu_cd_producto
FROM cart_productos
where rownum<10;
END IF;
END datos_empleados;

PROCEDURE parent IS
c_cur empleados_type;
c_emp_rec c_cur%ROWTYPE;
BEGIN

datos_empleados('123', c_cur);
LOOP
FETCH c_cur INTO c_emp_rec;
EXIT WHEN c_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (c_emp_rec.capu_carp_cd_ramo);
DBMS_OUTPUT.PUT_LINE (c_emp_rec.capu_cd_producto);
END LOOP;
CLOSE c_cur;
/*TENER PRESENTE QUE CON ESTA FORMA DE APERTURA NO ANDA
FOR c_emp_rec IN c_cur LOOP
DMBS_OUTPUT.PUT_LINE('capu_carp_cd_ramo=' || c_emp_rec.capu_carp_cd_ramo);
DMBS_OUTPUT.PUT_LINE('capu_cd_producto=' || c_emp_rec.capu_cd_producto);
END LOOP;
*/
end parent;
END;

--Para probarlo--
begin
empleados_pkg.parent;
end;

Utilizando NUMTOYMINTERVAL

El otro día me encontré con la situación de que debía hacer algunos cálculos de fechas un tanto simples, pero engorrosos y tediosos.
Dada la vagancia que me suele acompañar en todo, cuanto menos, mejor y cuanto menos escriba... bueno... a veces salen cosas buenas.
Revolviendo por ahí me encontré con esta maravillosa funcIón de Oracle llamada NUMTOYMINTERVAL.

Sintaxis:

NUMTOYMINTERVAL ( n , 'char_expr' );

Más o menos lo que debia hacer era algo así como calcular la fecha del último día del mes, pero con una frecuencia que variaba entre años, dias, meses, etc.
En definitiva, eso de sumarle a la fecha los días está bueno, pero me parecía que ya existía una función que hacía eso.

Comenzando a revolver...

Luego de googler mucho, me econtré con esa belleza y me puse a trabajar.
Claro con esto e smas sencillo, porque la función convierte un número n, a un intervalo de mes o año, dependiendo del literal varchar que se le paso como segundo parametro.
Este segundo parametro puede ser
YEAR (año)
MONTH (mes)

Fijense que lo puse en muyúsculas. OJO!!! es CASE SENSITIVE!!!!

Un ejemplo burdo pero útil.

"A la fecha del día sumerle 6 meses"

Claro... una pavada...

perooooo... con esto es mas sencillo:

BR>SELECT SYSDATE
2 FROM DUAL;

SYSDATE
---------
16-DEC-10

BR>SELECT (SYSDATE + NUMTOYMINTERVAL(6,'MONTH')) SYSDATEMAS6
2 FROM DUAL;

SYSDATEMAS6
---------
16-JUN-11


Nada de andar coontando dias, sumando ni dividiendo... directo y al grano.

si le queremos sumar 2 años la usamos de la siguiente manera:

BR>select sysdate + NUMTOYMINTERVAL(2, 'YEAR') FECHAMAS2
2 FROM DUAL;

FECHAMAS2
---------
16-DEC-12

Claro, mi funcion fué así de sencilla:

CREATE OR REPLACE FUNCTION COL_DATE_POST_REVFREQ(vFecha IN DATE, vRevFrec IN VARCHAR2) RETURN DATE IS
vRevFrec2 CHAR;
vFechaRet DATE;
BEGIN

/*Autor: Mariano Dominguez.
fecha: 13/12/2010
proposito: devuelve la fecha segun la frecuencia */

vRevFrec2 := UPPER(vRevFrec);
CASE vRevFrec
WHEN 'D' THEN
vFechaRet := trunc(vFecha + 1);
WHEN 'W' THEN
vFechaRet := trunc(vFecha + 7);
WHEN 'M' THEN
vFechaRet:= trunc(LAST_DAY((vFecha + NUMTOYMINTERVAL(1,'MONTH'))));
WHEN 'Q' THEN
vFechaRet:= trunc(LAST_DAY((vFecha + NUMTOYMINTERVAL(3,'MONTH'))));
WHEN 'A' THEN
vFechaRet:= trunc(LAST_DAY((vFecha + NUMTOYMINTERVAL(1,'YEAR'))));
WHEN 'S' THEN
vFechaRet:= trunc(LAST_DAY((vFecha + NUMTOYMINTERVAL(6,'MONTH'))));
END CASE;

RETURN vFechaRet;


EXCEPTION
WHEN OTHERS THEN
--ERROR
DBMS_OUTPUT.PUT_LINE('ERROR>>'||SQLERRM);
END;
/

Muy sencillo y práctico.
No select explicito y si al uso de asiganaciones.
Si al uso de "case" y no al miedo a las funciones.

Saludos y luego subo a su hermanita... numtodsinterval.