CODEWITHSUNDEEP
databaseoracle-databasetriggersascii
D.Millin
D.Millin

Reputation: 69

Creating a Trigger on an Oracle Schema to replace certain characters

I'm having issues with non ASCII characters getting into my database due to user input.

I want to create a trigger on the database so when a specific character is inserted/updated it just replaces it with a blank space.

This is my trigger (I will add multiple characters that are not allowed) :

CREATE OR REPLACE TRIGGER database.incidents_ascci_summary  
BEFORE INSERT OR UPDATE ON database.table
FOR EACH ROW
BEGIN
        if :new.column like '%'||chr(253)||'%' then
           :new.column := REPLACE( :new.column, ''%'||chr(253)||'%'', ' ' );
END if;
END;
/

chr(253) = ý

Which creates the trigger. However, when I run

UPDATE table f
SET f.column = 'Hiýa' 
WHERE f.incidentid = 'INCIDENT#'

I get this error message from Oracle:

ORA-04098: trigger 'INCMAN.INCIDENTS_ASCCI_SUMMARY' is invalid and failed re-validation

Any help would be greatly appreciated!

Upvotes: 0

Views: 207

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You have an issue in :

:new.column := REPLACE( :new.column, ''%'||chr(253)||'%'', ' ' );

It should be:

:new.column := REPLACE( :new.column, chr(253), ' ' );

Also, you don't need to check before using the REPLACE, but you can simply do 

BEGIN
        :new.column := REPLACE( :new.column, chr(253), ' ' );
END;

If you need to handle more than one character, TRANSLATE could be a way; for example:

SELECT TRANSLATE('aaXbbYccZdd', 'XYZ', '   ') from dual

gives:

TRANSLATE('AAXBBYCCZDD','XYZ','')
---------------------------------
aa bb cc dd

Upvotes: 1

Related Questions