#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    5
    Rep Power
    0

    Calling a Procedure using a Trigger


    Hello. I am fairly new to ORACLE SQL Developer. I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND
    Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'

    Please help...

    Here is my procedure:

    create or replace PROCEDURE INSERTINTOPHYSLOG
    AS
    prec_num long;
    srec_num long;
    drec_num long;
    examtype long;
    fldrec_num long;
    actiondate DATE;
    refuse CHAR;
    examresult long;
    no_data_found EXCEPTION;

    CURSOR c1
    IS
    select distinct(select b.fldnew_char from Auditlog b where b.fldcolumn = 'FLDTYPE' and b.fldrecnum = a.fldrecnum and a.fldaction = 'I') ,
    (select m.fldnew_date from Auditlog m where m.fldcolumn = 'FLDDATE' and m.fldrecnum = a.fldrecnum and a.fldaction = 'I') ,
    (select k.fldnew_char from Auditlog k where k.fldcolumn = 'FLDREFUSE' and k.fldrecnum = a.fldrecnum and a.fldaction = 'I') ,
    c.fldrec_num as "prec_num" , d.fldrec_num as "srec_num" from auditlog a, employee c, employee d, physlog e
    where a.flddate >= (sysdate - 1) and
    a.fldaction = 'I' and
    a.fldtable = 'PHYSLOG' and
    a.fldrecnum = e.fldrec_num and
    e.fldemployee = c.fldrec_num and
    c.fldssn = d.fldssn and
    c.fldrec_num <> d.fldrec_num;

    CURSOR c2
    IS
    select fldemployee from PHYSLOG where fldemployee = srec_num and
    FLDTYPE = examtype and
    FLDDATE = actiondate;

    BEGIN

    open c1;

    FETCH c1 INTO examtype, actiondate, refuse, prec_num, srec_num;
    close c1;

    FOR prec_num in c1 LOOP
    if refuse is null then
    refuse := 'n';
    end if;

    open c2;
    fetch c2 into drec_num;
    IF c2%notfound then
    fldrec_num:= OHMSEQUENCE.NEXTVAL;
    Insert INTO PHYSLOG (FLDREC_NUM,FLDEMPLOYEE, FLDTYPE, FLDDATE, FLDREFUSE, FLDRESULT) VALUES (fldrec_num, srec_num, examtype, actiondate, refuse,'C');
    COMMIT;
    Exit;
    END IF;
    close c2;
    END LOOP;

    END;


    Here is my trigger calling the above procedure:

    create or replace
    TRIGGER ohm.EXAMLOG_INSERTPHYSLOG
    AFTER INSERT or UPDATE of fldtype, flddate, fldresult ON ohm.PHYSLOG for each row
    Begin
    Call INSERTINTOPHYSLOG(:new.flddate, :new.fldtype, :new.fldresult, :new.flddatedue, :new.fldcomment, :new.fldemployee);
    End;
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    5
    Rep Power
    0

    Help


    Can somebody please help? I have been working on this for months now! Thanks!
  4. #3
  5. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    119
    In the procedure definition there are no parameters.

    But while call the same inside the trigger you are passing 6 parameters, hence you are getting the error
    Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'
    http://folding.extremeoverclocking.com/user_summary.php?s=&u=472288

IMN logo majestic logo threadwatch logo seochat tools logo