I want to use an argument on a function which has an user-defined datatype, and call the function in a trigger

From: <ejosue.15_at_gmail.com>
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"

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.


create or replace function Validar(objeto in FechaHistorica) return integer is
some_variable integer;
vBandera boolean;
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
    END IF;
    vBandera := True;
    if(objeto.dia>29) then

        END IF;
      End If;

    vBandera := False;
    End If;
  End if;
End if;
return some_variable;
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?


CREATE OR REPLACE TRIGGER exper.some_trigger   before insert or update on exper.Persona for each row   BEGIN

  if Validar(:New.fNacimiento) = 0
    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
    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

Original text of this message