Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Weird error using :new in trigger

752032
Member Posts: 26
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.
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.
Best 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.
Answers
-
Does :NEW.UUID have any value?
Usually these columns are populated through calls to some SEQUENCE.NEXTVAL value.
How are you populating the column? -
Are you creating trigger using OEM and target database 9i? If so, there is a bug. Use other tool, e.g. SQL*Plus.
SY. -
In this case the UUID column is a nvarchar2(100) and is provided in the insert action....
Thanx for your response. -
No, I'm using OEM from client 10g and oracle database 10g
Thanx for your response... -
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. -
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 -
Try saving it using SQLPLUS and if that works that may indicate some bug on the OEM side.
-
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 -
Type 'show error' to see the errors.
-
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.
This discussion has been closed.