How do I UPDATE values from control into table

Introduction
Updating values from controls into a table is a fundamental operation in Oracle APEX that enables users to modify existing records directly from a form interface. This functionality is often required in applications where users need to review and edit existing data, such as contact details, product information, or status fields. APEX makes it simple to bind form items to database columns, retrieve the current values, and submit changes using a process that automatically updates the underlying table or uses custom PL/SQL.

 To update values from controls into a table in Oracle APEX, you typically use a form page that is bound to a table or a custom PL/SQL process. When a user selects a record (e.g., from a report), the page is populated with current values. After editing, the user submits the form, and APEX updates the table using the new input.

Here’s a step-by-step guide to achieve this:

Step 1: Create or Identify the Table to Update

Assume you have the following table:

CREATE TABLE EMPLOYEES (
  EMP_ID        NUMBER PRIMARY KEY,
  EMP_NAME      VARCHAR2(100),
  EMAIL         VARCHAR2(100),
  SALARY        NUMBER,
  LAST_UPDATED  DATE
);

Step 2: Create a Form to Edit Records

1. In APEX, go to App Builder > open your app.

2. Click Create > Page.

3. Choose Form > Form on a Table with Report.

4. Select the EMPLOYEES table.

5. APEX creates:

   • A report page (e.g., Page 1) with a link to edit a row.

   • A form page (e.g., Page 2) to edit selected record.

This form automatically includes page items like P2_EMP_NAME, P2_EMAIL, P2_SALARY, and a hidden P2_EMP_ID.

Step 3: Check or Configure the Automatic Row Fetch and Process

When the user clicks a row in the report:

• Process: Fetch Row from EMPLOYEES
  Automatically loads record into page items on page load.

• Process: Process Row of EMPLOYEES
  Automatically updates the table on submit, using the primary key.

Make sure this process has:

• Type: Automatic Row Processing (DML)

• Target Type: Table

• Table Name: EMPLOYEES

• Primary Key Column: EMP_ID

• Primary Key Item: P2_EMP_ID

• DML Operation: Update

Step 4: Add a Submit Button

This button is used to commit changes:

• Name: SAVE

• Action: Submit Page

Step 5: Optional – Add a Success Message

In the form page settings:

• Go to Messages > Success Message

• Set to: Employee record updated successfully.

Step 6: Test the Form

1. Run the application.

2. On the report page, click a row to edit.

3. Change the values in the form.

4. Click Save.

5. APEX updates the table using the primary key.

 Alternative: Manual Update Using PL/SQL

If you need more control:

1. Create a form manually using page items: P2_EMP_ID, P2_EMP_NAME, P2_EMAIL, etc.

2. Add a button: Update

3. Add a PL/SQL Process after submit:

UPDATE EMPLOYEES
SET
  EMP_NAME     = :P2_EMP_NAME,
  EMAIL        = :P2_EMAIL,
  SALARY       = :P2_SALARY,
  LAST_UPDATED = SYSDATE
WHERE EMP_ID = :P2_EMP_ID;

Only run when Update button is pressed.

Oracle APEX makes updating table data from form controls straightforward, whether using automatic form processing or writing custom PL/SQL. By connecting page items to database columns and using APEX's built-in fetch/update logic or manual control, you can ensure data is accurately captured and persisted. This approach is ideal for building data management interfaces that are both user-friendly and powerful.

 

Updating Values from Controls into a Table in Oracle APEX Forms and Reports

Oracle APEX allows users to update database records directly from forms and reports. This tutorial covers updating values using APEX forms, interactive grids, classic reports, and PL/SQL processes.

Updating Values Using APEX Form

Forms in APEX provide a simple way to update existing records.

Step 1: Create a Form Based on a Table

1. In Oracle APEX, go to your application and click Create Page.

2. Choose Form and select Form on a Table with Report.

3. Select the table where data will be updated (e.g., EMPLOYEE_DATA).

4. Choose Primary Key Column (e.g., EMP_ID).

5. Click Next, then Finish.

This will generate a report and a form where users can update values.

Step 2: Add an Update Button

1. Open Page Designer and select the form page.

2. Locate the Submit Button (Apply Changes).

3. Under Processing, find the Automatic Row Processing (DML) process.

4. Ensure it is set to Update the selected record.

When users edit values in the form and click Apply Changes, the record will be updated automatically.

Updating Values Using PL/SQL Process

To manually update values using PL/SQL:

Step 1: Create a Manual Update Process

1. In Page Designer, go to Processing.

2. Click Create Process and select PL/SQL Code.

3. Set When Button Pressed to your update button (e.g., P1_UPDATE).

4. Use the following PL/SQL code:

BEGIN

    UPDATE employee_data

    SET emp_name = :P1_EMP_NAME,

        emp_email = :P1_EMP_EMAIL,

        emp_salary = :P1_EMP_SALARY,

        emp_hire_date = TO_DATE(:P1_EMP_HIRE_DATE, 'YYYY-MM-DD')

    WHERE emp_id = :P1_EMP_ID;

    COMMIT;

END;

This will update the values in the database when the update button is clicked.

Updating Values in an Interactive Grid

Interactive Grids allow users to update multiple records inline.

Step 1: Create an Interactive Grid

1. In Oracle APEX, go to your application and click Create Page.

2. Select Report, then Interactive Grid.

