I want to use an argument on a function which has an user-defined datatype, and call the function in a trigger
Date: Sun, 12 Apr 2015 12:33:53 -0700 (PDT)
Message-ID: <184ab4a8-2dc4-432b-9c20-5aedc531e791_at_googlegroups.com>
1.I create my datatype "FechaHistorica"
--1-
create or replace TYPE exper.FechaHistorica as object(
anio integer,
mes integer,
dia integer
);
2- I create a table with 2 columns with my user datatype
CREATE TABLE exper.Persona(
persona varchar(20) not null,
nombre varchar(200) not null,
sexo varchar(1) not null,
fNacimiento ref FechaHistorica,
fMuerte ref FechaHistorica,
generales nclob,
constraint pkPersona primary key( persona ),
constraint chkSexo check( sexo = 'M' or sexo = 'm' or sexo = 'F' or sexo = 'f' )
);
3- In this function, what I'm trying to do is to pass either Fnacimiento or FMuerte. In this function I'm returning the value as 0 if the comprobations mark the value as correct.
--2-
create or replace function Validar(objeto in FechaHistorica)
return integer is
some_variable integer;
vBandera boolean;
begin
if(objeto.anio = null and objeto.mes=null and objeto.dia=null ) then
some_variable:=0;
elsif(objeto.anio = null and objeto.mes<>null and objeto.dia<>null) then
some_variable:=1;
elsif(objeto.mes = null and objeto.dia<>null) then
some_variable:=1;
elsif(objeto.anio<>null and objeto.mes<>null and objeto.dia = null) then
some_variable:=0;
end if;
--Comprabar mes y día.
if(some_variable = 0) then
if((objeto.mes>12) or (objeto.mes <1)) then
some_variable:=1;
elsif((objeto.mes = 02) or (objeto.mes = 2)) then
If objeto.anio Mod 4 = 0 Then
If (objeto.anio Mod 100 = 0) And Not (objeto.anio Mod 400 = 0) Then
vBandera := False;
if(objeto.dia>28) then
some_variable:=1;
END IF;
Else
vBandera := True;
if(objeto.dia>29) then
some_variable:=1; END IF; End If;
Else
vBandera := False;
End If;
End if;
End if;
return some_variable;
end;
4- In this trigger I want to call the function, but I get an error wrong number or types of arguments on the call of the function. What should I do to correct this?
--3-
CREATE OR REPLACE TRIGGER exper.some_trigger
before insert or update on exper.Persona for each row
BEGIN
if Validar(:New.fNacimiento) = 0
then
DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento Aceptada'); Else
raise_application_error(-20001, 'Fecha de Nacimiento no aceptada');
END IF;
if Validar(:New.fMuerte) = 0
then
DBMS_OUTPUT.PUT_LINE('Fecha de Muerte Aceptada');
Else
raise_application_error(-20002, 'Fecha de Muerte no aceptada');
END IF;
END some_trigger;
Received on Sun Apr 12 2015 - 21:33:53 CEST