How Do I Create a stored procedure for displaying SELECT from a table in a report

 Introduction

Creating a stored procedure to perform a SELECT query from a table and display the results in an Oracle APEX report is a useful technique when you want to centralize complex query logic or return custom result sets. Instead of embedding SQL directly in the report region, you can write a PL/SQL stored procedure that returns a ref cursor. This approach improves code maintainability, separates business logic from the UI, and gives you greater control over how data is retrieved and presented.

In Oracle APEX, you can use a stored procedure with a ref cursor to return data from a table and display it in a report region. This approach is useful when you want to encapsulate complex query logic in the database and reuse it across multiple pages or applications. The procedure returns a result set using a SYS_REFCURSOR, which APEX can then use as the source for a report.

Step 1: Open SQL Workshop
Log in to Oracle APEX and go to SQL Workshop. Click on SQL Commands to create the stored procedure.

Step 2: Create the Stored Procedure
Use SQL to define a procedure that returns a ref cursor. Here is an example that selects data from the EMPLOYEES table:

CREATE OR REPLACE PROCEDURE get_employees (
  p_result OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN p_result FOR
    SELECT employee_id, first_name, last_name, email, hire_date
    FROM employees
    ORDER BY hire_date DESC;
END;

This procedure defines an OUT parameter p_result that holds the result set. Inside the procedure, the cursor is opened with a SELECT statement from the employees table.

Step 3: Create a Report Region in APEX
Go to your APEX application, open the page where you want the report, and enter Page Designer.

1. Create a new region.

2. Set the region type to Report → Classic Report or Interactive Report.

3. Set the Source Type to PL/SQL Function Returning SQL Query.

Step 4: Use the Stored Procedure in the Report Source
Enter the following code in the Source area:

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  get_employees(l_cursor);
  RETURN l_cursor;
END;

This block calls the stored procedure and returns the ref cursor as the report data source.

Step 5: Run and Test the Page
Click Run. Your report should display the data returned by the stored procedure. If the procedure is valid and the table has data, the results will be shown in the report region.

Optional Step: Add Parameters
You can modify the procedure to accept parameters for filtering. For example:

CREATE OR REPLACE PROCEDURE get_employees_by_dept (
  p_dept_id IN NUMBER,
  p_result OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN p_result FOR
    SELECT employee_id, first_name, last_name, email
    FROM employees
    WHERE department_id = p_dept_id;
END;

Then in APEX, you can pass a page item like this:

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  get_employees_by_dept(:P1_DEPT_ID, l_cursor);
  RETURN l_cursor;
END;

Best Practices
Use clear and meaningful names for procedures and parameters.
Add exception handling if needed.
Keep the SELECT statement optimized for performance.
Use bind variables instead of hardcoding values.
Recompile procedures if you change table structures or logic.

Using a stored procedure with a ref cursor to display data in a report allows you to keep SQL logic in the database, improve reusability, and simplify maintenance across your Oracle APEX application.

Conclusion

Using a stored procedure to return data for a report allows you to organize and reuse query logic while keeping your APEX pages cleaner and more modular. It is especially helpful for complex data retrieval or when the same logic needs to be used across multiple reports. By leveraging ref cursors and PL/SQL procedures, you create a more flexible and scalable foundation for your Oracle APEX applications.

How Do I Create a stored procedure for UPDATING a table

 Introduction

Creating a stored procedure for updating a table in Oracle APEX is a smart way to centralize and manage your data modification logic. Instead of writing repetitive SQL update statements across multiple pages or processes, a stored procedure allows you to write the logic once and call it wherever needed. This improves maintainability, security, and consistency throughout your application, especially when updating rows based on parameters passed from the user interface.

How Do I Create a Stored Procedure for Updating a Table in Oracle APEX
Font: Arial | Format: Plain Text

Creating a stored procedure to update a table in Oracle APEX allows you to encapsulate the update logic in one place, making your application easier to manage and reuse. You can call this procedure from forms, buttons, or processes inside your APEX app.

Step 1: Open SQL Workshop
Log in to Oracle APEX. From the main menu, go to SQL Workshop and click on SQL Commands.

Step 2: Write the Stored Procedure Code
Use the CREATE OR REPLACE PROCEDURE statement to define the logic. Below is an example that updates an employee’s email address based on their employee ID.

CREATE OR REPLACE PROCEDURE update_employee_email (
  p_employee_id IN NUMBER,
  p_new_email   IN VARCHAR2
)
AS
BEGIN
  UPDATE employees
  SET email = p_new_email
  WHERE employee_id = p_employee_id;
END;

In this example:

• The procedure is named update_employee_email.

• It accepts two input parameters: p_employee_id and p_new_email.

• It performs an UPDATE operation on the employees table.

Step 3: Execute the Procedure Code
Click the Run or Execute button to compile the procedure. If there are no errors, the procedure will be created and stored in your schema.

Step 4: Test the Procedure
You can test the procedure by running an anonymous PL/SQL block like this:

BEGIN
  update_employee_email(101, 'newemail@example.com');
END;

This will update the email of the employee with ID 101.

Step 5: Call the Procedure from Your APEX App
In Page Designer, create a button or process that submits data. Under the processing section, add a PL/SQL process with code like this:

BEGIN
  update_employee_email(:P1_EMPLOYEE_ID, :P1_EMAIL);
END;

Make sure that P1_EMPLOYEE_ID and P1_EMAIL are page items that hold the input values.

Step 6: Add Validations if Needed
You can add validations to check if the email is in the correct format or if the employee ID exists before calling the procedure.

Best Practices
Use clear parameter names.
Add error handling using EXCEPTION blocks.
Keep the procedure logic simple and focused.
Grant EXECUTE permission if the procedure needs to be accessed from another schema.

Creating a stored procedure for updating a table helps you manage business logic in one place, improves security, and reduces repeated code across your APEX application. It makes your updates cleaner, safer, and easier to maintain.

Conclusion

Using a stored procedure to update a table simplifies your Oracle APEX application by encapsulating the update logic in a reusable and secure way. It ensures that updates follow a consistent process and allows for easier future changes without touching multiple pages. By calling the procedure from page processes, buttons, or dynamic actions, you maintain clean code separation and gain better control over how and when your data is modified.

Example:

CREATE OR REPLACE PROCEDURE UPDATE customer (   

p_name IN CUSTOMER.NAME%TYPE,  

p_gender IN CUSTOMER.GENDER%TYPE,  

p_address IN CUSTOMER.ADDRESS%TYPE)  

IS  

BEGIN  

UPDATE CUSTOMER SET NAME=p_name, GENDER=p_gender, ADDRESS=p_address WHERE NAME=p_name;  

COMMIT;  

END;