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 (See ORA-00001.ora-code.com)/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@(protected)
[mailto:oracle-l-bounce@(protected)]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