3. Choose the table (EMPLOYEE_DATA) and primary key (EMP_ID).

4. Click Next, then Finish.

Step 2: Enable Editing in the Interactive Grid

1. Open Page Designer and select the Interactive Grid Region.

2. Under Attributes, set Edit Enabled to Yes.

3. Run the page, edit values directly in the grid, and click Save to update records.

Updating Values Using Classic Reports with Edit Links

If you want users to update records from a classic report:

Step 1: Add an Edit Link to a Classic Report

1. Open Page Designer and select your Classic Report.

2. Add a new column for the edit link using the following SQL:

SELECT emp_id,

       emp_name,

       emp_email,

       emp_salary,

       emp_hire_date,

       '<a href="f?p=&APP_ID.:10:&SESSION.::NO:RP,10:P10_EMP_ID:' || emp_id || '">Edit</a>' AS edit_link

FROM employee_data;

3. Click Save and Run.

Step 2: Create an Edit Form on Another Page

1. Create a new form page where users can edit records.

2. Add a hidden item P10_EMP_ID to store the record ID.

3. Modify the form to fetch existing values:

SELECT emp_name, emp_email, emp_salary, emp_hire_date 

INTO :P10_EMP_NAME, :P10_EMP_EMAIL, :P10_EMP_SALARY, :P10_EMP_HIRE_DATE 

FROM employee_data 

WHERE emp_id = :P10_EMP_ID;

4. Add an Update button with a PL/SQL process to update the record.

Now, clicking Edit in the report will open the form with the selected record.

Updating Values Using Dynamic Actions (Without Page Submit)

If you want to update values without submitting the page, use AJAX and Dynamic Actions.

Step 1: Create an AJAX Process

1. Go to Shared Components > Application Processes.

2. Create a process named UPDATE_EMPLOYEE_DATA and set Type to PL/SQL AJAX Process.

3. Use the following code:

BEGIN

    UPDATE employee_data

    SET emp_name = :P1_EMP_NAME,

        emp_email = :P1_EMP_EMAIL,

        emp_salary = :P1_EMP_SALARY,

        emp_hire_date = TO_DATE(:P1_EMP_HIRE_DATE, 'YYYY-MM-DD')

    WHERE emp_id = :P1_EMP_ID;

    COMMIT;

END;

Step 2: Create a Dynamic Action

1. In Page Designer, select the Update Button.

2. Click Create Dynamic Action and set:

• Event: Click

• Action: Execute JavaScript

3. Add the following JavaScript code:

apex.server.process("UPDATE_EMPLOYEE_DATA", {

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

}, {

    success: function(data) {

        alert("Record updated successfully!");

    },

    error: function(request) {

        alert("Error updating record.");

    }

});

This method updates values in real time without refreshing the page.

Best Practices for Updating Data in APEX

• Use Forms for Single Record Updates: Best for updating one record at a time.

• Use Interactive Grids for Bulk Updates: Best for updating multiple records quickly.

• Use Classic Reports with Edit Links: Good for linking reports to an edit form.

• Use Dynamic Actions for Live Updates: Best for seamless user experience.

• Validate Inputs Before Updating: Prevent errors and invalid data entries.

Oracle APEX provides multiple ways to update records, including forms, interactive grids, classic reports, PL/SQL processes, and AJAX dynamic actions. The best method depends on your application's needs.

EXAMPLE:

We have a dropdown that will display the value in one text box and the display text in a second text box. The goal is t to update the Text from the list  by changing the text in the “New Item” text box and saving that in the table.

 

Step 1 – Add the dropdown list and two text boxes.

Step 2- Add the following settings in the first true branch

The code is as follows

---- This part of the code gets the VALUE from the list and saves it to the 'P48_TESTA_ID_VALUE' text box.

var DisplayValueVar =$("#P48_TESTA_DROPDOWN option:selected").val();

console.log(DisplayValueVar);

apex.item('P48_TESTA_ID_VALUE').setValue(DisplayValueVar);

---- This part of the code gets the TEXT from the list and saves it to the 'P48_NEW_TEXT' text box.

var DisplayTextVar =$("#P48_TESTA_DROPDOWN option:selected").text();

console.log(DisplayTextVar);

apex.item('P48_NEW_TEXT').setValue(DisplayTextVar);

Step 3 – Add the second true branch for saving to the session

The next code saves the information to the session.The code is as follows:

APEX_UTIL.SET_SESSION_STATE('P48_TESTA_ID_VALUE',v('P48_TESTA_ID_VALUE'));

Step 4 – Next add a button with a Dynamic Action and two True Branches

Step 5 – First True branch

• Identification > Action: set to Execute Server-side Code

• Settings 

• > Language: PL/SQL

• > PL/SQL Code

BEGIN

    UPDATE TEST_A

    SET VALUEA1 = :P48_NEW_TEXT

    WHERE ID = :P48_TESTA_ID_VALUE;

END;

• > Items to Submit – This passes the information into the SQL above.

Step 7 – Add a classic report to display the data of the table and set the following values

Step 6 – Set the refresh for the table report 

Conclusion
Oracle APEX provides a powerful and flexible way to update table values from form controls. Whether using automated form processes or custom PL/SQL blocks, developers can efficiently capture user changes and apply them to the database. With built-in support for validations, conditions, and user-friendly form elements, updating data becomes a secure and seamless experience that enhances the overall usability of your applications.

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.