How do I INSERTvalues from controls into table

Introduction
In Oracle APEX, inserting values from form controls—like text fields, select lists, or date pickers—into a database table is a fundamental task when building data entry applications. Whether you're collecting customer information, tracking tasks, or storing feedback, the process follows a consistent pattern. By mapping page items to table columns and using built-in APEX processing or custom PL/SQL, you can quickly set up a secure and reliable way to capture user input and store it in your database.

 To insert values from controls into a table in Oracle APEX, you typically use a combination of form items, a submit button, and a PL/SQL process. This is a common pattern for data entry pages where user input is captured through page controls like text fields, select lists, or date pickers and stored in a database table.

Here is a detailed walkthrough on how to do this:

Step 1: Create the Target Table (If Not Already Created)

Example table:

CREATE TABLE CONTACTS (
  ID            NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  FULL_NAME     VARCHAR2(100),
  EMAIL_ADDRESS VARCHAR2(100),
  PHONE_NUMBER  VARCHAR2(20),
  CREATED_AT    DATE DEFAULT SYSDATE
);

Step 2: Create a New APEX Page

1. Go to App Builder in Oracle APEX.

2. Open your application.

3. Click Create > Page.

4. Choose Form > Form on a Table with Report, or just Blank Page if you want full control.

If using a blank page:

• Manually add form controls and the submit button.

Step 3: Add Page Items (Form Controls)

Add three page items (under the appropriate region):

• P1_FULL_NAME (Text Field)

• P1_EMAIL_ADDRESS (Text Field)

• P1_PHONE_NUMBER (Text Field)

These items collect values from the user.

Step 4: Add a Submit Button

Add a button to submit the data:

• Label: Save

• Name: SAVE_BTN

• Action: Defined by Dynamic Action or Submit Page

Step 5: Create a PL/SQL Process to Insert the Record

Go to the Processing section and add a new Process:

• Name: Insert Contact

• Type: PL/SQL Code

• Process Point: After Submit

• When Button Pressed: SAVE_BTN

PL/SQL Code:

INSERT INTO CONTACTS (
  FULL_NAME,
  EMAIL_ADDRESS,
  PHONE_NUMBER
) VALUES (
  :P1_FULL_NAME,
  :P1_EMAIL_ADDRESS,
  :P1_PHONE_NUMBER
);

Step 6: Optional – Add Success Message

• Go to Messages > Success Message

• Enter: Contact saved successfully.

Step 7: Run and Test the Page

1. Run the page.

2. Fill out the form fields.

3. Click the Save button.

4. The record should now be inserted into the CONTACTS table.

Optional: Clear the Form After Insert

Add another PL/SQL Process to clear values:

:P1_FULL_NAME := NULL;
:P1_EMAIL_ADDRESS := NULL;
:P1_PHONE_NUMBER := NULL;

Run this process After Submit, after the insert, and only when the button SAVE_BTN is pressed.Conclusion

Inserting values from controls into a table in Oracle APEX is a foundational task and follows a clear process: define form controls, use a submit button, and write a PL/SQL process to insert the data. This pattern gives you full control over how and when data is stored, and it’s easy to expand with validations, conditionals, or dynamic actions for more advanced behavior.

 

Inserting Values from Controls into a Table in Oracle APEX

Oracle APEX allows you to capture user input through form controls like text fields, dropdowns, and date pickers, and then insert these values into a database table. This tutorial explains different methods to insert values into a table using PL/SQL, Dynamic Actions, and Processes.

Step 1: Create a Table for Data Storage

Before inserting values, ensure there is a table where data will be stored.

CREATE TABLE employee_data (

    emp_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    emp_name VARCHAR2(100),

    emp_email VARCHAR2(100),

    emp_salary NUMBER(10,2),

    emp_hire_date DATE

);

This table will store employee details entered through APEX form controls.

Step 2: Create an APEX Form with Controls

1. Open Oracle APEX and go to Page Designer.

2. Create a Blank Page or a Form Page.

3. Add the following items: 

• P1_EMP_NAME (Text Field)

• P1_EMP_EMAIL (Text Field)

• P1_EMP_SALARY (Number Field)

• P1_EMP_HIRE_DATE (Date Picker)

• P1_SUBMIT (Button)

These form fields will collect user input before inserting data into the employee_data table.

