How Do I Create a Stored procedure for DELETE and execute the procedure

 Introduction

Creating a stored procedure for deleting data from a table in Oracle APEX is a secure and efficient way to manage deletion logic. Instead of writing DELETE statements directly on multiple pages, a stored procedure allows you to centralize and control how records are removed from the database. This is especially useful when you want to apply conditions, logging, or validation before deleting data. With the procedure in place, you can execute it easily from buttons, processes, or PL/SQL blocks within your APEX application.

Creating a stored procedure to delete a record from a table in Oracle APEX is a safe and structured way to manage data removal. Instead of writing DELETE statements directly inside page processes or buttons, the logic is stored in the database, making it reusable, secure, and easier to maintain.

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: Create the Stored Procedure
Write a PL/SQL procedure using the CREATE OR REPLACE PROCEDURE syntax. Below is a basic example for deleting an employee by ID:

CREATE OR REPLACE PROCEDURE delete_employee (
  p_employee_id IN NUMBER
)
AS
BEGIN
  DELETE FROM employees
  WHERE employee_id = p_employee_id;
END;

Explanation:

• The procedure is named delete_employee.

• It accepts one input parameter: p_employee_id.

• The procedure performs a DELETE operation based on the employee ID.

Step 3: Execute the Procedure to Test It
You can test the procedure using an anonymous PL/SQL block in SQL Commands:

BEGIN
  delete_employee(101);
END;

This will delete the row where employee_id = 101, assuming the record exists.

Step 4: Use the Procedure in Your APEX Application
To call the procedure from a button or process in your APEX page:

1. In Page Designer, create a button (for example, "Delete").

2. Under the Processing section, create a new PL/SQL process.

3. Set the execution point to After Submit or when the button is clicked.

4. Use the following code in the process:

BEGIN
  delete_employee(:P1_EMPLOYEE_ID);
END;

Make sure that P1_EMPLOYEE_ID is a page item holding the employee ID you want to delete.

Step 5: Add Success Message and Validation (Optional)
You can display a success message by setting the process message property or add a validation process to confirm that the ID exists before deletion.

Best Practices

• Always validate the input parameter to avoid accidental deletions.

• Include exception handling inside the procedure using EXCEPTION WHEN OTHERS THEN.

• Use audit logging if you need to track deleted records.

• Grant EXECUTE privilege if the procedure is used by another schema.

By using a stored procedure for deleting records, you gain centralized control over data removal logic, reduce duplication across your application, and ensure that deletions follow consistent rules. This method enhances security, reusability, and code clarity in Oracle APEX.

Conclusion
Using a stored procedure for deleting records gives you better control, reusability, and maintainability of your data operations in Oracle APEX. It ensures that deletion follows consistent business rules and reduces the chance of accidental data loss. Executing the procedure from your application is straightforward, and it also allows you to incorporate exception handling and validation logic as needed. This approach improves both the reliability and security of your APEX solutions.

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;  

How Do I Use SQL code for creating and using Stored Procedures

 Introduction

Using SQL code to create and manage stored procedures is an essential part of developing efficient and organized applications in Oracle APEX. Stored procedures allow you to encapsulate SQL and PL/SQL logic into reusable blocks that can be called from different parts of your application, such as buttons, processes, or dynamic actions. This helps improve performance, maintainability, and code reusability by centralizing business logic within the database.

In Oracle APEX, stored procedures are blocks of PL/SQL code stored in the database that can be called repeatedly to perform tasks such as inserting data, validating input, or processing business rules. Creating and using stored procedures helps keep your application logic organized, reusable, and easier to maintain.

Step 1: Open SQL Workshop
Log in to Oracle APEX and go to the main menu. Click on SQL Workshop, then select SQL Commands or Object Browser to create and manage stored procedures.

Step 2: Write SQL Code to Create a Stored Procedure
You can use the CREATE OR REPLACE PROCEDURE statement to define a stored procedure. Below is an example of a simple procedure that inserts a record into a table called employees.

CREATE OR REPLACE PROCEDURE add_employee (
  p_first_name   IN VARCHAR2,
  p_last_name    IN VARCHAR2,
  p_email        IN VARCHAR2
)
AS
BEGIN
  INSERT INTO employees (first_name, last_name, email)
  VALUES (p_first_name, p_last_name, p_email);
END;

Explanation:

• The procedure is named add_employee.

• It accepts three input parameters: p_first_name, p_last_name, and p_email.

• The BEGIN...END block contains the SQL logic to insert a new row into the employees table.

Step 3: Execute the SQL Code
Click the Run or Execute button in SQL Commands. If the syntax is correct and you have privileges, the procedure will be created in your schema.

Step 4: View and Manage Stored Procedures
To view your stored procedure:

