How do I INSERT values from controls into table

 Introduction

In Oracle APEX, inserting values from page controls—such as text fields, select lists, and date pickers—into a database table is a common task in application development. This functionality allows users to input data through a form and store that data directly in a table with a single click. Whether you're building a form to capture customer information or logging employee records, APEX provides a straightforward and declarative way to handle inserts using built-in page processes. With minimal coding, developers can connect form controls to table columns and ensure data is inserted reliably and securely.

To insert values from controls (such as text fields, select lists, checkboxes) into a database table in Oracle APEX, you typically use a Form page or create a custom PL/SQL process tied to a button. Below is a detailed explanation of how to do this using both the automatic form creation method and a manual PL/SQL block for full control.

1. Using a Form Page (Automatic Binding)

Oracle APEX makes inserting data easy through built-in Form pages that are directly linked to your database table.

Step-by-step:

1. Open your Oracle APEX app and go to App Builder.

2. Click Create Page.

3. Choose Form > then select Form on a Table with Report or Form on a Table.

4. Choose your table (e.g., PEOPLE) where you want to insert the data.

5. APEX will auto-generate form items (controls) that match your table columns.

6. A "Submit" button and process will be created automatically.

7. Run the application. When you fill out the form and click Submit, APEX inserts the values into the table.

This method uses Data Source Binding, where form items are automatically tied to column names.

2. Inserting with Custom PL/SQL Code

If you are not using an APEX-generated form and want to insert data manually, you can use a PL/SQL Process that runs on button click.

Steps:

1. Create a blank page or use an existing one.

2. Add page items like:

   • P1_FIRST_NAME (Text Field)

   • P1_LAST_NAME (Text Field)

   • P1_EMAIL (Text Field)

3. Add a Button (e.g., INSERT_BTN).

4. Go to Processing > Create Process.

5. Type: PL/SQL Code

6. When Button Pressed: INSERT_BTN

7. In the PL/SQL Code section, write:

INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, EMAIL)
VALUES (:P1_FIRST_NAME, :P1_LAST_NAME, :P1_EMAIL);

8. Click Apply Changes.

Note: The : syntax is used to refer to APEX page items.

3. Optional Enhancements

• Validation: Add page-level validations to ensure required fields are filled.

• Success Message: Set a success message (e.g., “Record successfully added.”)

• Navigation: Add a branch to return to a report page or confirmation screen after insertion.

4. Dynamic Actions (Optional Use Case)

If using JavaScript or AJAX for inserts, Dynamic Actions can trigger PL/SQL code via AJAX callback. This is more advanced and suitable for interactive UIs.

By using Forms or PL/SQL processes in Oracle APEX, developers can efficiently collect user input and insert it into database tables while keeping control over validations and data flow. Whether you use automated forms or write the insert manually, APEX provides all the tools needed to securely and efficiently manage data input.

Example

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

Using Oracle APEX to insert values from page controls into a table streamlines data entry and supports rapid application development. By linking form items to database columns and using APEX’s declarative processing, developers can quickly build functional and responsive applications. This approach ensures consistent user input handling, integrates seamlessly with database logic, and maintains data integrity—making it an essential skill for any APEX developer.

How do I refresh a table with a button

 Introduction

In Oracle APEX, providing users with a seamless and responsive experience is key to building effective web applications. One simple yet powerful technique is allowing users to refresh a data table (such as a Classic Report or Interactive Report) by clicking a button. This is especially useful after inserting, updating, or deleting data, ensuring the displayed information is always up-to-date without requiring a full page reload. With Oracle APEX’s declarative features and Dynamic Actions, adding a refresh button is both quick and efficient.

In Oracle APEX, refreshing a table (such as a Classic Report, Interactive Report, or Interactive Grid) using a button can be achieved easily using Dynamic Actions. This is a very common requirement when users need to see updated data after performing actions like inserting or deleting records, or after applying filters. By wiring a button to refresh a region, you can ensure the data shown in your report or grid remains current without reloading the entire page.

To implement a refresh button for a report or grid:

1. Create or identify the report region to be refreshed
Make sure the region you want to refresh (Classic Report, Interactive Report, etc.) has a unique static ID.
To assign or verify the static ID:

• Go to the Page Designer.

• Select the region (e.g., your report).

• In the Advanced section of the Property Editor, set a value for Static ID (e.g., report_emp).

2. Add a button to your page
You can place the button anywhere on the page.

• In Page Designer, right-click on the region where you want the button (e.g., a region above the report).

• Select Create Button.

• Set the Button Name (e.g., REFRESH_BTN) and Label (e.g., Refresh Report).

• Set Action to “Defined by Dynamic Action”.

3. Create a Dynamic Action for the button

• In Page Designer, select the REFRESH_BTN button.

