RE: Trigger - Missing IN or OUT parameter at index: 1

From: Bill Coulam <bill.coulam_at_scgo.com>
Date: Mon, 6 Dec 2004 15:01:42 -0600
Message-ID: <FJEOLLFBELDPHAFFABKMOELPCCAA.bill.coulam@scgo.com>

It would help us to have the whole trigger.

It seems that you are using the trigger to prevent duplicates. Can you not use a unique constraint for that task, trapping the ORA-00001/DUP_VAL_ON_INDEX exception at the application layer and displaying a nice error message to the user?

I'm guessing this is a BEFORE trigger since as an AFTER you'll probably get the ol' mutating table problem. If it is a BEFORE trigger, the evaluation will need to read IF (cnt >= 1), because as-is, it will only raise the error on the third attempt, not the second.

The error number you use in RAISE_APPLICATION_ERROR needs to fall between -20000 and -20999.

Finally, the error message you pasted here isn't from Oracle. It is a Java/JDBC error complaining about you're not having set up the call to the procedure/function properly, recount the number of IN/OUT parameters in the PL/SQL routine you're calling and make sure there is a matching set statement for each. Remember that functions return a value as well.

• bill c.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Lyndon Tiu Sent: Monday, December 06, 2004 10:23 AM To: Oracle-L
Subject: Trigger - Missing IN or OUT parameter at index: 1

Hello guys,

Could use some help here.

1. Below is a trigger body:

declare cnt number;
BEGIN
select count(*) into cnt from xdb_record_types where query_by_default = 1 and xdb_layer_type_fk = :new.xdb_layer_type_fk;

    if cnt > 1 then
    RAISE_APPLICATION_ERROR (

        num=> -1,
        msg=> 'Duplicate query by default for layer fk = ' +
:new.xdb_layer_type_fk);
     end if;

END; 2) I am getting error:

"Missing IN or OUT parameter at index : 1"

Thank you for any suggestions.

--

Lyndon Tiu
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 06 2004 - 15:15:11 CST