How do I add a delete button to a Classic Report

 Adding a delete button to a Classic Report in Oracle APEX is a practical way to allow users to remove records directly from the report interface. This feature enhances user interaction by providing a straightforward method to manage data without navigating away from the report page. Implementing a delete button involves combining SQL, page processes, and Dynamic Actions to ensure safe and efficient record deletion within your application.

Adding a delete button to a Classic Report in Oracle APEX allows users to delete specific records directly from the report, improving usability and efficiency. Here is a detailed step-by-step explanation of how to add this functionality:

1. Create a Delete Button Column in the SQL Query
   Modify the SQL query used by your Classic Report to include a column that generates a delete link or button for each row. Use HTML anchor tags with URL syntax to call the current page or a processing page, passing the primary key of the row to be deleted as a parameter. For example:

   SELECT EMPLOYEE_ID,
          FIRST_NAME,
          LAST_NAME,
          '<a href="f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:DELETE_EMPLOYEE::P_EMPLOYEE_ID:' || EMPLOYEE_ID || '" 
           onclick="return confirm(''Are you sure you want to delete this record?'');">Delete</a>' AS DELETE_LINK
   FROM EMPLOYEES
   

   This creates a “Delete” link for each employee, passing the employee ID as a page item.

2. Create a Page Item to Hold the ID to Delete
   In the page where the Classic Report exists, create a hidden page item (for example, PXX_EMPLOYEE_ID) to receive the primary key of the record to be deleted. This page item will be used in the delete process.

3. Create a Branch or a Process to Handle Deletion
   Add a process on the page that runs before header or after submit that checks if the delete operation should be performed. Use a condition to run this process only when the request is DELETE_EMPLOYEE (matching the request value used in the link). In the PL/SQL code, delete the record based on the page item value:

   BEGIN
     DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = :PXX_EMPLOYEE_ID;
     COMMIT;
   END;
   

4. Redirect Back to the Report Page
   After the deletion, add a branch to redirect the user back to the same page to refresh the Classic Report and reflect the changes.

5. Add Confirmation Prompt
   The onclick JavaScript in the delete link confirms the user action before proceeding, preventing accidental deletions.

6. Optional: Use Dynamic Actions for Enhanced UX
   Instead of a full page reload, you can use Dynamic Actions and AJAX callbacks to delete the record asynchronously, refreshing only the report region. This improves the user experience but requires additional configuration.

By following these steps, you create an effective delete button inside a Classic Report that is secure, intuitive, and integrates well with Oracle APEX’s architecture. It allows users to manage data efficiently without leaving the report page.

Example

Adding a delete button to a Classic Report in Oracle APEX allows users to remove records directly from the report without navigating to another page. This tutorial explains multiple approaches, including using JavaScript with AJAX, PL/SQL processes, and dynamic actions to achieve a seamless and interactive delete functionality.

Why Add a Delete Button in a Classic Report?

• Allows users to remove records quickly without navigating away.

• Enhances user experience by making the application more interactive.

• Reduces the need for additional pages dedicated to delete actions.

• Can be integrated with confirmation dialogs for safety.

Step 1: Modify the Classic Report SQL Query

To include a delete button, modify the SQL query to generate an HTML button inside the report.

SELECT 

    empno, 

    ename, 

    job,

    '<button class="delete-button" data-id="' || empno || '">Delete</button>' AS delete_action

FROM emp

Explanation:

• The delete-button class is added for targeting via JavaScript.

• The data-id attribute stores the empno value, which will be used in the delete action.

Step 2: Create a PL/SQL Process for Deleting the Record

Create an On-Demand AJAX Process that deletes the record from the database.

1. Navigate to Shared Components > Application Processes.

2. Click Create and set the process type to On-Demand: Process (Ajax Callback).

3. Name the process DELETE_EMP_RECORD.

4. Use the following PL/SQL code:

BEGIN

    DELETE FROM emp WHERE empno = :P0_EMPNO;

    COMMIT;

END;

This process deletes the employee record based on the employee number (empno).

Step 3: Add JavaScript to Handle the Delete Action

1. Go to Page Designer and open the Classic Report's properties.

