Trigger to change field to UPPER 1

[BattleDroid42]

Found this trigger some place for upper casing a field but am getting this error on OEM:

PLS-00049: bad bind variable 'NEW.TEXT_COLUMN'

CREATE OR REPLACE TRIGGER UpCase
BEFORE INSERT OR UPDATE ON TableName
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
BEGIN
:new.text_column := uppernew.text_column);
EXCEPTION
WHEN OTHERS THEN
Null;
END FOO;

Any idea why?

 

[Turkbear]

Hi,
Are you trying to run it 'as is'?
You need to substitute actual table and column names..

 

[BattleDroid42]

Yes, of course.

 

[sem]

It's very strange, because I tried your code on 8.0.5, 8.1.7 and 9.2.0 and it works. The only reason I may invent is enclosing code provided into BEGIN..END or other attempt to call it from PL/SQL. If this is the case note, that DDL statements can not be called from pl/sql directly, only through DYNAMIC sql.

Regards, Dima

 

[BattleDroid42]

Got it. Misspelled the column name even after staring right at it 10 times

 

[ddrillich]

Good Day,

In 8.1.6 I got -
ORA-04098: trigger 'UPCASE' is invalid and failed re-validation.

Cheers,
Dan