Search code examples
oracleloopsplsqloracle-apexprocedure

Browse the values of multiple fields and insert them into the same column

I am trying to create a functionality for my oracle apex application using duplicated row. Let's say that i have a vehicle table.

CREATE TABLE vehicles
(
    brand VARCHAR2(50),
    model VARCHAR2(50),
    comment VARCHAR2(50)
);

I first created a PL/SQL procedure that is currently working, which duplicate the row n amount of times, selected by the user. For example, if the user has 5 of the same vehicles, he can enter "5", and the application will insert 5 times the same vehicle, with different primary keys of course.

What I am trying to achieve, is to give the possiblity for the user to add a custom comment on every row. For example, he would have 5 of the same cars, with different comment on each one.

Here's the code of my procedure when the user is inserting the cars :

FOR i IN 1..:P27_NUMBER_VEHICLES -- this represents the numbers of cars the users want to insert
LOOP
INSERT INTO vehicles(brand, model) 
VALUES(:P27_BRAND, :P27_MODEL);
END LOOP;
END;

This creates 5 of the same vehicles, but i want the user to be able to add 5 different comments on each vehicle, here's an example of my insertion form, the comment field appears dynamically based on the numbers that the user want to insert enter image description here

I've now tried to update my procedure with the comment, i tried to give a name that could be dynamically changed by the procedure, I named it :P27_COMMENT_0 / 1 / 2 / 3 / 4 / 5. So i tried to add :P27_COMMENT_ || i (the index of the loop) to try to browse the differents fields automatically, here's the code :

BEGIN
FOR i IN 1..:P27_NUMBER_VEHICLES 
LOOP
INSERT INTO vehicles(brand, model, comment) 
VALUES(:P27_BRAND, :P27_MODEL, :P27_COMM_ || i);
END LOOP;
END;

This unfortunately doesn't work.

I hope you guys could have a solution for me. To summarize everything, I am trying to duplicate a row witha loop, but still being able to add a custom entry for each one on a specific column.

Do not hesitate to ask for more details, as I may have forgot some important things.

Thank you in advance,

Thomas

UPDATE

Here's how it is showing, I can't write anything on it :

enter image description here

Solution

  • In my opinion is easier to use APEX_ITEM, as follow:

    1. Add an Interactive Report region or sub region after your items.

    2. Set the IR SQL query as:

    SELECT 
  APEX_ITEM.TEXT(
     p_idx => 1,  
     p_attributes=> 'placeholder="Comment '|| LEVEL ||'"') AS Name
FROM dual
CONNECT BY LEVEL <= TO_NUMBER (:P16_NUM)
;

    :P16_NUM is an item which holds the number of items to display. Do not forget to add it in Page Items to Submit in the Source section of the IR.

    1. Add a submit button.

    2. In Processing create a new process an iterate through the items as:

    BEGIN
  apex_debug.enable;
  for i in apex_application.g_f01.FIRST..apex_application.g_f01.LAST loop
    apex_debug.info('####### Comment '||i||' '||apex_application.g_f01(i));
  end loop;
END;
    1. Finally your INSERT:
    INSERT INTO vehicles(brand, model, comment)
VALUES(:P27_BRAND, :P27_MODEL, apex_application.g_f01(i));
    1. When you check that everything works fine, change the process code:
    BEGIN
    for i in apex_application.g_f01.FIRST..apex_application.g_f01.LAST loop
        INSERT INTO vehicles(brand, model, comment)
        VALUES(:P27_BRAND, :P27_MODEL, apex_application.g_f01(i));
    end loop;
END;

    EDIT

    1. Select the column where the inputs are, on the right find the Security section and disable Escape special characters.

    enter image description here