2. In the Page Attributes, navigate to JavaScript > Function and Global Variable Declaration.

3. Add the following JavaScript code:

$(document).ready(function() {

    $(".delete-button").click(function() {

        var empId = $(this).data("id");

        if (confirm("Are you sure you want to delete this record?")) {

            $.ajax({

                url: "wwv_flow.show",

                type: "POST",

                data: {

                    "p_request": "APPLICATION_PROCESS=DELETE_EMP_RECORD",

                    "p_flow_id": $v("pFlowId"), 

                    "p_flow_step_id": $v("pFlowStepId"),  

                    "p_instance": $v("pInstance"),  

                    "P0_EMPNO": empId

                },

                success: function(response) {

                    alert("Record deleted successfully!");

                    location.reload();  

                },

                error: function() {

                    alert("Error deleting record.");

                }

            });

        }

    });

});

Explanation:

• When the delete button is clicked, it retrieves the empno from the data-id attribute.

• A confirmation popup ensures users confirm before deleting.

• The AJAX request calls the DELETE_EMP_RECORD process, passing the empno.

• Upon success, the page refreshes to reflect the deleted record.

Step 4: Add a Confirmation Dialog for Better UX (Optional)

To use an APEX built-in confirmation dialog instead of confirm(), create a Dynamic Action:

1. Select the Classic Report region.

2. Click Create Dynamic Action.

3. Set Event to Click.

4. Set Selection Type to jQuery Selector and enter .delete-button.

5. Add an Action: Execute JavaScript Code.

6. Enter this JavaScript:

var empId = $(this.triggeringElement).data("id");

apex.confirm("Are you sure you want to delete this record?", {

    request: "DELETE_" + empId

});

7. Create a PL/SQL Process (Before Header) with:

DECLARE

    v_empno NUMBER;

BEGIN

    v_empno := TO_NUMBER(SUBSTR(:REQUEST, 8)); 

    DELETE FROM emp WHERE empno = v_empno;

    COMMIT;

END;

8. Set the Condition to REQUEST LIKE 'DELETE_%'.

This method enhances user experience by utilizing APEX’s built-in confirmation modal.

Step 5: Using a Trash Icon Instead of a Button (Alternative)

For a more modern UI, replace the button with an icon using Font Awesome.

Modify the SQL query to:

SELECT 

    empno, 

    ename, 

    job,

    '<i class="fa fa-trash delete-icon" data-id="' || empno || '"></i>' AS delete_action

FROM emp

Update the CSS:

.delete-icon {

    color: red;

    cursor: pointer;

    font-size: 18px;

}

.delete-icon:hover {

    color: darkred;

}

This provides a cleaner interface with a trash icon instead of a button.

Best Practices for Adding a Delete Button

• Always use a confirmation dialog to prevent accidental deletions.

• Ensure that the PL/SQL process uses a COMMIT; statement to save changes.

• Use APEX built-in modal dialogs instead of confirm() for better UI consistency.

• Apply CSS styles to improve button visibility and usability.

• Implement role-based authorization to restrict who can delete records.

Adding a delete button in a Classic Report allows users to remove records directly from the report while maintaining a smooth user experience. By using PL/SQL processes, JavaScript, AJAX, and dynamic actions, you can implement a secure and interactive delete feature. Customizing the button with CSS or replacing it with an icon can further improve usability.

EXAMPLE: 

Step 1 - Create your button here and copy the generated html.

Button Builder - Universal Theme (oracle.com)

In this case, the HTML code is as follows:

<button type="button" 

class="t-Button t-Button--icon t-Button--hot t-Button--danger t-Button--simple t-Button--iconLeft">

<span aria-hidden="true" class="t-Icon t-Icon--left fa fa-trash">

</span>Delete

</button>

Step 2- In your report, add a virtual column

Step 3- In the column’s type change from “Link” to “Plain Text”

Step 4-  in the column’s  HTML Expression enter the button ‘s mark up code from Step 1

In conclusion, adding a delete button to a Classic Report empowers users with convenient data management capabilities while maintaining control and security through APEX’s built-in features. With careful configuration, developers can provide a seamless and intuitive experience that integrates record deletion directly into the reporting workflow.