423,350 Members | 2,519 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,350 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Expand|Select|Wrap|Line Numbers
  1. IF length(COUNTRY_ORIGIN)= 2 then
  2.    COUNTRY_ORIGIN := :new.COUNTRY_ORIGIN;
  3.        ELSE
  4.            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:

Expand|Select|Wrap|Line Numbers
  1. alter session set current_schema=TRADEVIEW2;
  2.  
  3. ALTER TRIGGER "TRADEVIEW2"."TRIG_HEADERS" DISABLE;
  4.  
  5. create or replace trigger TRADEVIEW2.TRIG_PO_HEADERS
  6.     BEFORE INSERT OR UPDATE OR DELETE ON TRADEVIEW2.PO_HEADERS REFERENCING OLD AS old NEW AS new
  7.     FOR EACH ROW
  8. DECLARE
  9.         currId  NUMBER := 0;
  10.         p_err_msg varchar2(400) := '';
  11.         v_module varchar2(12) := '';
  12.         v_COUNTRY_ORIGIN varchar2(2);
  13.         BEGIN
  14.  
  15.             DBMS_OUTPUT.PUT_LINE ('Before classifying the event');
  16.             IF DELETING THEN
  17.                if (:old.LAST_UPD_SRC in ('TX', 'TW')) then
  18.                INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
  19.                   (ID
  20.                  , REFERENCE_NUMBER
  21.                  , BUYER_ID
  22.                  , ENTRY_TIME
  23.                  , PO_CREATE_DATE
  24.                  , CURRENCY_CODE
  25.                  , PO_CONTRACT_NO
  26.                  , PRODUCT_ID
  27.                  , INSTRUMENT_TYPE
  28.                  , LATE_SHIP_DATE
  29.                  , EARLY_SHIP_DATE
  30.                  , SHIPPING_TERMS
  31.                  , SHIPPING_METHOD
  32.                  , SHIP_FROM_PORT
  33.                  , SHIP_TO_PORT
  34.                  , COUNTRY_ORIGIN
  35.                  , PAY_TYPE
  36.                  , FOB_DESCRIPTION
  37.                  , TERMS_TYPE_CODE
  38.                  , SOURCE
  39.                  , EVENT
  40.                  , PICKED_UP
  41.                  , FAILURE_STATUS
  42.                  , TX_PAY_TYPE
  43.                  , GUID
  44.                  , UPLOAD_FILE_NAME
  45.                  )
  46.                  select
  47.                  TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
  48.                  , :old.POREF
  49.                  , :old.APPLICANT_ID
  50.                  , SYSDATE
  51.                  , :old.PO_DATE
  52.                  , :old.CURRENCY_CODE
  53.                  , :old.PO_CONTRACT_NO
  54.                  , null
  55.                  , :old.INSTRUMENT_TYPE
  56.                  , :old.LATE_SHIP_DATE
  57.                  , :old.EARLY_SHIP_DATE
  58.                  , :old.SHIPPING_TERMS
  59.                  , :old.SHIPPING_METHOD
  60.                  , :old.SHIP_FROM_PORT
  61.                  , :old.SHIP_TO_PORT
  62.                  , :old.COUNTRY_ORIGIN
  63.                  , null
  64.                  , :old.FOB_DESCRIPTION
  65.                  , :old.TERMS_TYPE_CODE
  66.                  , 'TX'
  67.                  , 'DELETE'
  68.                  , 'N'
  69.                  , null
  70.                  ,:old.PAYMENT_METHOD
  71.                  ,:old.GUID
  72.                   from dual where not exists (
  73.                  select 1 from TRADEVIEW2.PO_DELETED
  74.                  where POREF = :old.POREF
  75.                  and GUID = :old.GUID
  76.                  and IS_HEADER = 'Y'
  77.                  );
  78.             END;
  79.             /
  80.                   ELSE
  81.                --Insert Logic here for INSERT AND UPDATE
  82.  
  83.                --Handle country_origin length
  84. CASE
  85.     WHEN length(COUNTRY_ORIGIN)= 2 then
  86.         v_COUNTRY_ORIGIN :='new.COUNTRY_ORIGIN'
  87.         new.COUNTRY_ORIGIN :='COUNTRY_ORIGIN';
  88.         else
  89.             v_COUNTRY_ORIGIN :=null;
  90.                 :new.COUNTRY_ORIGIN :=null;
  91.                 END CASE;
  92.  
  93.  
  94.      --Handle country_origin
  95. IF length(COUNTRY_ORIGIN)= 2 THEN
  96.     update TRADEVIEW2.PO_LINEITEMS
  97.        set COUNTRY_ORIGIN = :new.COUNTRY_ORIGIN 
  98.          where POREF = :new.POREF
  99.           and GUID = :new.GUID
  100.            and (COUNTRY_ORIGIN is null);
  101.     END IF;
  102.  
  103.       --Handle switching of product id
  104. CASE
  105.     WHEN :new.MODULE = 'COLL' then
  106.         v_module := 'COLLECTIONS';
  107.             :new.INSTRUMENT_TYPE := 'IMP-OPEN-ACCNT';
  108.                 WHEN :new.MODULE = 'IMLC' then
  109.                  v_module := 'COMM-LC';
  110.                     :new.INSTRUMENT_TYPE :='IMPORT-LC';
  111.                     WHEN :new.MODULE = 'COLLECTIONS' then      --if coming from TW
  112.                     v_module := 'COLLECTIONS';
  113.                     :new.MODULE := 'COLL';
  114.                     WHEN :new.MODULE = 'COMM-LC' then          --if coming from TW
  115.                     v_module := 'COMM-LC';
  116.                     :new.MODULE := 'IMLC';
  117.                     else
  118.                     v_module := null;
  119.                     :new.INSTRUMENT_TYPE := null;
  120.                     END CASE;
  121.  --Handle product
  122.     IF (:new.MODULE is not NULL or :new.MODULE <> '') THEN
  123.      update TRADEVIEW2.PO_LINEITEMS
  124.       set BUYER_PRODUCT_ID = :new.MODULE
  125.        where POREF = :new.POREF
  126.         and GUID = :new.GUID
  127.         and (BUYER_PRODUCT_ID is null or BUYER_PRODUCT_ID = '');
  128.     END IF;
  129.  
  130. IF INSERTING THEN
  131.     if (:new.LAST_UPD_SRC = 'TX') then
  132.       BEGIN
  133.        INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
  134.        (ID
  135.                         , REFERENCE_NUMBER
  136.                          , BUYER_ID
  137.                          , ENTRY_TIME
  138.                          , PO_CREATE_DATE
  139.                          , CURRENCY_CODE
  140.                          , PO_CONTRACT_NO
  141.                          , PRODUCT_ID
  142.                          , INSTRUMENT_TYPE
  143.                          , LATE_SHIP_DATE
  144.                          , EARLY_SHIP_DATE
  145.                          , SHIPPING_TERMS
  146.                          , SHIPPING_METHOD
  147.                          , SHIP_FROM_PORT
  148.                          , SHIP_TO_PORT
  149.                          , COUNTRY_ORIGIN
  150.                          , PAY_TYPE
  151.                          , FOB_DESCRIPTION
  152.                          , TERMS_TYPE_CODE
  153.                          , SOURCE
  154.                          , EVENT
  155.                          , PICKED_UP
  156.                          , FAILURE_STATUS
  157.                          , TX_PAY_TYPE
  158.                          , GUID
  159.                          , UPLOAD_FILE_NAME
  160.                          )
  161.                    VALUES
  162.                          (
  163.                          TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
  164.                          , :new.POREF
  165.                          , :new.APPLICANT_ID
  166.                          , SYSDATE
  167.                          , :new.PO_DATE
  168.                          , :new.CURRENCY_CODE
  169.                          , :new.PO_CONTRACT_NO
  170.                          , v_module
  171.                          , :new.INSTRUMENT_TYPE
  172.                          , :new.LATE_SHIP_DATE
  173.                          , :new.EARLY_SHIP_DATE
  174.                          , :new.SHIPPING_TERMS
  175.                          , :new.SHIPPING_METHOD
  176.                          , :new.SHIP_FROM_PORT
  177.                          , :new.SHIP_TO_PORT
  178.                          , :new.COUNTRY_ORIGIN
  179.                          , null
  180.                          , :new.FOB_DESCRIPTION
  181.                          , :new.TERMS_TYPE_CODE
  182.                          , 'TX'
  183.                          , 'ADD'
  184.                          , 'N'
  185.                          , null
  186.                          , :new.PAYMENT_METHOD
  187.                          , :new.GUID
  188.                          , :new.UPLOAD_FILE_NAME
  189.                           );
  190.                           select TWORKS_R2.SEQ_ITF_TX_PO_HEADER.currval@TWKSUPDATE_LINK into currId from dual;
  191.                           :new.ITF_ID := currId;
  192.                         END;
  193.                         /
  194. -- Insert Logic here for INSERT ONLY
  195.  
  196. IF UPDATING THEN
  197.      if   (:new.LAST_UPD_SRC = 'TX') then
  198.  
  199.          BEGIN
  200.             DBMS_OUTPUT.PUT_LINE ('Normal update block');
  201.                     INSERT INTO TWORKS_R2.ITF_TX_PO_HEADER@TWKSUPDATE_LINK
  202.                      (ID
  203.                      , REFERENCE_NUMBER
  204.                      , BUYER_ID
  205.                      , ENTRY_TIME
  206.                      , PO_CREATE_DATE
  207.                      , CURRENCY_CODE
  208.                      , PO_CONTRACT_NO
  209.                      , PRODUCT_ID
  210.                      , INSTRUMENT_TYPE
  211.                      , LATE_SHIP_DATE
  212.                      , EARLY_SHIP_DATE
  213.                      , SHIPPING_TERMS
  214.                      , SHIPPING_METHOD
  215.                      , SHIP_FROM_PORT
  216.                      , SHIP_TO_PORT
  217.                      , COUNTRY_ORIGIN
  218.                      , PAY_TYPE
  219.                      , FOB_DESCRIPTION
  220.                      , TERMS_TYPE_CODE
  221.                      , SOURCE
  222.                      , EVENT
  223.                      , PICKED_UP
  224.                      , FAILURE_STATUS
  225.                      , TX_PAY_TYPE
  226.                      , GUID
  227.     , UPLOAD_FILE_NAME
  228.                          )
  229.                    VALUES
  230.                         (
  231.                           TWORKS_R2.SEQ_ITF_TX_PO_HEADER.nextval@TWKSUPDATE_LINK
  232.                            , :new.POREF
  233.                            , :new.APPLICANT_ID
  234.                            , SYSDATE
  235.                            , :new.PO_DATE
  236.                            , :new.CURRENCY_CODE
  237.                            , :new.PO_CONTRACT_NO
  238.                            , v_module
  239.                            , :new.INSTRUMENT_TYPE
  240.                            , :new.LATE_SHIP_DATE
  241.                            , :new.EARLY_SHIP_DATE
  242.                            , :new.SHIPPING_TERMS
  243.                            , :new.SHIPPING_METHOD
  244.                            , :new.SHIP_FROM_PORT
  245.                            , :new.SHIP_TO_PORT
  246.                            , :new.COUNTRY_ORIGIN
  247.                            ,  null
  248.                            , :new.FOB_DESCRIPTION
  249.                            , :new.TERMS_TYPE_CODE
  250.                            , 'TX'
  251.                            , 'UPDATE'
  252.                            , 'N'
  253.                            , null
  254.                            , :new.PAYMENT_METHOD
  255.                            , :new.GUID
  256.                              );
  257.                              select TWORKS_R2.SEQ_ITF_TX_PO_HEADER.currval@TWKSUPDATE_LINK into currId from dual;
  258.                              :new.ITF_ID := currId;
  259.                     END;
  260.                     /
  261.  -- Insert Logic here for UPDATE ONLY
  262.   IF length(COUNTRY_ORIGIN)= 2 then
  263.    v_COUNTRY_ORIGIN := :new.COUNTRY_ORIGIN;
  264.        ELSE
  265.            COUNTRY_ORIGIN= null;
  266. END IF;
  267.  
  268. EXCEPTION
  269.   WHEN OTHERS THEN
  270.     DBMS_OUTPUT.PUT_LINE ('Other Errors' || SQLERRM);
  271.        p_err_msg := SUBSTR (SQLERRM, 1, 400);
  272.         INSERT INTO TRADEVIEW2.WF_TX_MSG_ERROR_LOG (TIMESTAMP, SOURCE,MESSAGE, MSG_TEXT)
  273.           VALUES (SYSDATE,'Trigger, PO_HEADERS_EVENT','Other Errors', p_err_msg);
  274.             END;
  275.  
  276. ALTER TRIGGER "TRADEVIEW2"."TRIG_HEADERS" ENABLE;
  277.  
  278.  