• In the Dynamic Actions section, click Create Dynamic Action.

• Set Name (e.g., Refresh Report Action).

• Event: Click.

• Selection Type: Button.

• Button: REFRESH_BTN.

4. Define the True Action to refresh the region

• Under True Actions, click Add True Action.

• Action: Refresh.

• Affected Elements:

  • Selection Type: Region.

  • Region: select the report region to refresh (e.g., Employee Report).

This setup allows the report to refresh dynamically whenever the button is clicked. The refresh process uses AJAX behind the scenes, so only the report region reloads, not the entire page.

Optional: Refresh After a Process
If you're inserting data via a PL/SQL Process and want the report to refresh immediately after, you can add a Dynamic Action on a button click or after a process completes, using the same method:

• Use the “Execute PL/SQL Code” action, followed by a “Refresh” action.

This technique ensures a smooth and modern user experience while keeping your application's data accurate and instantly visible.

Example

The goal id to refresh a table after adding an entry to the table.

Step 1- Add a table region

Step 2 – Name the table

Step 3 – Select the table name

Step 4 – Add a second Dynamic Action to the button

• Select The action: Refresh

• Affected Elements

• Selection Type: Region ( the report is a region)

• Region: Newtable (the name of the report region)

Conclusion
Refreshing a table with a button in Oracle APEX enhances the interactivity and usability of your application. It helps users instantly view the most current data without navigating away or manually refreshing the page. By combining a standard button with a Dynamic Action to refresh the relevant region, you can provide a responsive and polished interface with minimal effort. This approach not only improves user experience but also maintains performance and clarity across your application pages.

How do I Add a row to a table with a button

 Adding a row to a table using a button in Oracle APEX is a common requirement for creating interactive and user-friendly applications. This functionality allows users to quickly insert new records into a table directly from the user interface without navigating away from the current page. By configuring a button to trigger a process that inserts data, developers can streamline data entry workflows and enhance the overall application experience.

Adding a row to a table with a button in Oracle APEX involves creating a user interface element (button) that, when clicked, triggers a process to insert a new record into the database table. This is a common feature in data entry applications to simplify the process of adding new information.

Step 1: Create a Button

1. Navigate to the Application Builder and open the page where you want to add the button.

2. In the Page Designer, under the region where the table or report is displayed, click on the “Buttons” node.

3. Click the “Create” button to add a new button.

4. Give the button a meaningful name such as “Add Row” or “New Record.”

5. Set the button position (e.g., above the report or in the page toolbar).

Step 2: Define a Process to Insert the New Row

1. Still in the Page Designer, navigate to the “Processing” section.

2. Click “Create” to add a new process.

3. Choose “PL/SQL” as the process type.

4. Write a PL/SQL block that inserts a new row into your target table. For example:

BEGIN
   INSERT INTO your_table_name (column1, column2, column3)
   VALUES (:P1_COLUMN1, :P1_COLUMN2, :P1_COLUMN3);
   COMMIT;
END;

Replace your_table_name and the columns with the actual table and columns you are working with. The bind variables like :P1_COLUMN1 refer to page items where the user inputs data.

Step 3: Set the Button to Submit the Page

1. Go back to the button properties.

2. Set the button action to “Submit Page.”

3. Set the “Request” value to something meaningful, such as ADD_ROW. This value will be used to identify the process triggered by this button.

Step 4: Condition the Process to Run on Button Click

1. In the process you created, set a condition to run only when the button is pressed.

2. Use the condition “When Button Pressed” and select the button you created (e.g., “Add Row”).

Step 5: Refresh the Region

1. To see the new row added immediately, set up a Dynamic Action to refresh the report or region after the process completes.

2. Create a Dynamic Action on the button click event.

3. Add a True Action of type “Refresh” and select the region displaying the data.

Step 6: Test Your Setup

1. Run the page.

2. Enter the data into the form items corresponding to the columns.

3. Click the “Add Row” button.

4. The PL/SQL process inserts the row, commits the transaction, and the report region refreshes to show the new row.

This approach ensures that users can add new data seamlessly via a button click, maintaining a smooth and interactive application experience. With Oracle APEX’s declarative framework combined with PL/SQL, this process is efficient and easy to manage.

Example

In this example we are going to use two fields and one table

• Fields: P10_NEW_1 and  P10_NEW_2

• Table:

• Using Table1

• Has 3 columns

• ID

• VALUE1 – varchar2

• VALUE2- varchar2

• Has 1 button: named “add”

Step 1 -Create a page with both fields and a button

Step 2- Add a dynamic Action to the button

Step 3- In the Dynamic Action TRUE section

• In Identification choose Action: Execute Server-side Code

• In the Settings

• Language (PL/SQL)

• PL/SQL Code: Add the following code

