4 Replies Latest reply on Apr 12, 2011 4:56 AM by Tubby

    Getting Errors in PL/SQL - bad bind variable and encountered the symbol...

    854546
      CREATE OR REPLACE TRIGGER update_QOH
      AFTER INSERT ON ORDERLINE
      FOR EACH ROW
      DECLARE
           QOH_PRODUCT PRODUCT.QOH%TYPE;
      BEGIN
           SELECT QOH INTO QOH_PRODUCT FROM PRODUCT WHERE :old.product_no = :new.product_no;
           IF :new.QTY <= :old.QOH THEN
                QOH = :old.QOH - :new.QTY
           ELSE
                send_email(ord_no, 'Backorder');
                
                INSERT INTO BACKORDER (backorder_no_seq.NEXTVAL, :new.product_no, :new.qty, SYSDATE);
                
                INSERT INTO PRODVENDOR (po_no_seq.NEXTVAL, :new.vendor_no, :new.product_no, :new.vend_qty, :new.shipping_method, SYSDATE, NULL, NULL, NULL);

           END IF;
      END;
      /


      -------------------------------------------
      Error(5,17): PLS-00049: bad bind variable 'OLD.QOH'
      Error(6,7): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ;
      Error(6,9): PLS-00049: bad bind variable 'OLD.QOH'
        • 1. Re: Getting Errors in PL/SQL - bad bind variable and encountered the symbol...
          Ora
          This trigger is After Insert Row Trigger. While Insertion, there are only new values and not old values. You cannot use :OLD for this type of trigger.
          • 2. Re: Getting Errors in PL/SQL - bad bind variable and encountered the symbol...
            Frank Kulash
            Hi,

            Welcome to the forum!

            I see 4 mistakes:
            851543 wrote:
            CREATE OR REPLACE TRIGGER update_QOH
            AFTER INSERT ON ORDERLINE
            FOR EACH ROW
            DECLARE
                 QOH_PRODUCT PRODUCT.QOH%TYPE;
            BEGIN
                 SELECT QOH INTO QOH_PRODUCT FROM PRODUCT WHERE :old.product_no = :new.product_no;
                 IF :new.QTY <= :old.QOH THEN
                      QOH = :old.QOH - :new.QTY
            (1) The variable qoh isn't declared.
            (2) Did you mean the assignment operator, :=, rather than the equality operator, = ?
            (3) An assignment statement must end with a semi-colon.
                 ELSE
                      send_email(ord_no, 'Backorder');
            (4) The variable ord_no isn't declared.
                      
                      INSERT INTO BACKORDER (backorder_no_seq.NEXTVAL, :new.product_no, :new.qty, SYSDATE);
                      
                      INSERT INTO PRODVENDOR (po_no_seq.NEXTVAL, :new.vendor_no, :new.product_no, :new.vend_qty, :new.shipping_method, SYSDATE, NULL, NULL, NULL);

                 END IF;
            END;
            /
            While you can reference :NEW and :OLD values in the trigger, it doesn't make any sense to reference the :OLD values. On an INSERT (which is the only time this trigger fires), all :OLD values are NULL.
            >
            -------------------------------------------
            Error(5,17): PLS-00049: bad bind variable 'OLD.QOH'
            Error(6,7): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ;
            Error(6,9): PLS-00049: bad bind variable 'OLD.QOH'
            I don't believe the code you posted is causing these errors. Each of the errors mentioned above would cause a different message. Post the actual code and error messages.

            Whenever you post a question, post all the code necessary for people to re-create the problem and test their ideas.
            In this case, that means CREATE statements for any tables or sequences involved, INSERT statements for any tables that need rows (such as product) as they exist before the INSERT, some INSERT statements for orderline, and the contents of all the tables after each of those INSERTs.
            Always say which version of Oracle you're using.
            • 3. I changed my code as following; however, still getting errors.
              854546
              CREATE OR REPLACE TRIGGER update_QOH
              BEFORE INSERT ON ORDERLINE
              FOR EACH ROW
              DECLARE
                   QOH_PRODUCT PRODUCT.QOH%TYPE;
              ORD_NO ORDERS.ORDER_NO%TYPE;
              BEGIN
                   SELECT QOH INTO QOH_PRODUCT FROM PRODUCT WHERE :old.product_no = :new.product_no;
              SELECT ORDER_NO INTO ORD_NO FROM ORDERLINE WHERE :old.order_no = :new.order_no;
                   IF (:new.QTY <= QOH_PRODUCT) THEN
                        UPDATE PRODUCT SET QOH = QOH_PRODUCT - :new.QTY;
                   ELSE
                        send_email(ord_no, 'Backorder');
              INSERT INTO BACKORDER (backorder_no_seq.NEXTVAL, :new.product_no, :new.qty, SYSDATE);
                        
                        INSERT INTO PRODVENDOR (po_no_seq.NEXTVAL, :new.vendor_no, :new.product_no, :new.vend_qty, :new.shipping_method, SYSDATE, NULL, NULL, NULL);
                   END IF;
              END;
              /

              ______________________________________________________

              Error(11,5): PL/SQL: SQL Statement ignored
              Error(11,54): PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification
              Error(13,3): PL/SQL: SQL Statement ignored
              Error(13,46): PLS-00049: bad bind variable 'NEW.VENDOR_NO'
              Error(13,46): PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification
              Error(13,79): PLS-00049: bad bind variable 'NEW.VEND_QTY'
              Error(13,94): PLS-00049: bad bind variable 'NEW.SHIPPING_METHOD'

              ______________________________________________________________________________________

              The following are my tables:

              Product table (P_no, QOH, etc)

              Orderline table (OL_no, QTY, etc)

              Backorder table (B_no, B_QTY, etc)

              Vendor table (V_no, etc)

              I need to make sure that when a customer buys a product, there is enough QOH in product table, if there is, QOH in Product should be decreased (updated). If not, send email to customer, update the backorder table and the product should be ordered from the vendor.
              • 4. Re: I changed my code as following; however, still getting errors.
                Tubby
                851543 wrote:
                I need to make sure that when a customer buys a product, there is enough QOH in product table, if there is, QOH in Product should be decreased (updated). If not, send email to customer, update the backorder table and the product should be ordered from the vendor.
                Then you want to get rid of this trigger.

                You need to serialize access to the data, you need to do locking. Your trigger won't see changes happening in other sessions, so when you think there is a quantity or 10, and another session is just about to finish it's processing (but hasn't committed yet) where someone purchased the last 10 items, your trigger fires and decrements the quantity by 1 (what the trigger knows about and how it sees the data as it exists in the current session) ... bad news.

                Long story short (too late) you need to think about multi-user scenarios (this code doesn't seem to).

                Another point of concern is
                send_email(ord_no, 'Backorder');
                What do you propose to do when the code following that
                INSERT INTO BACKORDER 
                Fails for some unexpected reason (say you violate a constraint ... or run out of tablespace)?

                Your transaction will roll back, but you've already sent an email (unless the name of the procedure call is misleading). Bad transaction handling.