1. Go to SQL Workshop

2. Click on Object Browser

3. Under Procedures, find and click on your procedure name
   You can see the source code, compile status, and edit or drop the procedure from here.

Step 5: Call the Stored Procedure from APEX
You can call the stored procedure from APEX using PL/SQL processes, page buttons, or dynamic actions.

Example: Add a button to a form page that calls the procedure on submit. In the button’s process, write:

BEGIN
  add_employee(:P1_FIRST_NAME, :P1_LAST_NAME, :P1_EMAIL);
END;

This will pass values from page items to the stored procedure and execute it.

Best Practices

• Use clear naming conventions for procedures and parameters.

• Include exception handling inside your procedure using EXCEPTION WHEN OTHERS THEN.

• Keep your business logic in the procedure to make it reusable across different pages.

• Recompile procedures if you make changes to referenced objects.

• Grant appropriate execution privileges if used across schemas.

Using SQL code to create and use stored procedures in Oracle APEX gives you strong control over your business logic and data processes, allowing you to build robust and maintainable applications.

Conclusion

Creating and using stored procedures with SQL code allows Oracle APEX developers to separate business logic from the user interface, resulting in cleaner, more modular applications. Whether you're performing complex calculations, managing transactions, or automating processes, stored procedures offer a powerful way to control how your application interacts with the database. Mastering stored procedure creation is a key skill for building scalable and maintainable APEX solutions.

How Do I Use SQL code for creating a table

 Introduction

Creating a table using SQL is one of the foundational skills in working with databases and is especially useful when developing applications in Oracle APEX. A table is a structured object that stores data in rows and columns, and writing SQL code to create one gives you full control over its structure. Whether you’re designing a form, building a report, or storing user input, understanding how to write the CREATE TABLE statement is essential for efficient database development.

How Do I Use SQL Code for Creating a Table in Oracle APEX

Font: Arial | Format: Plain Text

Creating a table using SQL code in Oracle APEX is a basic but important task for defining the structure of your application's data. Tables are used to store records in rows and columns, and each column is defined with a specific data type and optional constraints. You use the CREATE TABLE SQL statement to define a new table in the Oracle database that your APEX application will use.

Step 1: Open SQL Workshop
Log in to Oracle APEX. From the App Builder home screen, go to the main menu and click on SQL Workshop. From there, choose SQL Commands to open the SQL editor.

Step 2: Write the CREATE TABLE Statement
In the SQL Commands editor, type the SQL code to create your table. Here is a simple example:

CREATE TABLE employees (
  employee_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  email         VARCHAR2(100) UNIQUE,
  hire_date     DATE,
  status        VARCHAR2(20) DEFAULT 'Active'
);

Explanation of the code:

• employee_id is a unique identifier with an auto-incrementing number.

• first_name, last_name, and email are text fields with specified length.

• email is marked as UNIQUE to prevent duplicates.

• hire_date is a date column to store when the employee was hired.

• status has a default value of 'Active'.

Step 3: Execute the SQL Code
After writing the SQL command, click the Run or Execute button. If the syntax is correct and you have the necessary privileges, the table will be created in your schema.

Step 4: Verify the Table
To confirm the table has been created:

1. In SQL Workshop, go to Object Browser.

2. Find your table under the Tables section.

3. Click on it to view its structure, columns, and data.

Step 5: Use the Table in Your APEX App
Now that the table exists, you can:

• Create a form or report on the table using the APEX Page Designer.

• Insert data using SQL Commands or through APEX forms.

• Write additional SQL queries to select, update, or delete records.

Best Practices

• Always name your columns clearly and consistently.

• Use appropriate data types and sizes for each column.

• Apply primary keys and constraints to maintain data integrity.

• Avoid using reserved words as table or column names.

• Use comments in your SQL to document the purpose of each column when needed.

Using SQL code to create tables gives you full control over your database design and allows you to build a strong foundation for your Oracle APEX application.

Conclusion

Using SQL code to create tables allows developers to define exactly how data will be stored, accessed, and related within an Oracle APEX application. By mastering the CREATE TABLE syntax, including data types, constraints, and keys, you can ensure your application's data layer is well-structured and optimized for performance. This knowledge forms the backbone of building scalable, flexible, and professional-grade APEX applications.

Important Information to Know About Oracle SQL for APEX Development

 Oracle SQL is the backbone of Oracle APEX applications, as it provides the means to retrieve, manipulate, and store data efficiently. Whether you're designing forms, reports, or dashboards, understanding key Oracle SQL features can significantly improve your APEX development experience.

1. Understanding SQL Basics in Oracle APEX

Oracle SQL (Structured Query Language) is used to interact with the database. APEX provides a low-code interface, but SQL remains essential for:

• Retrieving data for reports

