Hi I have a problem with the error: Subscription beyond count. I have a procedure that creates an order (for the clarity of the code this part is removed). Procedure picks the products from the basket and then lowers the quantity of the particular product in the warehouses (there can be 1 or more warehouses) in the loop. Can you please explain what am I doing wrong? Thanks a lot.

Here is the body of my procedure:

PROCEDURE cpr_create_order (pin_intCustomer       IN customer.customer_id%TYPE,
                            pin_strPayment        IN payment.payment_type%TYPE,
                            pin_strTransp         IN transp.transp_type%TYPE,
                            pout_strErrorCode     OUT NUMBER,
                            pout_strErrorMessage  OUT VARCHAR2)
IS

    lv_warehouse_balance    NUMBER;
    lv_intQuantity          NUMBER;
    lv_intOrder             NUMBER;
    
    TYPE collection_list IS TABLE OF NUMBER;
    lv_products     collection_list := collection_list();
    lv_quantities   collection_list := collection_list();
    
    lv_warehouses   collection_list := collection_list();
    lv_balances     collection_list := collection_list();
    
BEGIN
    pout_strErrorCode    := 0;
    pout_strErrorMessage := '';
    
    SAVEPOINT S1;
        
        -- fill collections of products and quantities from basket
        SELECT product_id, basket_quantity
        BULK COLLECT INTO lv_products, lv_quantities
        FROM basket
        WHERE order_id IS NULL
        ORDER BY product_id;
        
        FOR i IN 1.. lv_products.COUNT LOOP    -- 1
            
             -- fill collection of warehouses and actual product balances
             SELECT warehouse_id, product_balance  --1,2   -- 5,10
             BULK COLLECT INTO lv_warehouses, lv_balances
             FROM warehouse
             WHERE product_id = lv_products(i)
             ORDER BY warehouse_id;
             
             lv_intQuantity := lv_quantities(i);   --8
             
             FOR j IN 1..lv_warehouses.COUNT LOOP
                IF lv_intQuantity > 0 THEN
                    SELECT product_balance
                    INTO lv_warehouse_balance
                    FROM warehouse
                    WHERE product_id = lv_products(i)
                    AND warehouse_id = lv_warehouses(j);
                    
                    -- lower the balance of the product at the warehouse by the given quantity
                    IF lv_intQuantity < lv_warehouse_balance THEN   --8 --10
                        UPDATE warehouse
                        SET product_balance = product_balance - lv_intQuantity
                        WHERE product_id = lv_products(i)
                        AND warehouse_id = lv_warehouses(j);
                        
                        lv_intQuantity := lv_intQuantity - lv_intQuantity; -- 0
                    
                    -- remove product from the warehouse    
                    ELSIF lv_intQuantity >= lv_warehouse_balance THEN
                        DELETE FROM warehouse
                        WHERE product_id = lv_products(i)
                        AND warehouse_id = lv_warehouses(j);
                        
                        lv_intQuantity := lv_intQuantity - lv_balances(i);
                        
                    END IF;
                END IF;
             END LOOP;  
        END LOOP;     
    
EXCEPTION
    WHEN OTHERS THEN
      pout_strErrorCode := -1;
      pout_strErrorMessage  := substr(SQLERRM,
                                instr(SQLERRM, 'ORA')   11,
                                length(SQLERRM));
      ROLLBACK TO S1;
END cpr_create_order;

CodePudding user response：

This line:

lv_intQuantity := lv_intQuantity - lv_balances(i);

should be:

lv_intQuantity := lv_intQuantity - lv_balances(j);

The lv_products and lv_quantities collections are populated by the first bulk collect, so they have the same number of members, and should be indexed by the same variable, i - which you are doing.

The lv_warehouses and lv_balances collections are populated by the second bulk collect, so they have the same number of members, and should be indexed by the same variable, j.

You should not refer to lv_balances(i) - it will give a result sometimes but it won't be what you expect, and when j is lower then i it will get this error as there is no member with index i in that collection.