Error while writing trigger in Oracle Enterprise Manager Console
Hi,
I am using Oracle Enterprise Manager to write a trigger, but get "Missing in or out parameters at index:: 1" everytime that I want to use :new or :old. I looked around on the internet and it seems to be either a bind variable problem or using preparedStatement instead of Statement problem, but I have no idea how I can use that in Enterprise Manager Console. Does anyone know what I can do to get the Enterprise Manager to accept :new and :old for what they are?
I am using Oracle Enterprise Manager to write a trigger, but get "Missing in or out parameters at index:: 1" everytime that I want to use :new or :old. I looked around on the internet and it seems to be either a bind variable problem or using preparedStatement instead of Statement problem, but I have no idea how I can use that in Enterprise Manager Console. Does anyone know what I can do to get the Enterprise Manager to accept :new and :old for what they are?
Dr_Billy
can you post the code of the trigger ?
ASKER
Hi Dr. Billy,
this is one of the variations that I tried (it really does not matter what I do inside the trigger, the missing parameter error comes out as soon as I use the semicolon for NEW or OLD inside the "trigger body" window):
CREATE OR REPLACE TRIGGER "TEST"."PR_UPDATE" AFTER
UPDATE OF "PSZ" ON "TEST"."TABLE1" FOR EACH ROW BEGIN
var1 VARCHAR2(10)
var1 := :OLD.KEYTAG
INSERT INTO REVTRACK (KEYTAG, CHGCOL)
VALUES ( var1, 'PSZ');
END;
this is one of the variations that I tried (it really does not matter what I do inside the trigger, the missing parameter error comes out as soon as I use the semicolon for NEW or OLD inside the "trigger body" window):
CREATE OR REPLACE TRIGGER "TEST"."PR_UPDATE" AFTER
UPDATE OF "PSZ" ON "TEST"."TABLE1" FOR EACH ROW BEGIN
var1 VARCHAR2(10)
var1 := :OLD.KEYTAG
INSERT INTO REVTRACK (KEYTAG, CHGCOL)
VALUES ( var1, 'PSZ');
END;
This should do it.
CREATE OR REPLACE TRIGGER TEST.PR_UPDATE AFTER
UPDATE OF PSZ ON TEST.TABLE1 FOR EACH ROW
DECLARE
var1 VARCHAR2(10);
BEGIN
var1 := :OLD.KEYTAG;
INSERT INTO REVTRACK (KEYTAG, CHGCOL)
VALUES ( var1, 'PSZ');
END;
CREATE OR REPLACE TRIGGER TEST.PR_UPDATE AFTER
UPDATE OF PSZ ON TEST.TABLE1 FOR EACH ROW
DECLARE
var1 VARCHAR2(10);
BEGIN
var1 := :OLD.KEYTAG;
INSERT INTO REVTRACK (KEYTAG, CHGCOL)
VALUES ( var1, 'PSZ');
END;
ASKER
Hi WallaceAdrian,
I still get the same error about a missing index (attached jpeg file).
Apparently it has to do with variable binding for :OLD (or :NEW) and I don't know how to get around it in Enterprise Manager (I've seen one solution for sql saying to use Statement instead of PreparedStatement, but I don't know how this could be done through Enterprise Manager interface either - I am very new to this) .
Thanks!
errormsg.JPG
I still get the same error about a missing index (attached jpeg file).
Apparently it has to do with variable binding for :OLD (or :NEW) and I don't know how to get around it in Enterprise Manager (I've seen one solution for sql saying to use Statement instead of PreparedStatement, but I don't know how this could be done through Enterprise Manager interface either - I am very new to this) .
Thanks!
errormsg.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi WallaceAdrian,
I only have Enterprise Manager.
Is there a way of getting the values from the record without using :NEW or :OLD (like a function call or something)?
Thanks!
I only have Enterprise Manager.
Is there a way of getting the values from the record without using :NEW or :OLD (like a function call or something)?
Thanks!
You should have the sqlplus.exe in your oracle bin folder.
You could use a stored procedure to record the changes before updating the table.
procedure update_table1(i_table1 in table1%rowtype) is
begin
INSERT INTO REVTRACK (KEYTAG, CHGCOL)
VALUES ( i_table1.psz, 'PSZ');
update table1
set row = i_table1
where pk = i_table1.pk;
end;
You could use a stored procedure to record the changes before updating the table.
procedure update_table1(i_table1 in table1%rowtype) is
begin
INSERT INTO REVTRACK (KEYTAG, CHGCOL)
VALUES ( i_table1.psz, 'PSZ');
update table1
set row = i_table1
where pk = i_table1.pk;
end;
ASKER
well oracle is definitely not my forte. :) This is the first time I'm doing anything with it.
what I am trying to do is update the REVTRACK table whenever a table1.psz is changed - I want to add table1.keytag into revtrack.keytag and concatenate 'psz' into the revtrack.chgcol (what I posted above does not concatenate yet).
So in your stored procedure, you are updating table1, right? Would I just drop the 3 columns to adapt it to what I want?
what I am trying to do is update the REVTRACK table whenever a table1.psz is changed - I want to add table1.keytag into revtrack.keytag and concatenate 'psz' into the revtrack.chgcol (what I posted above does not concatenate yet).
So in your stored procedure, you are updating table1, right? Would I just drop the 3 columns to adapt it to what I want?
The procedure is updating table1. I'm not sure what you mean about dropping the 3 columns?
ASKER
oh sorry, I meant last 3 lines of code (I did not even realize I wrote columns). :)
thanks!
thanks!
ASKER
As you said, using a different interface solved the problem.