Thread: PLS-00306 Error

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

    Join Date
    Jul 2013
    Posts
    5
    Rep Power
    0

    PLS-00306 Error


    I keep getting PLS-00306 error message. Can anybody explain this?

    I am also getting an error that the statement was ignored. Can somebody please assist me with this?

    Thanks
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Probably, but you need to post the sql your getting the error on
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    5
    Rep Power
    0

    Thanks


    Hey thanks for replying.... I have included my procedure and the trigger that I am creating!!!

    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;
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    You are calling your procedure in the trigger like you want to pass it some parameters:
    Code:
    Begin
    INSERTINTOPHYSLOG(:new.flddate, :new.fldtype, :new.fldresult, :new.flddatedue, :new.fldcomment, :new.fldemployee);
    End;
    But your procedure has no input or output parameters defined.

    I don't know your data types but if you want to pass in parameters to your procedure and use them in the code it would look something like this:

    Code:
    create or replace PROCEDURE INSERTINTOPHYSLOG ( flddate      IN varchar2,
                                                    fldtype      IN varchar2,
                                                    fldresult    IN varchar2,
                                                    flddatedue   IN varchar2,
                                                    fldcomment   IN varchar2,
                                                    fldemployee  IN varchar2 )
    is
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    5
    Rep Power
    0
    Should my parameters match the fields that I am calling in my procedure?

    So, should this:

    prec_num long;
    srec_num long;
    drec_num long;
    examtype long;
    fldrec_num long;
    actiondate DATE;
    refuse CHAR;
    examresult long;


    Match this:
    [/FONT][/COLOR]
    Code:
    create or replace PROCEDURE INSERTINTOPHYSLOG ( flddate      IN varchar2,
                                                    fldtype      IN varchar2,
                                                    fldresult    IN varchar2,
                                                    flddatedue   IN varchar2,
                                                    fldcomment   IN varchar2,
                                                    fldemployee  IN varchar2 )
    is
    [/QUOTE]

    I am new to SQL Developer!!!
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Yes the 'IN' type should match the type of field you are passing to the procedure.

IMN logo majestic logo threadwatch logo seochat tools logo