Oracle Apex - Loop Items-CodePudding

I have a form where an user can add a record for a customer, issue is, each customer can have multiple products. My insert (record) process basically checks if there's info added on the product items and loops for the necessary amount of times, e.g.: User adds customer ABCDE and selects 2 products, 2 records for customer ABCDE will be inserted with the 2 new products. Issue is that I can't get Apex to insert both products. I have something like this:

LOOP
    V_COUNTER := V_COUNTER   1;
    EXIT WHEN V_COUNTER > V_AMOUNT_OF_PRODUCTS_COUNTER;
        INSERT INTO MY_TABLE (CUSTOMER,                   PRODUCT)
        VALUES               (:P2_MY_TEXT_CUSTOMER_ITEM, :P2_PRODUCT_||V_COUNTER);
END LOOP;

If the user adds 3 products: result: 3 rows of the same customer with the same (first item) product name desired result: 3 rows of the same product with products being :P2_PRODUCT_1, :P2_PRODUCT_2 and :P2_PRODUCT_3.

Thanks

CodePudding user response：

Bind variables cannot be declaratively named within a block of pl/sql code, but you can pass them as an argument with the function APEX_UTIL.GET_SESSION_STATE. LPAD transforms "1" to "01".

So your code would be (untested):


LOOP
    V_COUNTER := V_COUNTER   1;
    EXIT WHEN V_COUNTER > V_AMOUNT_OF_PRODUCTS_COUNTER;
        INSERT INTO MY_TABLE (
          CUSTOMER,
          PRODUCT)
        VALUES (
          :P2_MY_TEXT_CUSTOMER_ITEM
          ,apex_util.get_session_state (p_item => 'P2_PRODUCT'||'_'||LPAD(V_COUNTER,2,'0')));
END LOOP;

or with a for loop:


FOR r IN 1 .. V_AMOUNT_OF_PRODUCTS_COUNTER LOOP
  INSERT INTO MY_TABLE (
    CUSTOMER,
    PRODUCT)
  VALUES (
    :P2_MY_TEXT_CUSTOMER_ITEM
    ,apex_util.get_session_state (p_item => 'P2_PRODUCT'||'_'||LPAD(r,2,'0')));
END LOOP;