Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

BINARY_CI COLLATION table Trigger error: PLS-00049: bad bind variable 'NEW.C_NAME' Oracle 19c

User_CSOQUAug 28 2020 — edited Aug 29 2020

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

Comments

6363
Ramesh_R wrote:
Hi all,
I have a secenario like to check the old values and new values of a record inside a trigger
like :new.col_name = :old.col_name. The scenario is i need to dynamically iterate thro' all
the column values. because the number of columns is large and keeps on changing in future too.
Can't do that.

Simply add your trigger code to the same source repository as you table creation scripts, and when the table is updated, update and deploy the trigger too.
1 - 1

Post Details

Added on Aug 28 2020
8 comments
1,476 views