How do I ADD values from 2 controls into table

 Introduction

In Oracle APEX, adding values from multiple controls (form fields) into a table is a fundamental task in building data-entry forms. Whether you are creating a form for capturing customer information, entering orders, or submitting feedback, collecting values from two or more page items and inserting them into a table is done through built-in or custom processes. This approach ensures that user input is efficiently captured and stored in your application's database, using APEX's declarative tools or PL/SQL logic. This blog will walk you through how to take values from two form controls and add them into a database table.

In Oracle APEX, adding values from two form controls into a table involves capturing user input from page items and inserting those values using a Process. This can be done declaratively through the Application Builder or programmatically with PL/SQL. The most common use case is when you have a simple form with two fields—such as FIRST_NAME and LAST_NAME—and you want to save these values into a table like PEOPLE.

To begin, create the necessary table in your database. For example:

CREATE TABLE PEOPLE (
  ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  FIRST_NAME VARCHAR2(100),
  LAST_NAME VARCHAR2(100)
);

Next, go into App Builder in Oracle APEX and follow these steps:

1. Create a Form Page

   • From App Builder, click Create Page.

   • Choose Form > Form on a Table with Report or Form on a Table.

   • Select the PEOPLE table.

   • APEX automatically creates a report and a form that includes the table’s columns as form controls.

2. Add Two Page Items (If Not Auto-Generated)

   • In the Page Designer, under the form region, ensure you have two page items: PXX_FIRST_NAME and PXX_LAST_NAME (where XX is your page number).

   • These items represent the two controls where users will enter data.

3. Create an Insert Process (If Not Auto-Generated)

   • In the Processing section, create a new Process.

   • Set the Type to PL/SQL Code.

   • Use the following code:

INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME)
VALUES (:PXX_FIRST_NAME, :PXX_LAST_NAME);

• Replace PXX with your actual page number.

4. Set the Process Point

   • The process should run On Submit: After Computations and Validations.

   • Make sure the form has a Submit button.

5. (Optional) Add Validations

   • Add validations to ensure that neither FIRST_NAME nor LAST_NAME is left blank before submitting the form.

6. Run the Application

   • Save and run the application.

   • Fill in the two fields and click Submit.

   • The values will be inserted into the PEOPLE table.

If you are working in a custom form and want to manually create the insert logic, follow the same steps but design the form from scratch, manually creating the page items and the PL/SQL insert process.

This method ensures you have full control over how data from multiple form controls is collected and saved into your database table.

Example

Adding Values from Two Textbox Items into a Database Table in Oracle APEX

Oracle APEX allows inserting values from multiple form fields into a database table using PL/SQL processes, dynamic actions, and form submissions. This tutorial covers different ways to insert values from two textbox items into a table.

 

Step 1: Create a Form with Two Textbox Items

1. Open your Oracle APEX application.

2. Click Create Page > Form > Form on a Table with Report.

3. Choose the table where the values will be inserted (e.g., EMPLOYEE_DETAILS).

4. In Page Designer, create two text fields: 

• P1_EMP_NAME (for Employee Name)

• P1_EMP_EMAIL (for Employee Email)

5. Add a Submit or Save button (P1_SAVE_BUTTON).

 

Step 2: Create a PL/SQL Process to Insert Data

1. In Page Designer, go to the Processing section.

2. Click Create Process, select PL/SQL Code, and enter the following:

BEGIN

    INSERT INTO employee_details (emp_name, emp_email)

    VALUES (:P1_EMP_NAME, :P1_EMP_EMAIL);

    COMMIT;

END;

3. Set Execution Point to After Submit and When Button Pressed to P1_SAVE_BUTTON.

4. Click Save and Run the page.

When users enter values in the textboxes and click Save, the data will be inserted into EMPLOYEE_DETAILS.

 

Step 3: Confirm Success with a Message

To show a success message after inserting the record:

1. Open Page Designer, go to Messages, and add the following code inside the PL/SQL process:

apex_util.set_session_state('P1_SUCCESS_MSG', 'Record successfully added!');

2. Create a new static region and add a Display Item (P1_SUCCESS_MSG) to show the message.

3. Set Source to PL/SQL Expression:

:P1_SUCCESS_MSG

Now, after submitting, users will see a confirmation message.

 

Alternative: Insert Data Using AJAX (Without Page Refresh)

If you want to insert data without refreshing the page, use AJAX and Dynamic Actions.

Step 1: Create an AJAX PL/SQL Process

1. Go to Shared Components > Application Processes.

2. Click Create Process, name it INSERT_EMPLOYEE_AJAX, and enter the following PL/SQL:

BEGIN

    INSERT INTO employee_details (emp_name, emp_email)

    VALUES (:P1_EMP_NAME, :P1_EMP_EMAIL);

    COMMIT;

END;

3. Set Process Type to Ajax Callback.

Step 2: Create a Dynamic Action

1. In Page Designer, select the Save Button (P1_SAVE_BUTTON).

2. Click Create Dynamic Action and set:

• Event: Click

• Action: Execute JavaScript

3. Add this JavaScript code:

apex.server.process("INSERT_EMPLOYEE_AJAX", {

    pageItems: "#P1_EMP_NAME, #P1_EMP_EMAIL"

}, {

    success: function(data) {

        alert("Record successfully added!");

        apex.item("P1_EMP_NAME").setValue("");

        apex.item("P1_EMP_EMAIL").setValue("");

    },

    error: function(request) {

        alert("Error inserting record.");

    }

});

This method inserts values without refreshing the page and clears the textboxes after insertion.

 

Best Practices

• Validate Inputs: Ensure users enter valid data before inserting records.

• Use Dynamic Actions for Better User Experience: Avoid full-page reloads by using AJAX.

• Confirm Successful Insertions: Show success messages after inserting values.

• Use Form Submissions for Simple Implementations: When AJAX is not required, a simple PL/SQL process on Submit works well.

 

Oracle APEX provides multiple ways to insert values from two textboxes into a table. You can use PL/SQL processes for form submissions or AJAX for real-time updates without refreshing the page. The best approach depends on your application's requirements.

EXAMPLE:

Assuming a page with 2 textboxes and a button. We want to take the two values from the controls and add them into the database as a new entry.

Step 1 – Add two textboxes to page 

Step 2 – Add a button with a Dynamic Action with two true branches

Step 3 – Set the following code in the first true branch

The code is as follows:

BEGIN

INSERT INTO TEST_A (VALUEA1, VALUEA2)

VALUES (:P49_TESTA_VALUEA1_VALUE,:P49_TESTA_VALUEA2_VALUE);

 END;

Step 4 – add a report at the bottom of the page with the following settings.

Step 5 – In the button’s second True branch set as follows.

You should now see something like this

Conclusion

Adding values from two controls into a table using Oracle APEX is both straightforward and powerful. By using APEX's native form-building features or by writing simple PL/SQL logic, you can ensure that user data is inserted correctly and efficiently. Whether you're capturing basic input like names and emails or more complex combinations, APEX offers the tools you need to quickly create secure and maintainable data-entry interfaces. This allows you to streamline application workflows and ensure accurate data handling within your Oracle database.