INSERT INTO TABLE1  (value1,value2)

 VALUES(:P10_NEW_1,:P10_NEW_2);

• In the Items to Submit enter all of the fields that you want to pass to the database. If you don’t do this then the values are not entered into the database.

Implementing a button to add a new row not only improves usability but also helps maintain data consistency and integrity by guiding users through predefined input forms or processes. With Oracle APEX’s declarative features and PL/SQL integration, adding this capability becomes straightforward and efficient, enabling rapid application development.

How do I Create a Classic Report with data entry form

 Creating a Classic Report with a data entry form in Oracle APEX is a practical approach to display data while providing users the ability to add, edit, or delete records seamlessly. This combination enhances user interaction by allowing direct manipulation of data from the same interface where information is viewed. By integrating a Classic Report with a form, developers can build efficient applications that improve workflow and data management.

Creating a Classic Report with a data entry form in Oracle APEX allows users to view data in a tabular format and simultaneously add, edit, or delete records through a form interface. This setup improves user experience by combining data presentation and data manipulation within a seamless workflow.

To create this combination, start by building a Classic Report page based on a SQL query that retrieves the desired data from your table. Navigate to Application Builder, then create a new page, and select Classic Report as the region type. Specify the source SQL query that defines the data to display.

Next, add a data entry form page that will be linked to the Classic Report. This form should be based on the same table or view as the report, allowing users to insert new records or modify existing ones. In Application Builder, create a new page and select a Form type such as "Form on a Table with Report" or "Form on a Table." Configure the form items to match the columns in your table, setting appropriate item types, validations, and default values as needed.

Link the Classic Report to the data entry form by adding a column with a link or button in the report. This link should pass the primary key value of the selected record as a request or page item to the form page. To do this, edit the Classic Report SQL and include a column with an HTML anchor tag or use APEX’s link builder functionality. For example, you can add a column that contains a link like:

SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       '<a href="f?p=&APP_ID.:10:&SESSION.::NO::P10_EMPLOYEE_ID:' || EMPLOYEE_ID || '">Edit</a>' AS EDIT_LINK
FROM EMPLOYEES

Here, page 10 is assumed to be the form page where P10_EMPLOYEE_ID is the item that holds the employee’s ID.

On the form page, configure processing to handle insert and update operations based on whether the primary key value is present. This is typically done using built-in Automatic Row Processing (DML) processes. Ensure validations are set to prevent incorrect data entry.

Finally, add navigation buttons on the form page such as “Save” and “Cancel.” The “Save” button should submit the page and perform the DML operation, then redirect back to the Classic Report page. The “Cancel” button can simply redirect without saving changes.

This combination of Classic Report and data entry form offers a powerful and user-friendly interface, allowing users to browse data easily and perform data entry or editing without switching contexts. It is a common pattern in Oracle APEX applications that balances simplicity and functionality.

Example

In Oracle Application Express (APEX), integrating a Classic Report with a data entry form on the same page enhances user experience by enabling both data viewing and input within a single interface. To achieve this integration, follow these steps:

1. Create the Classic Report:

• Utilize the Create Page Wizard in APEX to generate a Classic Report based on your chosen data source. This process involves specifying the report's data source, such as a table, view, or SQL query, and configuring attributes like the report's name and navigation settings. 

2. Create the Data Entry Form:

• Within the App Builder, initiate the creation of a new page and select Form as the component type.

• Choose the appropriate form type, such as Form on a Table or Form on a SQL Query, depending on your data structure.

• Define the form's attributes, ensuring it targets the same data source as your Classic Report to maintain consistency. 

3. Link the Report and Form:

• In your Classic Report, configure a link (e.g., an Edit button) that directs users to the same page but switches the page mode from Report to Form.

• Pass the primary key of the selected record as a parameter in the URL to identify the specific record for editing.

• On the form page, utilize this parameter to retrieve and display the corresponding record, allowing users to edit the data seamlessly. 

This setup allows users to view data in the Classic Report and edit individual records using the form, streamlining data management tasks.

Is critical that you pay attention to the following fields: 

Select the primary key.

You should now have two new pages with the names that matches what you entered in the previous screens.

Note: if you try running the ClassicReport_Form by itself you will get the following error:

When you run the “ClassicReportWithForm” page you get the following result:

If you click on the “Pencil” link you get the modal “form” page.

Notice that the “State_ID” field displays as a deropdown with all of the states.

If you click on the control you see the list.

You also get some “pre-wire” buttons for the form:

In conclusion, combining a Classic Report with a data entry form in Oracle APEX creates a powerful and user-friendly interface. This setup allows users to interact with data dynamically, offering both visibility and edit capabilities in one place. Implementing this approach can significantly streamline data handling processes and improve overall application usability.