RE: Trigger - Missing IN or OUT parameter at index: 1
- From: "Bill Coulam" <bill.coulam@xxxxxxxx>
- To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Dec 2004 15:01:42 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
