Hi,
I have a table with varchar2 column & table is case insensitive with defaut collation binary_ci Oracle 19c.
When I create trigger it show error. But If I create table without collate binary_ci my trigger works fine.
Details with example:
CREATE TABLE Office_Record
(C_ID INTEGER,
C_NAME VARCHAR2(100) Collate binary_ci,
C_Type VARCHAR2(100) Collate binary_ci
)
DEFAULT COLLATION BINARY_CI;
CREATE OR REPLACE TRIGGER TRG_UPD_Office_Record
BEFORE UPDATE ON Office_Record
FOR EACH ROW
BEGIN
:NEW.C_NAME := (CASE
WHEN LENGTH(:NEW.C_ID) = 1 THEN '00' || :NEW.C_NAME
WHEN LENGTH(:NEW.C_ID) = 2 THEN '0' || :NEW.C_NAME
ELSE :NEW.C_NAME
END);
END;
/
Error Shows below:
[Error] PLS-00049 (23: 5): PLS-00049: bad bind variable 'NEW.C_NAME'
[Error] PLS-00049 (25: 44): PLS-00049: bad bind variable 'NEW.C_NAME'
[Error] PLS-00049 (27: 43): PLS-00049: bad bind variable 'NEW.C_NAME'
[Error] PLS-00049 (29: 9): PLS-00049: bad bind variable 'NEW.C_NAME'
Please help
How BINARY_CI COLLATION will work with Trigger oracle 19c?
Thanks
Solaiman