Reputation: 716
I want to Check the value of data Before insertion in a table.Like ,if things like " or & come in the data,it should ignore it or insert using some escape sequence .
create table test (checkit varchar2(40));
insert into test values("Here is the & test data");
or
insert into test values("Here is the " test data");
Is there a way to do that in oracle? thanks
EDIT
I want to know the solution especially for Characters like & , " etc not for normal data as shown in the example.
Upvotes: 0
Views: 92
Reputation: 24732
You can do it using BEFORE trigger.
Here is an example replacing & with &
and " with "
;
SET DEFINE OFF;
drop table test;
create table test (checkit varchar2(60));
CREATE OR REPLACE TRIGGER validateTest
BEFORE DELETE OR INSERT OR UPDATE ON test
FOR EACH ROW
BEGIN
:new.checkit := replace(:new.checkit, '&', '<e;');
:new.checkit := replace(:new.checkit, '"', '"');
END;
/
insert into test (checkit) values ('Here is the & test "data"');
select * from test;
Output:
drop table test succeeded.
create table succeeded.
TRIGGER validateTest Compiled.
1 rows inserted
CHECKIT
------------------------------------------------------------
Here is the <e; test "data"
1 rows selected
Obviously replacement strings can be whatever.
Upvotes: 1