Oracle Apex - Loop Items

user_13552281 0
Viewed 146

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

oracle-apex oracle
1 Answers

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;
user_41898140
Related