Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 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
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 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_PERSONSY.
Answers
-
Hoek Senior Developer Den Haag, Nederland | The Hague, The NetherlandsMember Posts: 16,089 Gold CrownDoes :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_PERSONSY.
This discussion has been closed.