How do I DELETE manually from a table

 

Introduction
Manually deleting data from a table in Oracle APEX is a common task during development or maintenance when you need to remove specific records based on user input or custom criteria. While APEX provides declarative features for automated deletion in interactive reports and forms, sometimes a manual approach is necessary—especially when implementing custom buttons or writing business-specific logic. Using simple PL/SQL, you can safely control how and when rows are deleted.

To manually delete rows from a table in Oracle APEX, you create a page with controls that allow users to specify which records to delete and then use a PL/SQL process to perform the deletion. This approach gives you full control over when and how rows are removed.

Here’s a detailed step-by-step guide:

Step 1: Identify the Table

Assume you have a table named EMPLOYEES with a primary key column EMP_ID.Step 2: Create a Page with Controls

1. In Oracle APEX, create a new page or use an existing one.

2. Add a text item to capture the record identifier to delete, for example:

   • Page Item: P1_EMP_ID

   • Label: Employee ID to Delete

3. Add a button to trigger the delete action:

   • Name: DELETE_BTN

   • Label: Delete Record

   • Action: Submit Page

Step 3: Add a PL/SQL Process to Delete the Record

1. In the page designer, go to the Processing section.

2. Add a new Process:

   • Name: Delete Employee Record

   • Type: PL/SQL Code

   • Point: After Submit

   • Server-side Condition: When Button Pressed = DELETE_BTN

3. In the PL/SQL Code, write the DELETE statement:

BEGIN
  DELETE FROM EMPLOYEES
  WHERE EMP_ID = :P1_EMP_ID;

  IF SQL%ROWCOUNT = 0 THEN
    APEX_ERROR.ADD_ERROR(
      p_message => 'No record found with Employee ID ' || :P1_EMP_ID,
      p_display_location => apex_error.c_inline_in_notification);
  ELSE
    APEX_UTIL.SET_SESSION_STATE('P1_EMP_ID', NULL);
  END IF;
END;

This code deletes the employee with the specified ID. It also checks if any row was deleted. If none, it shows an inline error message. If successful, it clears the input.

Step 4: Add Confirmation (Optional but Recommended)

To prevent accidental deletes:

• Create a Dynamic Action on the DELETE_BTN button.

• Action: Confirm

• Message: Are you sure you want to delete this record?

Step 5: Run and Test

1. Run the page.

2. Enter an Employee ID in the text box.

3. Click Delete Record.

4. If the record exists, it is deleted; otherwise, an error is shown.

Additional Notes

• Always validate input before running DELETE statements.

• Consider adding authorization checks to restrict who can delete.

• For multiple deletes, you can use interactive grids or reports with row selection and delete actions.

• Use APEX built-in error handling to provide user-friendly feedback.

Manual deletion of rows in Oracle APEX empowers developers to create precise and controlled data removal processes. By capturing user input, confirming actions, and executing PL/SQL DELETE commands, you ensure that data is deleted safely and intentionally. This approach is suitable for custom workflows and scenarios requiring explicit user-driven record removal.

 

EXAMPLE:

In this  we will select a value with the dropdown list, display the ID in a textbox, and delete it from the database via a manual button push, with a report display.

Step 1 Create a dropdown list with the following values

Step 2 – Add a dynamic Action and two True branches

Step 3 -  In the first True branch

 

Here is the code used.

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

console.log(DisplayValueVar);

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

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

console.log(DisplayTextVar);

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

Step 4 – In the second true branch

Step 5 – Add the button

Step 6- Add the delete execute true branch with the following settings

Here is the code that we used

BEGIN

 DELETE FROM TEST_A

 WHERE ID = :P50_TESTA_ID_VALUE;

 

END;

Step 7 – Add the report to the application

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

You should see something like this

   

Conclusion
Deleting records manually from a table in Oracle APEX gives developers precise control over the deletion logic and user interactions. By combining page items, buttons, and PL/SQL processes, you can create secure and targeted delete operations tailored to your application’s needs. Whether triggered by user action or embedded in a custom process, manual deletion provides the flexibility to manage data effectively in any APEX project.

How do I ADD values from 2 controls into table

Introduction
Adding values from two form controls into a table in Oracle APEX is a common requirement when capturing user input across multiple fields. Whether you're entering product details, capturing a name and email, or logging coordinates, the process involves creating input items on a page and using a submit process to store those values in the database. With Oracle APEX, this can be achieved using either declarative features or a custom PL/SQL block, making it simple to integrate into both basic and complex applications.

 To add values from two controls into a table in Oracle APEX, you use page items (form fields), a submit button, and a process that takes the values from those items and inserts them into a target table. This is a common scenario for capturing basic data like a name and email, or a code and description.

Here’s a detailed step-by-step process:

Step 1: Create the Table (if it doesn’t exist)

First, define the table that will store the values. For example:

CREATE TABLE CONTACT_LOG (
  ID        NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  FULL_NAME VARCHAR2(100),
  EMAIL     VARCHAR2(100),
  CREATED_AT DATE DEFAULT SYSDATE
);

Step 2: Create a Page in Oracle APEX

1. Open your application in App Builder.

2. Click Create > Page.

3. Choose Blank Page.

4. Name the page (e.g., “Add Contact Info”).

Step 3: Add Form Controls (Page Items)

In the page designer:

• Under a region (e.g., "Contact Form"), add two page items:

  • P1_FULL_NAME – Type: Text Field – Label: Full Name

  • P1_EMAIL – Type: Text Field – Label: Email

These will be the input controls.

Step 4: Add a Button to Submit

1. Under the same region or a new one, add a button:

   • Name: SAVE

   • Label: Save

   • Action: Submit Page

Step 5: Add a PL/SQL Process to Insert the Values

1. In the Processing section, click “+” to add a new Process.

2. Name: Insert Contact Log

3. Type: PL/SQL Code

4. Point: After Submit

5. When Button Pressed: SAVE

PL/SQL Code:

INSERT INTO CONTACT_LOG (FULL_NAME, EMAIL)
VALUES (:P1_FULL_NAME, :P1_EMAIL);

Step 6: Add a Success Message (Optional)

In the Messages section of the page:

• Success Message: Contact saved successfully.

Step 7: Run and Test

1. Run the application.

2. Enter values in the Full Name and Email fields.

3. Click Save.

4. The record is inserted into the CONTACT_LOG table.

Optional: Clear Form Fields After Insert

If you want to clear the form after submission:

1. Add another Process after the insert (or include it in the same one).

2. Example:

:P1_FULL_NAME := NULL;
:P1_EMAIL := NULL;

This resets the form for the next entry.

Adding values from two controls into a table in Oracle APEX is quick and efficient using declarative form items and a simple PL/SQL process. This method provides flexibility to collect and store input from users in a secure and controlled way. Whether you're building a contact form, data logger, or simple data capture tool, this approach gives you the core structure to expand into more complex applications.

 

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
Inserting values from two controls into a table in Oracle APEX is a foundational task that reinforces the platform’s strength in rapid form development. With a few clicks or lines of code, developers can bind input fields to database columns and ensure accurate data capture. This process supports flexible UI design while maintaining data consistency, forming the backbone of many real-world APEX applications.