SQL & PL/SQL

Hi,

I have the below tables in my Oracle DB and I am trying to create an After Insert trigger on Book_type table to insert into Book_type_hist table.

desc Book_type;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TYPE_ID                                            NUMBER
TYPE_NAME                                          VARCHAR2(100)
CATEGORY                                           VARCHAR2(100)
ISAVAILABLE                                        NUMBER

desc book_type_hist;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TYPE_ID                                            NUMBER
TYPE_NAME                                          VARCHAR2(100)
CATEGORY                                           VARCHAR2(100)
ISAVAILABLE                                        NUMBER
DATEADDED                                          DATE

Below is trigger script:

CREATE OR REPLACE TRIGGER booktype_inshist

   AFTER INSERT

   ON Book_type

   REFERENCING NEW AS NEW OLD AS OLD

   FOR EACH ROW

BEGIN

   INSERT INTO Book_type_hist (type_ID,

                               Type_Name,

                               Category,

                               IsAvailable,

                               DateAdded)

        VALUES (:new.TYPE_ID,

                :new.TYPE_NAME,

                :new.CATEGORY,

                :new.ISAVAILABLE,

                SYSDATE);

END;

I am getting bad bind variable error on only Varchar2 columns, though there is no change in my column names and it is available in source table.

PLS-00049: bad bind variable 'NEW.TYPE_NAME'

PLS-00049: bad bind variable 'NEW.CATEGORY'

Points to note : Schema DEFAULT_COLLATION is BINARY_AI.

Even if I change the session collation to BINARY or USING_NLS_COMP, I am still ending up with the same error.

Could you please let me know if I am missing something?

Thanks!