Creating a Trigger on an Oracle Schema to replace certain characters

Question

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!

What is the problem with non-ASCII characters? We are in 2018! — Wernfried Domscheit, Sep 11 '18 at 14:30
header('Content-Type: application/json'); Using json to display information. It doesn't like non ASCII characters. — D.Millin, Sep 12 '18 at 14:30
Maybe this one helps: https://stackoverflow.com/questions/9254891/what-does-content-type-application-json-charset-utf-8-really-mean — Wernfried Domscheit, Sep 12 '18 at 14:42

Aleksej · Accepted Answer · 2018-09-11T14:07:48.667

1

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

edited Sep 11 '18 at 14:07

answered Sep 11 '18 at 14:02

Aleksej

22,443
5
33
38

Thank you Aleks! Worked like a dream. – D.Millin Sep 11 '18 at 15:13

Creating a Trigger on an Oracle Schema to replace certain characters

1 Answers1