Step 3: Insert Data Using a PL/SQL Process (On Submit)

1. In Page Designer, select the P1_SUBMIT button.

2. Under Processing, click Create Process.

3. Set Type to PL/SQL Code.

4. Use the following PL/SQL code:

BEGIN

    INSERT INTO employee_data (emp_name, emp_email, emp_salary, emp_hire_date)

    VALUES (:P1_EMP_NAME, :P1_EMP_EMAIL, :P1_EMP_SALARY, TO_DATE(:P1_EMP_HIRE_DATE, 'YYYY-MM-DD'));

    COMMIT;

END;

5. Under Execution Options, set When Button Pressed to P1_SUBMIT.

6. Click Save and run the page to test the form submission.

Step 4: Insert Data Using a Dynamic Action (Without Page Submit)

Instead of submitting the page, use AJAX and Dynamic Actions to insert data asynchronously.

Create an AJAX Process

1. Go to Shared Components > Application Processes.

2. Click Create Process, name it INSERT_EMPLOYEE_DATA, and set Type to PL/SQL AJAX Process.

3. Add the following PL/SQL code:

BEGIN

    INSERT INTO employee_data (emp_name, emp_email, emp_salary, emp_hire_date)

    VALUES (:P1_EMP_NAME, :P1_EMP_EMAIL, :P1_EMP_SALARY, TO_DATE(:P1_EMP_HIRE_DATE, 'YYYY-MM-DD'));

    COMMIT;

END;

Create a Dynamic Action

1. In Page Designer, select the P1_SUBMIT button.

2. Under Dynamic Actions, click Create Dynamic Action.

3. Set Event to Click and select P1_SUBMIT.

4. Under True Actions, select Execute Server-side Code and enter:

apex.server.process("INSERT_EMPLOYEE_DATA", {

    pageItems: "#P1_EMP_NAME, #P1_EMP_EMAIL, #P1_EMP_SALARY, #P1_EMP_HIRE_DATE"

}, {

    success: function(data) {

        alert("Record inserted successfully!");

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

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

        apex.item("P1_EMP_SALARY").setValue('');

        apex.item("P1_EMP_HIRE_DATE").setValue('');

    },

    error: function(request) {

        alert("Error inserting record.");

    }

});

This method allows data to be inserted without a full-page reload, providing a better user experience.

Step 5: Display a Success Message

To notify users of successful insertion:

1. Go to Page Designer > Processing.

2. Select the PL/SQL Process.

3. Under Messages, set Success Message to "Record inserted successfully!".

Now, after submission, APEX will display this message automatically.

Step 6: Validating User Input Before Inserting

To ensure that required fields are not left empty:

1. In Page Designer, select each item (e.g., P1_EMP_NAME).

2. Under Validation, click Create Validation.

3. Set Type to Not Null.

4. Define a Validation Message like "Employee Name is required!".

This prevents blank submissions.

Best Practices for Inserting Values in APEX

• Use PL/SQL Processes for direct database operations.

• Use Dynamic Actions with AJAX for seamless form submission.

• Validate inputs before inserting to prevent errors.

• Display success and error messages for better user feedback.

• Use COMMIT in PL/SQL to ensure the transaction is saved.

Inserting values from controls into a table in APEX can be done using PL/SQL processes, AJAX Dynamic Actions, or APEX Form Pages. The best approach depends on whether you want to submit the page or use AJAX for real-time updates.

EXAMPLE:

Given the following controls, we want to save the values from the textboxes into a table

Step 1 – Give the button a Dynamic Action

Step 2 – Give the Action a True branch

Step 3 – set the following

Where the code looks like the following:

BEGIN

INSERT INTO TEST_AB 

    (TABLEA_ID,TABLEb_ID)

VALUES

    (:P44_TESTA_ID_VALUE,:P44_TESTB_ID_VALUE);

 END;

***** Notice that the control’s values are passed with a “:” in front of the control’s name.

This will save into the TABLE_AB like the following:

Conclusion
Inserting values from controls into a table in Oracle APEX is a straightforward process that can be implemented using declarative features or custom logic. By understanding how to bind page items to database columns and manage the insert process, you can build powerful, user-friendly data entry forms. This essential skill ensures your applications can effectively collect and store information, forming the backbone of most APEX solutions.