This is the error i got:

Expand|Select|Wrap|Line Numbers
  1. SQL> show errors
  2. Errors for TRIGGER TRADEVIEW2.TRIG_PO_HEADERS:
  3.  
  4. LINE/COL ERROR
  5. -------- -----------------------------------------------------------------
  6. 65/7     PLS-00049: bad bind variable 'OLD.UPLOAD_FILE_NAME'
  7. 162/28   PLS-00049: bad bind variable 'V_COUNTRY_ORIGIN'
  8. 172/10   PLS-00049: bad bind variable 'NEW.UPLOAD_FILE_NAME'
  9. 231/40   PLS-00049: bad bind variable 'V_COUNTRY_ORIGIN'
  10. 241/13   PLS-00049: bad bind variable 'NEW.UPLOAD_FILE_NAME'
  11. 250/13   PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
  12.          of the following:
  13.          ( begin case declare else elsif end exit for goto if loop mod
  14.          null pragma raise return select update while with
  15.          <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock
  16.  
  17. LINE/COL ERROR
  18. -------- -----------------------------------------------------------------
  19.          insert open rollback savepoint set sql execute commit forall
  20.          merge pipe purge
  21.  
  22. 257/12   PLS-00103: Encountered the symbol "end-of-file" when expecting
  23.          one of the following:
  24.          end not pragma final instantiable order overriding static
  25.          member constructor map
4 Weeks Ago #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.