I am getting Bad bind variable error when i try to create a trigger before insert, up

[akeem]

I need help on this. I want to create a trigger that should fire before update(back end) when an insert occurs on a table(front end)but the back end table has one of it column datatype(NVARCHAR2(2) different from the front end datatype(VARCHAR2(35)table and as such the trigger is not working. So i introduced a case function

IF length(COUNTRY_ORIGIN)= 2 then
   COUNTRY_ORIGIN := :new.COUNTRY_ORIGIN;
       ELSE
           COUNTRY_ORIGIN= null; 

so as to allow update on the backend table but i keep getting bad bind variable.

Here is what my code looks like:

alter session set current_schema=TRADEVIEW2;
 
ALTER TRIGGER "TRADEVIEW2"."TRIG_HEADERS" DISABLE;
 
create or replace trigger TRADEVIEW2.TRIG_PO_HEADERS
    BEFORE INSERT OR UPDATE OR DELETE ON TRADEVIEW2.PO_HEADERS REFERENCING OLD AS old NEW AS new
    FOR EACH ROW
DECLARE
        currId  NUMBER := 0;
        p_err_msg varchar2(400) := '';
        v_module varchar2(12) := '';
        v_COUNTRY_ORIGIN varchar2(2);
        BEGIN
 
            DBMS_OUTPUT.PUT_LINE ('Before classifying the event');
            IF DELETING THEN
               if (:old.LAST_UPD_SRC in ('TX', 'TW')) then
               INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
                  (ID
                 , REFERENCE_NUMBER
                 , BUYER_ID
                 , ENTRY_TIME
                 , PO_CREATE_DATE
                 , CURRENCY_CODE
                 , PO_CONTRACT_NO
                 , PRODUCT_ID
                 , INSTRUMENT_TYPE
                 , LATE_SHIP_DATE
                 , EARLY_SHIP_DATE
                 , SHIPPING_TERMS
                 , SHIPPING_METHOD
                 , SHIP_FROM_PORT
                 , SHIP_TO_PORT
                 , COUNTRY_ORIGIN
                 , PAY_TYPE
                 , FOB_DESCRIPTION
                 , TERMS_TYPE_CODE
                 , SOURCE
                 , EVENT
                 , PICKED_UP
                 , FAILURE_STATUS
                 , TX_PAY_TYPE
                 , GUID
                 , UPLOAD_FILE_NAME
                 )
                 select
                 TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
                 , :old.POREF
                 , :old.APPLICANT_ID
                 , SYSDATE
                 , :old.PO_DATE
                 , :old.CURRENCY_CODE
                 , :old.PO_CONTRACT_NO
                 , null
                 , :old.INSTRUMENT_TYPE
                 , :old.LATE_SHIP_DATE
                 , :old.EARLY_SHIP_DATE
                 , :old.SHIPPING_TERMS
                 , :old.SHIPPING_METHOD
                 , :old.SHIP_FROM_PORT
                 , :old.SHIP_TO_PORT
                 , :old.COUNTRY_ORIGIN
                 , null
                 , :old.FOB_DESCRIPTION
                 , :old.TERMS_TYPE_CODE
                 , 'TX'
                 , 'DELETE'
                 , 'N'
                 , null
                 ,:old.PAYMENT_METHOD
                 ,:old.GUID
                  from dual where not exists (
                 select 1 from TRADEVIEW2.PO_DELETED
                 where POREF = :old.POREF
                 and GUID = :old.GUID
                 and IS_HEADER = 'Y'
                 );
            END;
            /
                  ELSE
               --Insert Logic here for INSERT AND UPDATE
 
               --Handle country_origin length
CASE
    WHEN length(COUNTRY_ORIGIN)= 2 then
        v_COUNTRY_ORIGIN :='new.COUNTRY_ORIGIN'
        new.COUNTRY_ORIGIN :='COUNTRY_ORIGIN';
        else
            v_COUNTRY_ORIGIN :=null;
                :new.COUNTRY_ORIGIN :=null;
                END CASE;
 
 
     --Handle country_origin
IF length(COUNTRY_ORIGIN)= 2 THEN
    update TRADEVIEW2.PO_LINEITEMS
       set COUNTRY_ORIGIN = :new.COUNTRY_ORIGIN 
         where POREF = :new.POREF
          and GUID = :new.GUID
           and (COUNTRY_ORIGIN is null);
    END IF;
 
      --Handle switching of product id
CASE
    WHEN :new.MODULE = 'COLL' then
        v_module := 'COLLECTIONS';
            :new.INSTRUMENT_TYPE := 'IMP-OPEN-ACCNT';
                WHEN :new.MODULE = 'IMLC' then
                 v_module := 'COMM-LC';
                    :new.INSTRUMENT_TYPE :='IMPORT-LC';
                    WHEN :new.MODULE = 'COLLECTIONS' then      --if coming from TW
                    v_module := 'COLLECTIONS';
                    :new.MODULE := 'COLL';
                    WHEN :new.MODULE = 'COMM-LC' then          --if coming from TW
                    v_module := 'COMM-LC';
                    :new.MODULE := 'IMLC';
                    else
                    v_module := null;
                    :new.INSTRUMENT_TYPE := null;
                    END CASE;
 --Handle product
    IF (:new.MODULE is not NULL or :new.MODULE <> '') THEN
     update TRADEVIEW2.PO_LINEITEMS
      set BUYER_PRODUCT_ID = :new.MODULE
       where POREF = :new.POREF
        and GUID = :new.GUID
        and (BUYER_PRODUCT_ID is null or BUYER_PRODUCT_ID = '');
    END IF;
 
IF INSERTING THEN
    if (:new.LAST_UPD_SRC = 'TX') then
      BEGIN
       INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
       (ID
                        , REFERENCE_NUMBER
                         , BUYER_ID
                         , ENTRY_TIME
                         , PO_CREATE_DATE
                         , CURRENCY_CODE
                         , PO_CONTRACT_NO
                         , PRODUCT_ID
                         , INSTRUMENT_TYPE
                         , LATE_SHIP_DATE
                         , EARLY_SHIP_DATE
                         , SHIPPING_TERMS
                         , SHIPPING_METHOD
                         , SHIP_FROM_PORT
                         , SHIP_TO_PORT
                         , COUNTRY_ORIGIN
                         , PAY_TYPE
                         , FOB_DESCRIPTION
                         , TERMS_TYPE_CODE
                         , SOURCE
                         , EVENT
                         , PICKED_UP
                         , FAILURE_STATUS
                         , TX_PAY_TYPE
                         , GUID
                         , UPLOAD_FILE_NAME
                         )
                   VALUES
                         (
                         TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
                         , :new.POREF
                         , :new.APPLICANT_ID
                         , SYSDATE
                         , :new.PO_DATE
                         , :new.CURRENCY_CODE
                         , :new.PO_CONTRACT_NO
                         , v_module
                         , :new.INSTRUMENT_TYPE
                         , :new.LATE_SHIP_DATE
                         , :new.EARLY_SHIP_DATE
                         , :new.SHIPPING_TERMS
                         , :new.SHIPPING_METHOD
                         , :new.SHIP_FROM_PORT
                         , :new.SHIP_TO_PORT
                         , :new.COUNTRY_ORIGIN
                         , null
                         , :new.FOB_DESCRIPTION
                         , :new.TERMS_TYPE_CODE
                         , 'TX'
                         , 'ADD'
                         , 'N'
                         , null
                         , :new.PAYMENT_METHOD
                         , :new.GUID
                         , :new.UPLOAD_FILE_NAME
                          );
                          select TWORKS_R2.SEQ_ITF_TX_PO_HEADER.currval@TWKSUPDATE_LINK into currId from dual;
                          :new.ITF_ID := currId;
                        END;
                        /
-- Insert Logic here for INSERT ONLY
 
IF UPDATING THEN
     if   (:new.LAST_UPD_SRC = 'TX') then
 
         BEGIN
            DBMS_OUTPUT.PUT_LINE ('Normal update block');
                    INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
                     (ID
                     , REFERENCE_NUMBER
                     , BUYER_ID
                     , ENTRY_TIME
                     , PO_CREATE_DATE
                     , CURRENCY_CODE
                     , PO_CONTRACT_NO
                     , PRODUCT_ID
                     , INSTRUMENT_TYPE
                     , LATE_SHIP_DATE
                     , EARLY_SHIP_DATE
                     , SHIPPING_TERMS
                     , SHIPPING_METHOD
                     , SHIP_FROM_PORT
                     , SHIP_TO_PORT
                     , COUNTRY_ORIGIN
                     , PAY_TYPE
                     , FOB_DESCRIPTION
                     , TERMS_TYPE_CODE
                     , SOURCE
                     , EVENT
                     , PICKED_UP
                     , FAILURE_STATUS
                     , TX_PAY_TYPE
                     , GUID
    , UPLOAD_FILE_NAME
                         )
                   VALUES
                        (
                          TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
                           , :new.POREF
                           , :new.APPLICANT_ID
                           , SYSDATE
                           , :new.PO_DATE
                           , :new.CURRENCY_CODE
                           , :new.PO_CONTRACT_NO
                           , v_module
                           , :new.INSTRUMENT_TYPE
                           , :new.LATE_SHIP_DATE
                           , :new.EARLY_SHIP_DATE
                           , :new.SHIPPING_TERMS
                           , :new.SHIPPING_METHOD
                           , :new.SHIP_FROM_PORT
                           , :new.SHIP_TO_PORT
                           , :new.COUNTRY_ORIGIN
                           ,  null
                           , :new.FOB_DESCRIPTION
                           , :new.TERMS_TYPE_CODE
                           , 'TX'
                           , 'UPDATE'
                           , 'N'
                           , null
                           , :new.PAYMENT_METHOD
                           , :new.GUID
                             );
                             select TWORKS_R2.SEQ_ITF_TX_PO_HEADER.currval@TWKSUPDATE_LINK into currId from dual;
                             :new.ITF_ID := currId;
                    END;
                    /
 -- Insert Logic here for UPDATE ONLY
  IF length(COUNTRY_ORIGIN)= 2 then
   v_COUNTRY_ORIGIN := :new.COUNTRY_ORIGIN;
       ELSE
           COUNTRY_ORIGIN= null;
END IF;
 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('Other Errors' || SQLERRM);
       p_err_msg := SUBSTR (SQLERRM, 1, 400);
        INSERT INTO TRADEVIEW2.WF_TX_MSG_ERROR_LOG (TIMESTAMP, SOURCE,MESSAGE, MSG_TEXT)
          VALUES (SYSDATE,'Trigger, PO_HEADERS_EVENT','Other Errors', p_err_msg);
            END;
 
ALTER TRIGGER "TRADEVIEW2"."TRIG_HEADERS" ENABLE;
 
 

This is the error i got:

SQL> show errors
Errors for TRIGGER TRADEVIEW2.TRIG_PO_HEADERS:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
65/7     PLS-00049: bad bind variable 'OLD.UPLOAD_FILE_NAME'
162/28   PLS-00049: bad bind variable 'V_COUNTRY_ORIGIN'
172/10   PLS-00049: bad bind variable 'NEW.UPLOAD_FILE_NAME'
231/40   PLS-00049: bad bind variable 'V_COUNTRY_ORIGIN'
241/13   PLS-00049: bad bind variable 'NEW.UPLOAD_FILE_NAME'
250/13   PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         ( begin case declare else elsif end exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
         insert open rollback savepoint set sql execute commit forall
         merge pipe purge
 
257/12   PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         end not pragma final instantiable order overriding static
         member constructor map