• Inserting, updating, and deleting records

• Performing calculations and aggregations

• Managing database objects such as tables, indexes, and views

Example SQL Query for an APEX Report:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY 

FROM EMPLOYEES 

WHERE DEPARTMENT_ID = :P1_DEPT_ID;

In this query, :P1_DEPT_ID is a bind variable referring to a page item in APEX.

2. Using SQL Bind Variables in APEX

Bind variables improve security and performance by ensuring queries are dynamically executed with user input.

Example in a PL/SQL Process:

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID)

VALUES (:P2_EMP_ID, :P2_FNAME, :P2_LNAME, :P2_DEPT);

This prevents SQL injection and enhances execution efficiency.

3. SQL Joins for Combining Data from Multiple Tables

Oracle APEX frequently requires joining multiple tables to create reports and dashboards.

Common types of joins:

• Inner Join – Returns matching records from both tables

• Left Join – Returns all records from the left table and matching records from the right

• Right Join – Returns all records from the right table and matching records from the left

• Full Outer Join – Returns all records when there is a match in either table

Example of an Inner Join in a Classic Report:

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME

FROM EMPLOYEES E

JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

This query combines employee and department information.

4. Using SQL Aggregation for APEX Reports

Aggregation functions help generate summarized reports in APEX.

Common aggregation functions:

• SUM(): Adds up numeric values

• AVG(): Calculates the average value

• COUNT(): Counts the number of records

• MIN() and MAX(): Retrieve the smallest and largest values

Example of a Summary Report Query:

SELECT DEPARTMENT_ID, COUNT(*) AS EMP_COUNT, AVG(SALARY) AS AVG_SALARY

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

This query generates a report showing the number of employees and average salary per department.

5. SQL Analytical Functions for Advanced Reporting

Analytical functions allow row-by-row calculations over a dataset without reducing the number of rows.

Example using ROW_NUMBER() to rank employees by salary:

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, 

       ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RANK

FROM EMPLOYEES;

This ranks employees based on salary, with the highest-paid employee being ranked 1.

6. Using SQL Subqueries in APEX Applications

A subquery is a query inside another query, useful for filtering data dynamically.

Example: Find employees with salaries higher than the average salary:

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY

FROM EMPLOYEES

WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

This allows for dynamic comparisons in reports.

7. Creating Views for Reusable SQL Queries

Views are virtual tables based on SQL queries, allowing for easier report generation.

Example: Creating a view for employee details:

CREATE VIEW EMPLOYEE_DETAILS AS 

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME, E.SALARY 

FROM EMPLOYEES E

JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

Instead of writing a complex join repeatedly, you can now use:

SELECT * FROM EMPLOYEE_DETAILS;

Views help simplify queries, improve code reusability, and enhance security by restricting direct access to tables.

8. Using SQL in APEX Interactive Reports and Grids

Interactive Reports and Interactive Grids allow users to filter, sort, and export data dynamically.

Example SQL for an Interactive Report:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY 

FROM EMPLOYEES 

WHERE DEPARTMENT_ID = :P1_DEPT_ID

ORDER BY HIRE_DATE DESC;

The user selects a department (P1_DEPT_ID), and the report updates automatically.

9. Using SQL to Manage Data (DML Statements)

APEX applications often include forms for inserting, updating, and deleting records.

• Insert Data

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY)

VALUES (SEQ_EMP.NEXTVAL, 'John', 'Doe', 50000);

• Update Data

UPDATE EMPLOYEES 

SET SALARY = 60000

WHERE EMPLOYEE_ID = 101;

• Delete Data

DELETE FROM EMPLOYEES 

WHERE EMPLOYEE_ID = 101;

These statements can be linked to buttons, dynamic actions, or processes in APEX.

10. Securing SQL Queries in Oracle APEX

Security is critical in APEX applications, especially when dealing with user input.

• Always Use Bind Variables to prevent SQL injection.

• Limit SQL Privileges – Avoid using GRANT ALL and provide only necessary access.

• Use Virtual Private Database (VPD) to enforce row-level security in APEX reports.

Example: Sanitizing User Input in a PL/SQL Process

DECLARE

    V_EMP_ID NUMBER;

BEGIN

    V_EMP_ID := TO_NUMBER(:P2_EMP_ID);  -- Converts input to a number safely

    DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_ID;

END;

This prevents malicious input from causing unintended SQL execution.

Mastering Oracle SQL is essential for building powerful and scalable APEX applications. Understanding key SQL concepts—such as joins, subqueries, aggregation, analytical functions, views, and security practices—allows developers to create efficient, secure, and user-friendly applications.

By applying these SQL techniques, you can optimize performance, enhance user experience, and ensure the reliability of your APEX applications.