Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Weird error using :new in trigger

752032Feb 7 2010 — edited Feb 7 2010
Hi buddies:

I'm using 10g and I've created an after insert trigger:

CREATE OR REPLACE TRIGGER "TR_INSERT_PERSON" AFTER
INSERT ON "PERSON" FOR EACH ROW BEGIN
INSERT INTO CHANGETABLE (ROWGUID, OPERATION, DATEOPERATION, TABLEID) VALUES (:NEW.UUID, 2, SYSDATE, 'PERSON');
END;

When I apply, it shows me the error message: "Missing IN or OUT parameter at index:: 1"
I know the cause of the error is the ':NEW' parameter, because when I replace it with a value it works, but I'm on a trigger body, what's wrong?

Thanx a lot in advance.
This post has been answered by Solomon Yakobson on Feb 7 2010
Jump to Answer

Comments

Hoek
Does :NEW.UUID have any value?
Usually these columns are populated through calls to some SEQUENCE.NEXTVAL value.
How are you populating the column?
Solomon Yakobson
Are you creating trigger using OEM and target database 9i? If so, there is a bug. Use other tool, e.g. SQL*Plus.

SY.
752032
In this case the UUID column is a nvarchar2(100) and is provided in the insert action....

Thanx for your response.
752032
No, I'm using OEM from client 10g and oracle database 10g

Thanx for your response...
Syed Ullah
Try putting a DBMS_OUTPUT.PUT_LINE() in your trigger to print the value of :NEW.UUID
and then do an insert in SQLPLUS to see what value gets printed for :NEW.UUID.
752032
The problem is: I can't save the trigger, it shows me the error message: "Missing IN or OUT parameter at index:: 1" and fails...
I'm working over the OEM
Syed Ullah
Try saving it using SQLPLUS and if that works that may indicate some bug on the OEM side.
752032
When I execute the script:

CREATE OR REPLACE TRIGGER "KIKO"."TR_INSERT_PERSON" AFTER
INSERT ON "KIKO"."PERSON" FOR EACH ROW BEGIN
INSERT INTO CHANGETABLE (ROWGUID, OPERATION, DATEOPERATION, TABLEID) VALUES (:NEW.UUID, 2, (SELECT SYSDATE FROM DUAL), 'PERSON');
END;Connect sys/****@orcl AS SYSDBA

It shows me the warning: "Trigger created with compilation errors."
It seems like a syntax or semantic error
Syed Ullah
Type 'show error' to see the errors.
Solomon Yakobson
Answer
Try the following in SQL*Plus:
CREATE OR REPLACE
  TRIGGER KIKO.TR_INSERT_PERSON
    AFTER INSERT
    ON KIKO.PERSON
    FOR EACH ROW
    BEGIN 
        INSERT
          INTO CHANGETABLE(
                           ROWGUID,
                           OPERATION,
                           DATEOPERATION,
                           TABLEID
                          )
          VALUES (
                  :NEW.UUID,
                  2,
                  SYSDATE,
                  'PERSON'
                 );
END;
/
SHOW ERRORS TRIGGER KIKO.TR_INSERT_PERSON
SY.
Marked as Answer by 752032 · Sep 27 2020
752032
The error message is: "Missing IN or OUT parameter at index:: 1"
752032
Seems like it works!!! Could you explain me what happened???
Solomon Yakobson
user12572223 wrote:
Seems like it works!!! Could you explain me what happened???
Could you provide create table statements for tables PERSON and CHANGETABLE.

SY.
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 7 2010
Added on Feb 7 2010
#error, #parameter, #trigger
13 comments
1,119 views