DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: 5/1 PL/SQL: SQL Statement ignored 5/56 PL/SQL: ORA-00923: FROM keyword not found

  1. #1
    Join Date
    Feb 2016
    Posts
    1

    5/1 PL/SQL: SQL Statement ignored 5/56 PL/SQL: ORA-00923: FROM keyword not found

    Hello
    I am trying to create a trigger which would raise an application error if the amount entered into the table exceeds the available amount but I am getting errors.

    Here is the trigger:

    Code:
    Create Trigger Check_Availability
       Before Insert
       On Order_Lines
       For Each Row
    Declare
       Quantity_On_Hand Number;
       New_Order_Placed Number;
    Begin
       Select QTY_ON_HAND  INTO QUANTITY_ON_HAND, 
              QTY_ON_ORDER INTO New_Order_Placed
         FROM Product_Inventory
        Where Product_Inventory.Product_ID=:New.Product_ID;
    
       IF Quantity_ON_Hand '<' New_Order_Placed then
          raise_Application_Error (-20001, 'Out of Stock');
       End if;
    End;
    /
    Last edited by gandolf989; 02-08-2016 at 12:21 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    First of all the board for Questions about this forum is not for asking PL/SQL questions.
    You can only have on INTO in a SELECT INTO statement. Your professor might appreciate
    that you added the FOR UPDATE statement to prevent two sessions from claiming the
    same inventory.

    Although, I don't generally use the FOR UPDATE clause, it makes sense if the transaction
    is short and you really want to make sure that you don't sell more inventory that what
    you have on hand. This has the result of serializing the order process so that only one
    person can order a given item at any moment.

    Finally, you need to use code tags, but unfortunately that means that code can be interpreted
    as escape characters. Hence I put single quotes around the less than. You should take them
    out when you copy the code.

    Code:
    CREATE TRIGGER check_availability
       BEFORE INSERT ON order_lines FOR EACH ROW
    DECLARE
       Quantity_On_Hand NUMBER;
       New_Order_Placed NUMBER;
    BEGIN
       SELECT qty_on_hand,      qty_on_order
         INTO quantity_on_hand, qty_on_order
         FROM Product_Inventory
        WHERE Product_Inventory.Product_ID =: NEW.Product_ID
          FOR UPDATE;
    
       IF Quantity_ON_Hand '<' New_Order_Placed 
       THEN
          raise_Application_Error (-20001, 'Out of Stock');
       END IF;
    END;
    /
    Last edited by gandolf989; 02-08-2016 at 12:28 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width