How Do I Use Form Fields in a List of Values (LOV)

Introduction
In Oracle APEX, form fields can be used to dynamically populate Lists of Values (LOVs), enabling users to select values that are relevant and context-specific. By linking page items as parameters within LOV queries, you can create responsive dropdowns that change based on user input or other form fields. This makes your application more interactive and user-friendly by displaying only applicable options.

How Do I Use Form Fields in a List of Values (LOV)
Font: Arial, font size: 14px, plain text

In Oracle APEX, you can use form fields (page items) to dynamically filter and populate Lists of Values (LOVs) based on user input. This technique helps create responsive and context-sensitive dropdowns that improve user experience and data accuracy.

Step 1: Create Page Items
Identify or create the form fields (page items) you want to use as filters in your LOV. For example, you might have a department select list :P1_DEPARTMENT that will control the employee LOV.

Step 2: Write the LOV SQL Query Using Bind Variables
In the LOV definition, write an SQL query that references the form fields as bind variables. For example:

SELECT employee_name, employee_id
FROM employees
WHERE department_id = :P1_DEPARTMENT
ORDER BY employee_name

Here, :P1_DEPARTMENT is the page item whose value dynamically filters the employees shown in the LOV.

Step 3: Set Up the LOV for the Target Item
Assign this LOV query to the page item (such as a select list) that will display the filtered values, for example :P1_EMPLOYEE.

Step 4: Configure Cascading LOV Parent Items
In Page Designer, set the Cascading LOV Parent Item property of the target item (P1_EMPLOYEE) to the controlling page item (P1_DEPARTMENT). This tells APEX that when the parent value changes, the child LOV should refresh.

Step 5: Enable AJAX Refresh
Make sure the target LOV page item has Cascading LOV Parent Item set and that Page Items to Submit include the controlling item(s) if necessary. This enables APEX to submit the controlling item’s value when refreshing the LOV dynamically without a full page reload.

Step 6: Test Your LOV
Run the page and change the controlling form field (P1_DEPARTMENT). The LOV on the dependent item (P1_EMPLOYEE) should update immediately, showing only values that match the selected department.

Best Practices

• Always use bind variables (:P1_ITEM) in your LOV queries to avoid SQL injection and improve performance.

• Set appropriate Cascading LOV Parent Item properties to enable dynamic refreshes.

• Include parent items in Page Items to Submit if you use dynamic actions or custom PL/SQL.

• Test the LOV behavior across all expected input scenarios to ensure it updates correctly.

Using form fields in LOVs is an effective way to build interactive and user-friendly forms in Oracle APEX that respond dynamically to user selections.

Form fields can be used to dynamically populate dropdown lists (LOVs) in Oracle APEX.

Example: Creating a Cascading Select List
When a value is selected in P1_DEPARTMENT, the P1_EMPLOYEE dropdown will show only employees from that specific department.

1. Create a Select List for P1_DEPARTMENT with this query:

SELECT department_name, department_id  
FROM departments  
ORDER BY department_name;

2. Create a Select List for P1_EMPLOYEE using:

SELECT emp_name, emp_id  
FROM employees  
WHERE department_id = :P1_DEPARTMENT  
ORDER BY emp_name;

3. In Page Designer, set the Cascading LOV Parent property of P1_EMPLOYEE to P1_DEPARTMENT.

4. Enable AJAX Refresh for P1_EMPLOYEE so the employee list updates dynamically whenever the department selection change.

Conclusion

Using form fields in LOVs is a powerful way to customize user selections and streamline data entry in Oracle APEX applications. By leveraging dynamic queries and cascading LOVs, you can ensure that dropdown lists remain relevant to the user’s current context, improving both usability and data accuracy. Implementing this technique helps build smarter, more responsive forms. 

How Do I Use Form Fields in Interactive Reports and Classic Reports

 Introduction

In Oracle APEX, form fields (page items) can be used effectively with Interactive Reports and Classic Reports to create dynamic and user-driven data views. By linking form field values to report queries, you enable users to filter, search, and customize the data displayed without needing complex coding. This makes reports more flexible and responsive to user input, enhancing the overall application experience.

In Oracle APEX, you can use form fields (page items) to filter and customize data shown in Interactive Reports and Classic Reports. This approach lets users control what data they want to see by entering values or selecting options in form fields, making reports dynamic and user-friendly.

Step 1: Create or Identify Form Fields
Create page items (such as text fields, select lists, or date pickers) that users will use to enter filter criteria. For example, a text item :P1_EMPLOYEE_NAME or a select list :P1_DEPARTMENT_ID.

Step 2: Reference Form Fields in the Report SQL Query
Modify the SQL query of your Interactive Report or Classic Report to include the page items as bind variables. For example:

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE (:P1_EMPLOYEE_NAME IS NULL OR LOWER(first_name) LIKE '%' || LOWER(:P1_EMPLOYEE_NAME) || '%')
  AND (:P1_DEPARTMENT_ID IS NULL OR department_id = :P1_DEPARTMENT_ID)

This query filters employees by name and department only if those page items have values.

Step 3: Ensure Page Items Are Submitted
To make sure the report query sees the current values of page items, add a Dynamic Action on the form fields that submits those items and refreshes the report region.

• Create a Dynamic Action on the page item’s Change event.

• Add a True action to Submit Page Items and list the form fields.

• Add another True action to Refresh the report region.

Step 4: Set Default Values and Null Handling
To avoid filtering out all data when a form field is empty, write SQL to handle null values appropriately (as shown above). This way, if the form field is blank, the filter is ignored.

Step 5: Test the Filtering
Run your page, enter values in the form fields, and verify that the Interactive Report or Classic Report updates accordingly with the filtered results.

Best Practices

• Use bind variables (:P1_ITEM_NAME) to avoid SQL injection and improve performance.

• Handle null or empty values in your SQL to prevent unintended filtering.

• Submit only necessary page items to optimize performance.

• Use Dynamic Actions to provide immediate feedback without full page reloads.

By integrating form fields with Interactive and Classic Reports, Oracle APEX enables you to build interactive, efficient, and user-friendly reporting pages that respond instantly to user input.

Reports can be dynamically filtered based on form field values.

Example: Filter an Interactive Report Based on a Form Field

• Create a form field (P1_EMPLOYEE_NAME).

• In Interactive Report Attributes, set the query as:

SELECT emp_id, emp_name, department, salary  

FROM employees  

WHERE emp_name LIKE '%' || :P1_EMPLOYEE_NAME || '%';

• When a user enters text in P1_EMPLOYEE_NAME, the report updates to show matching employee names.

Example: Filter a Classic Report Based on a Form Field

• Create a Classic Report and use this query:

SELECT order_id, customer_name, total_amount  

FROM orders  

WHERE customer_name = :P1_CUSTOMER_NAME;

• Assign a Dynamic Action to refresh the report when P1_CUSTOMER_NAME is changed.

Conclusion

Using form fields in Interactive and Classic Reports allows you to build powerful, customizable data displays in Oracle APEX. By connecting page items to report queries, users can interactively control what data they see, improving usability and relevance. Implementing this technique is key to creating dynamic applications that adapt seamlessly to user needs.

How Do I Refer Form Fields in SQL Queries

 In APEX, form fields (page items) can be referenced in SQL using bind variables. Bind variables prevent SQL injection and improve query performance.

Syntax for Using a Form Field in SQL

SELECT column1, column2  

FROM table_name  

WHERE column3 = :P1_FORM_ITEM;

• :P1_FORM_ITEM is a bind variable that represents the value entered by the user in the P1_FORM_ITEM field.

• The query dynamically filters results based on the user input.

Using Form Fields in a SQL Query in Oracle APEX

 Introduction

In Oracle APEX, form fields (also known as page items) can be directly used in SQL queries to create dynamic and responsive applications. By referencing form field values in your SQL code, you can filter results, calculate outputs, or control logic based on user input. This feature allows developers to create more interactive and personalized user experiences by linking query behavior to the current values entered on the screen.

In Oracle APEX, you can use form fields, also known as page items, directly within your SQL queries to create dynamic and interactive applications. This allows the query to respond to user input, filtering data or altering results based on the values entered in the form.

Step 1: Identify the Page Items
Page items typically have names like :P1_ITEM_NAME, where P1 is the page number and ITEM_NAME is the name of the form field. These are bind variables you use inside SQL.

Step 2: Reference Page Items in Your SQL Query
When writing SQL queries for reports, interactive grids, or regions, include the page items as bind variables. For example:

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = :P1_DEPARTMENT_ID

Here, :P1_DEPARTMENT_ID is a page item whose value filters the employees by department.

Step 3: Use NVL or COALESCE to Handle Nulls
If the page item can be null (for example, no filter selected), use functions like NVL or COALESCE to provide default behavior:

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = NVL(:P1_DEPARTMENT_ID, department_id)

This query returns all employees if :P1_DEPARTMENT_ID is null.

Step 4: Use Bind Variables in PL/SQL or Processes
You can also use page items as bind variables in PL/SQL blocks, page processes, and computations by referencing them with a colon:

DECLARE
  v_dept_id NUMBER := :P1_DEPARTMENT_ID;
BEGIN
  -- Your PL/SQL logic here using v_dept_id
END;

Step 5: Refresh Regions After User Input
To make sure that regions or reports reflect changes to page items, use Dynamic Actions to refresh the affected region after the user updates a form field.

Best Practices

• Always use bind variables (:P1_ITEM_NAME) instead of concatenating values into SQL to prevent SQL injection.

• Ensure page items have the correct session state before running queries by setting appropriate "Page Item to Submit" properties in dynamic actions or processes.

• Use default values or null handling to avoid empty or error-prone queries.

• Test queries thoroughly to verify they react as expected to different input values.

By using form fields directly in your SQL queries, Oracle APEX enables you to build highly interactive and data-driven applications that react instantly to user inputs.

Conclusion
Using form fields in SQL queries within Oracle APEX provides a flexible way to connect user input with live data results. Whether you're filtering a report, retrieving related data, or applying conditions, referencing page items in your SQL ensures that your application stays responsive to user actions. When used carefully and securely, this technique can greatly enhance the functionality and interactivity of your APEX pages.

Security Best Practices in PL/SQL

 Introduction

Security in PL/SQL is a critical aspect of building safe and reliable Oracle APEX applications. Since PL/SQL often interacts directly with the database, applying security best practices helps protect sensitive data, prevent unauthorized access, and avoid vulnerabilities like SQL injection. By writing secure PL/SQL code, you can ensure that your application logic is not only functional but also protected against misuse and attacks.

Security Best Practices in PL/SQL
Font: Arial, font size: 14px, plain text

When writing PL/SQL code in Oracle APEX, applying strong security practices is critical to protect your database and application from threats like SQL injection, privilege misuse, and data leakage. Below are the key best practices to follow when developing PL/SQL code for your APEX applications.

1. Use Bind Variables
Always use bind variables (e.g., :P1_ITEM) instead of concatenating values directly into SQL statements. This prevents SQL injection and ensures that values are properly escaped.

Bad Practice:

EXECUTE IMMEDIATE 'DELETE FROM users WHERE user_id = ' || :P1_USER_ID;

Good Practice:

EXECUTE IMMEDIATE 'DELETE FROM users WHERE user_id = :id' USING :P1_USER_ID;

2. Avoid Dynamic SQL When Possible
Dynamic SQL allows flexibility, but it can introduce vulnerabilities if not handled carefully. Prefer static SQL unless dynamic logic is absolutely necessary.

3. Validate All User Input
Never trust user input. Check for correct data types, expected ranges, and valid formats before using values in SQL or PL/SQL.

IF :P1_AGE < 0 THEN
  raise_application_error(-20001, 'Age must be a positive number.');
END IF;

4. Use the Least Privilege Principle
Grant only the permissions required for a procedure to function. Avoid using powerful roles like DBA in runtime environments. If a procedure only needs SELECT, do not grant UPDATE or DELETE.

5. Handle Exceptions Properly
Use exception handling to prevent your code from exposing sensitive information. Never return database error messages directly to the user.

EXCEPTION
  WHEN OTHERS THEN
    APEX_DEBUG.MESSAGE('An error occurred.');
    raise_application_error(-20002, 'Unexpected application error.');

6. Mask Sensitive Data
When logging or displaying error messages, do not reveal user data, passwords, or internal system logic. Always return friendly, generic messages to the front end.

7. Use APEX Utility Functions
Leverage Oracle APEX utility functions such as apex_escape.html() to sanitize outputs and prevent cross-site scripting (XSS) when outputting PL/SQL results on pages.

8. Avoid Hardcoding Credentials or Keys
Do not embed sensitive credentials or API keys inside PL/SQL code. Use secure APEX application settings or Oracle Wallet for external access when necessary.

9. Audit and Monitor Your Code
Regularly review PL/SQL logic for potential security holes. Enable logging, use APEX_DEBUG.MESSAGE in development, and consider auditing tools to track procedure usage.

10. Keep Your Environment Updated
Apply the latest Oracle patches and security updates. Vulnerabilities in underlying database components can also affect PL/SQL execution.

By following these best practices, you can ensure that your PL/SQL code in Oracle APEX is secure, reliable, and resilient against common threats. Writing secure code is not optional—it's an essential responsibility for protecting your users and data.

Conclusion
Following security best practices in PL/SQL is essential for maintaining the integrity, confidentiality, and reliability of your Oracle APEX applications. By using bind variables, managing privileges carefully, validating inputs, and avoiding dynamic SQL when possible, you can significantly reduce risks. Secure PL/SQL coding not only protects your data but also reinforces user trust and long-term application stability.

How Do I Use PL/SQL with APEX Collections

 Introduction

Oracle APEX Collections provide a flexible way to store and manage session-based data in memory, which is especially useful when dealing with temporary datasets like shopping carts, multi-step wizards, or user-specific records. By using PL/SQL with APEX Collections, developers can insert, update, delete, and query data stored in collections throughout the user's session. This allows for powerful data manipulation without committing anything to the database until needed.

How Do I Use PL/SQL with APEX Collections
Font: Arial, font size: 14px, plain text

In Oracle APEX, APEX Collections are in-memory structures used to store and manage temporary data specific to a user’s session. Using PL/SQL with collections allows you to manipulate session-specific data without committing it to a permanent table, which is useful in use cases such as shopping carts, wizard-style forms, or temporary staging areas.

Step 1: Create or Add Data to a Collection
To begin using a collection, you first need to create it. Collections are created automatically when you add data to them using the APEX_COLLECTION.ADD_MEMBER procedure.

BEGIN
  APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => 'MY_CART',
    p_c001            => :P1_PRODUCT_NAME,
    p_n001            => :P1_PRICE);
END;

In this example, a collection named MY_CART is created (if it doesn't already exist), and the values from page items P1_PRODUCT_NAME and P1_PRICE are stored in the character and number columns of the collection.

Step 2: Check If the Collection Exists
You can check if the collection already exists before trying to use it.

IF NOT APEX_COLLECTION.COLLECTION_EXISTS('MY_CART') THEN
  APEX_COLLECTION.CREATE_COLLECTION('MY_CART');
END IF;

Step 3: Query Data from a Collection
You can query the data from a collection using SQL, usually in a report region.

SELECT seq_id,
       c001 AS product_name,
       n001 AS price
FROM apex_collections
WHERE collection_name = 'MY_CART'

Step 4: Update a Collection Member
To update a member of a collection, use APEX_COLLECTION.UPDATE_MEMBER.

BEGIN
  APEX_COLLECTION.UPDATE_MEMBER(
    p_collection_name => 'MY_CART',
    p_seq             => :P1_SEQ_ID,
    p_c001            => :P1_PRODUCT_NAME,
    p_n001            => :P1_NEW_PRICE);
END;

Step 5: Delete a Member from a Collection
Use APEX_COLLECTION.DELETE_MEMBER to remove one row by its sequence ID.

BEGIN
  APEX_COLLECTION.DELETE_MEMBER(
    p_collection_name => 'MY_CART',
    p_seq             => :P1_SEQ_ID);
END;

Step 6: Delete the Entire Collection
Use DELETE_COLLECTION to remove the entire collection from session memory.

BEGIN
  APEX_COLLECTION.DELETE_COLLECTION('MY_CART');
END;

Step 7: Use Collections Across Pages
Since collections are session-based, their data is preserved while the user navigates through pages. This is helpful in multi-step wizards or applications where you collect and hold temporary data until a final submission.

Best Practices

• Always check if the collection exists before creating or modifying it.

• Use column aliases (C001–C050 for VARCHAR2, N001–N005 for NUMBER, D001–D005 for DATE) wisely to store relevant data.

• Clear collections when they are no longer needed to save session memory.

• Keep in mind that collections are specific to the session and will disappear when the session ends.

Using PL/SQL with APEX Collections gives you full control over temporary data handling, making your application more dynamic and responsive without constantly writing to the database.

Conclusion
Using PL/SQL with APEX Collections enhances your application's ability to manage temporary and user-specific data in a secure and scalable way. Collections allow you to work with structured data just like regular tables, all within a session's scope. Mastering PL/SQL operations with collections opens up dynamic use cases and improves user experience by maintaining data context across pages without requiring permanent storage.

How Do I Use Triggering PL/SQL via APEX Dynamic Actions

 Introduction

Oracle APEX Dynamic Actions provide a flexible way to respond to user interactions without requiring full page reloads. One powerful use of Dynamic Actions is triggering PL/SQL code directly from user events like button clicks, field changes, or selections. This enables you to execute business logic, update data, or validate inputs on the server instantly, creating a smooth and interactive user experience in your application.

How Do I Use Triggering PL/SQL via APEX Dynamic Actions
Font: Arial, 14px, Plain Text

In Oracle APEX, Dynamic Actions let you create interactive behavior without writing JavaScript. One of the most powerful uses of Dynamic Actions is triggering PL/SQL code when a user interacts with the page. You can use this feature to run validations, update the database, or fetch values based on user input, all without submitting the page.

Step 1: Add a Dynamic Action
Go to Page Designer, and select the item or button you want to respond to. Under the "Dynamic Actions" section, click the Create button.

Choose:

• Event: Select the triggering event, like Click, Change, or Blur.

• Selection Type: Choose how you’re identifying the item (usually Item(s) or Button).

• Affected Elements: Choose the specific page item or button that should trigger the action.

Step 2: Add a True Action to Execute PL/SQL
Once the Dynamic Action is created, click True (which means: what happens when the condition is true), then click Add Action and choose Execute PL/SQL Code.

Step 3: Write the PL/SQL Code
Enter the PL/SQL block that should execute when the event is triggered. For example:

BEGIN
  :P1_RESULT := :P1_VALUE * 10;
END;

This example takes the value of :P1_VALUE, multiplies it by 10, and stores the result in :P1_RESULT.

Step 4: Specify Items to Submit and Return
To make PL/SQL work correctly, APEX needs to know which items to send to the server and which items will be updated.

• Page Items to Submit: Add any items whose values are used in the PL/SQL block, such as P1_VALUE.

• Items to Return: Add any items whose values are changed by the PL/SQL block, such as P1_RESULT.

Step 5: Optional – Add a Condition
You can also apply a condition to determine when the Dynamic Action should run. For example, only when the value of a field is greater than 100.

Step 6: Test Your Application
Run the page. Change the input or click the button as configured. Check if the PL/SQL block runs and updates the specified item without requiring a page submit.

Best Practices

• Always declare and use :P1_ITEM_NAME syntax for referencing APEX items.

• Make sure items used in your logic are submitted to the server.

• Use APEX_DEBUG.MESSAGE to log or troubleshoot issues in PL/SQL.

• Avoid long-running PL/SQL operations in Dynamic Actions for better performance.

Using Dynamic Actions to trigger PL/SQL in Oracle APEX allows you to create seamless, interactive experiences that combine real-time user interaction with powerful backend logic.

Conclusion

Triggering PL/SQL through APEX Dynamic Actions allows you to combine responsive front-end behavior with robust back-end processing. It helps reduce page submissions, improve performance, and deliver a more seamless experience to users. By mastering this technique, you can build applications that are dynamic, efficient, and capable of handling complex business logic with ease.

Additional Example

How Do I Use Triggering PL/SQL via APEX Dynamic Actions APEX Dynamic Actions can call PL/SQL processes for real-time interactions.

Example: Enabling a Button When a Condition is Met Create a PL/SQL Dynamic Action with the following code: DECLARE v_salary NUMBER; BEGIN SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID; IF v_salary > 50000 THEN APEX_UTIL.SET_SESSION_STATE('P1_ENABLE_BUTTON', 'Y'); ELSE APEX_UTIL.SET_SESSION_STATE('P1_ENABLE_BUTTON', 'N'); END IF; END;

Use this session state to enable/disable a button dynamically.

Using PL/SQL for APEX Validations

 Introduction

Using PL/SQL for validations in Oracle APEX allows you to apply powerful and flexible business rules to your forms and processes. PL/SQL validations let you check data inputs, enforce conditions, and display meaningful error messages before the data is processed or saved. This helps ensure that only correct and complete information is submitted, improving data accuracy and application reliability.

How Do I Use PL/SQL for APEX Validations
Font: Arial, 14px, Plain Text

In Oracle APEX, PL/SQL validations allow you to apply custom logic to check user input before it is accepted and processed. This helps ensure data accuracy, apply business rules, and improve application behavior. You can create PL/SQL-based validations on buttons, page items, or conditions that must be met before submitting data.

Step 1: Go to the Page Designer
Open your Oracle APEX application and navigate to the page where you want to apply validation. In the left pane, expand the "Validations" section under the page you’re editing.

Step 2: Create a New Validation
Click the "Create" button inside the Validations section. Choose the type of validation you want to create. Select PL/SQL Function (returning Boolean) or PL/SQL Expression depending on your use case.

Step 3: Choose Validation Level
Choose where the validation should apply:

• Item-level: Checks one specific item

• Page-level: Checks multiple items or custom logic across the page

Step 4: Write PL/SQL Code for Validation
Use a PL/SQL function body that returns TRUE or FALSE. Return TRUE if the input is valid. Return FALSE (or raise an error) if the input is invalid.

Example 1 – PL/SQL Function Body (Page-level):

BEGIN
  IF :P1_AGE < 18 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
END;

Example 2 – PL/SQL Expression (simpler syntax):

:P1_SALARY > 0

Step 5: Add an Error Message
After entering the PL/SQL logic, specify the message that should appear if the validation fails. This message is shown to the user on the page.

Step 6: Set When Validation Fires
In the settings, choose when the validation should be triggered. You can link it to a specific button or have it run every time the page is submitted. You can also set conditions if the validation should run only under certain circumstances.

Step 7: Test the Validation
Run the page, enter values that break the rule, and make sure the error message appears. Then enter valid data and confirm that the form proceeds as expected.

Best Practices

• Keep validation logic clear and short.

• Use page item references like :P1_ITEM_NAME.

• Always provide meaningful and user-friendly error messages.

• Combine PL/SQL with SQL queries for advanced checks.

• Use APEX_DEBUG.MESSAGE for troubleshooting during development.

PL/SQL validations in Oracle APEX are a powerful way to enforce complex rules that cannot be handled by simple declarative options. They give you full control over input logic and allow you to keep your application data clean and consistent.

Conclusion
PL/SQL-based validations are a critical part of building secure and user-friendly Oracle APEX applications. By leveraging custom logic, you can enforce complex rules that go beyond built-in validations. Using PL/SQL for validations not only improves data quality but also gives you full control over how and when errors are detected and handled in your application.

Additional Example

PL/SQL is commonly used in validations to enforce data integrity.

Example of a Validation Process:

DECLARE

    v_count NUMBER;

BEGIN

    SELECT COUNT(*) INTO v_count FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID;

    IF v_count = 0 THEN

        RAISE_APPLICATION_ERROR(-20001, 'Employee does not exist.');

    END IF;

END;

If the employee ID does not exist, an error message is displayed.

How Do I Use PL/SQL with Dynamic SQL in APEX

 

Font: Arial, 14px, Plain Text

In Oracle APEX, PL/SQL procedures are named blocks of code that perform specific tasks and can be called multiple times from different parts of your application. Using procedures promotes reusability by allowing you to write logic once and reuse it wherever needed. This reduces code duplication, simplifies maintenance, and improves application organization.

Step 1: Creating a Procedure
You create a procedure using the CREATE OR REPLACE PROCEDURE statement in SQL Workshop or your database environment.

CREATE OR REPLACE PROCEDURE update_salary (
  p_employee_id IN NUMBER,
  p_new_salary IN NUMBER
) IS
BEGIN
  UPDATE employees
  SET salary = p_new_salary
  WHERE employee_id = p_employee_id;
  
  COMMIT;
END;

Step 2: Calling a Procedure
You can call the procedure from PL/SQL blocks, page processes, dynamic actions, or validations within APEX.

BEGIN
  update_salary(:P1_EMPLOYEE_ID, :P1_NEW_SALARY);
END;

Step 3: Passing Parameters
Procedures can accept IN, OUT, or IN OUT parameters to receive inputs and return outputs.

• IN parameters are for input values.

• OUT parameters return values from the procedure.

• IN OUT parameters can pass values in and out.

Example with an OUT parameter:

CREATE OR REPLACE PROCEDURE get_employee_name (
  p_employee_id IN NUMBER,
  p_employee_name OUT VARCHAR2
) IS
BEGIN
  SELECT first_name || ' ' || last_name
  INTO p_employee_name
  FROM employees
  WHERE employee_id = p_employee_id;
END;

Step 4: Using Procedures in APEX

• Create procedures in your database using SQL Workshop or external tools.

• Call them inside page processes, validations, or computations by writing anonymous PL/SQL blocks.

• Pass page items as parameters using colon notation (e.g., :P1_ITEM).

• Use procedures to centralize business logic and simplify application development.

Step 5: Best Practices

• Keep procedures focused on single tasks for better modularity.

• Use meaningful names that describe what the procedure does.

• Handle exceptions inside procedures to avoid unhandled errors.

• Document your procedures with comments for easier maintenance.

• Test procedures independently before integrating them into APEX pages.

Using PL/SQL procedures in Oracle APEX improves code reusability, reduces duplication, and helps maintain a clean and manageable codebase. They are fundamental building blocks for scalable and efficient application development.

How Do I Use PL/SQL Procedures for Reusability

Introduction

PL/SQL procedures are powerful tools in Oracle APEX that allow you to encapsulate reusable blocks of code. By creating procedures, you can write complex logic once and call it from multiple places within your application, improving code maintainability and reducing duplication. Understanding how to create and use PL/SQL procedures helps you build modular, efficient, and easier-to-manage applications.

In Oracle APEX, PL/SQL procedures are named blocks of code that perform specific tasks and can be called multiple times from different parts of your application. Using procedures promotes reusability by allowing you to write logic once and reuse it wherever needed. This reduces code duplication, simplifies maintenance, and improves application organization.

Step 1: Creating a Procedure
You create a procedure using the CREATE OR REPLACE PROCEDURE statement in SQL Workshop or your database environment.

CREATE OR REPLACE PROCEDURE update_salary (
  p_employee_id IN NUMBER,
  p_new_salary IN NUMBER
) IS
BEGIN
  UPDATE employees
  SET salary = p_new_salary
  WHERE employee_id = p_employee_id;
  
  COMMIT;
END;

Step 2: Calling a Procedure
You can call the procedure from PL/SQL blocks, page processes, dynamic actions, or validations within APEX.

BEGIN
  update_salary(:P1_EMPLOYEE_ID, :P1_NEW_SALARY);
END;

Step 3: Passing Parameters
Procedures can accept IN, OUT, or IN OUT parameters to receive inputs and return outputs.

• IN parameters are for input values.

• OUT parameters return values from the procedure.

• IN OUT parameters can pass values in and out.

Example with an OUT parameter:

CREATE OR REPLACE PROCEDURE get_employee_name (
  p_employee_id IN NUMBER,
  p_employee_name OUT VARCHAR2
) IS
BEGIN
  SELECT first_name || ' ' || last_name
  INTO p_employee_name
  FROM employees
  WHERE employee_id = p_employee_id;
END;

Step 4: Using Procedures in APEX

• Create procedures in your database using SQL Workshop or external tools.

• Call them inside page processes, validations, or computations by writing anonymous PL/SQL blocks.

• Pass page items as parameters using colon notation (e.g., :P1_ITEM).

• Use procedures to centralize business logic and simplify application development.

Step 5: Best Practices

• Keep procedures focused on single tasks for better modularity.

• Use meaningful names that describe what the procedure does.

• Handle exceptions inside procedures to avoid unhandled errors.

• Document your procedures with comments for easier maintenance.

• Test procedures independently before integrating them into APEX pages.

Using PL/SQL procedures in Oracle APEX improves code reusability, reduces duplication, and helps maintain a clean and manageable codebase. They are fundamental building blocks for scalable and efficient application development.

Conclusion

Using PL/SQL procedures for reusability is essential for developing clean and scalable Oracle APEX applications. Procedures help organize your code, promote consistency, and simplify maintenance by centralizing business logic. Mastering procedures not only saves development time but also enhances application performance and reliability.

How Do I Use Exception Handling in PL/SQL

 Introduction

Exception handling in PL/SQL is a crucial technique that helps you manage errors and unexpected situations gracefully in your Oracle APEX applications. By using exception handling, you can catch runtime errors, prevent application crashes, and provide meaningful messages or corrective actions when problems occur. Learning how to implement proper exception handling improves the reliability and user experience of your applications.

In Oracle APEX, exception handling in PL/SQL lets you manage errors that occur during code execution. By capturing exceptions, you can prevent your application from crashing and provide meaningful feedback or corrective actions. PL/SQL uses the EXCEPTION block within a PL/SQL block to handle errors effectively.

Step 1: Structure of Exception Handling
A PL/SQL block with exception handling has three sections: DECLARE (optional), BEGIN, and EXCEPTION.

DECLARE
  -- variable declarations
BEGIN
  -- executable statements
EXCEPTION
  -- error handling statements
END;

Step 2: Using Built-in Exceptions
PL/SQL has many predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc. You can handle these as follows:

BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = 9999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    APEX_DEBUG.MESSAGE('No employee found with this ID.');
END;

Step 3: Handling Multiple Exceptions
You can handle multiple exceptions in one block by listing them individually.

BEGIN
  -- code that may raise exceptions
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    APEX_DEBUG.MESSAGE('No data found.');
  WHEN ZERO_DIVIDE THEN
    APEX_DEBUG.MESSAGE('Division by zero error.');
  WHEN OTHERS THEN
    APEX_DEBUG.MESSAGE('Unexpected error occurred.');
END;

Step 4: Using WHEN OTHERS Clause
WHEN OTHERS catches any exceptions not explicitly handled earlier. It’s good practice to include it to avoid unhandled errors.

Step 5: Raising Custom Exceptions
You can define and raise your own exceptions to handle specific business rules.

DECLARE
  e_invalid_value EXCEPTION;
BEGIN
  IF :P1_AGE < 0 THEN
    RAISE e_invalid_value;
  END IF;
EXCEPTION
  WHEN e_invalid_value THEN
    APEX_DEBUG.MESSAGE('Age cannot be negative.');
END;

Step 6: Best Practices

• Always handle exceptions to maintain application stability.

• Use meaningful messages to inform users or developers.

• Avoid empty exception blocks that suppress errors silently.

• Log errors for troubleshooting when necessary.

• Use APEX_DEBUG.MESSAGE during development to trace issues.

Exception handling in PL/SQL is vital for building reliable Oracle APEX applications. It helps you control error situations, maintain data integrity, and improve user experience by handling problems gracefully.

Conclusion

Mastering exception handling in PL/SQL allows you to build robust and fault-tolerant Oracle APEX applications. It ensures that errors are caught and handled appropriately, helping maintain data integrity and smooth application flow. Proper use of exception handling not only improves debugging and maintenance but also enhances the overall stability and professionalism of your software solutions.

Additional Example

Errors can occur in PL/SQL, and handling them properly prevents application crashes.

BEGIN

    UPDATE EMPLOYEES SET SALARY = SALARY + 1000 WHERE EMPLOYEE_ID = :P1_EMP_ID;

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        :P1_MESSAGE := 'No employee found with this ID.';

    WHEN OTHERS THEN

        :P1_MESSAGE := 'An unexpected error occurred.';

END;

This ensures that meaningful error messages are displayed instead of generic system errors.

How Do I Use Loops in PL/SQL for Data Processing

 Introduction

Loops in PL/SQL are essential for processing multiple rows of data or repeating a set of instructions until a condition is met. In Oracle APEX, using loops allows you to automate repetitive tasks such as updating records, performing calculations, or validating data efficiently. Understanding how to use different types of loops like FOR, WHILE, and simple LOOP structures helps you write clean, effective code for managing complex data processing scenarios.

In Oracle APEX, loops in PL/SQL help you process multiple rows of data or repeat tasks efficiently. Loops let you perform the same operation multiple times, such as updating records, validating data, or calculating values. There are three common types of loops in PL/SQL: simple LOOP, FOR LOOP, and WHILE LOOP. Each serves a different purpose depending on your processing needs.

Step 1: Using a Simple LOOP
A simple LOOP repeats indefinitely until you explicitly exit it with an EXIT statement. Use it when you don’t know the number of iterations in advance.

DECLARE
  v_counter NUMBER := 1;
BEGIN
  LOOP
    APEX_DEBUG.MESSAGE('Iteration: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;
END;

Step 2: Using a FOR LOOP
FOR LOOP is useful when you know exactly how many times you want to repeat the code. It automatically handles the loop counter.

BEGIN
  FOR i IN 1..5 LOOP
    APEX_DEBUG.MESSAGE('Iteration: ' || i);
  END LOOP;
END;

Step 3: Using a WHILE LOOP
WHILE LOOP runs as long as a specified condition remains true. It checks the condition before each iteration.

DECLARE
  v_counter NUMBER := 1;
BEGIN
  WHILE v_counter <= 5 LOOP
    APEX_DEBUG.MESSAGE('Iteration: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;

Step 4: Looping Through Data with FOR LOOP and Cursor
You can use a FOR LOOP with an implicit cursor to process rows from a query.

BEGIN
  FOR rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 10) LOOP
    APEX_DEBUG.MESSAGE('Employee ID: ' || rec.employee_id || ', Name: ' || rec.first_name);
  END LOOP;
END;

Step 5: Best Practices

• Use FOR LOOPs when iterating over a known range or query result.

• Use EXIT WHEN inside simple LOOPs to control when the loop stops.

• Avoid infinite loops by ensuring your exit conditions are reachable.

• Use loops to minimize repetitive SQL calls and improve performance.

• Use APEX_DEBUG.MESSAGE or DBMS_OUTPUT.PUT_LINE for debugging your loop logic.

Using loops in PL/SQL within Oracle APEX helps automate repetitive tasks and process data efficiently. Understanding when and how to use each type of loop will improve your application's logic and performance.

Conclusion

Mastering loops in PL/SQL empowers you to handle bulk data operations and repetitive tasks smoothly within your Oracle APEX applications. By leveraging FOR, WHILE, and LOOP constructs, you can automate workflows, improve performance, and ensure consistent data handling. Using loops effectively is key to building scalable and maintainable applications that can process large amounts of data reliably.

Additional Example

Loops iterate over sets of data, making them useful for batch processing.

DECLARE

    v_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE;

    v_salary EMPLOYEES.SALARY%TYPE;

BEGIN

    FOR rec IN (SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES) LOOP

        v_emp_id := rec.EMPLOYEE_ID;

        v_salary := rec.SALARY;

        IF v_salary < 50000 THEN

            UPDATE EMPLOYEES SET SALARY = SALARY * 1.10 WHERE EMPLOYEE_ID = v_emp_id;

        END IF;

    END LOOP;

END;

This loop increases the salary of employees earning less than 50,000 by 10%.

How Do I Use Conditional Logic in PL/SQL

 Introduction

Using conditional logic in PL/SQL allows you to make decisions and control the flow of your code based on specific conditions. This is especially useful in Oracle APEX when you want to perform different actions depending on the values entered by users or the results of queries. PL/SQL provides structures like IF-THEN, IF-THEN-ELSE, and CASE statements, which help you write flexible and dynamic logic that responds to real-time data and user input.

In Oracle APEX, using conditional logic in PL/SQL allows you to control how your code behaves based on certain values or conditions. This helps your application respond differently depending on user input, query results, or system states. You can use IF, IF-THEN-ELSE, ELSIF, and CASE statements to manage multiple outcomes in a single PL/SQL block.

Step 1: Where to Use Conditional Logic
Conditional logic in PL/SQL can be used in:

• Page processes (after submit, before header, etc.)

• Validations

• Dynamic actions (server-side)

• Computations

• PL/SQL function bodies

• Procedures and functions in SQL Workshop

Step 2: Basic IF-THEN Statement
Use IF when you want to perform a block of code only when a condition is true.

DECLARE
  v_total NUMBER := 10;
BEGIN
  IF v_total > 5 THEN
    APEX_DEBUG.MESSAGE('Total is greater than 5');
  END IF;
END;

Step 3: Using IF-THEN-ELSE
Use ELSE when you want to do something when the condition is not true.

DECLARE
  v_status VARCHAR2(10) := 'CLOSED';
BEGIN
  IF v_status = 'OPEN' THEN
    APEX_DEBUG.MESSAGE('Status is OPEN');
  ELSE
    APEX_DEBUG.MESSAGE('Status is not OPEN');
  END IF;
END;

Step 4: Using ELSIF for Multiple Conditions
Use ELSIF to check more than one condition.

DECLARE
  v_score NUMBER := 85;
BEGIN
  IF v_score >= 90 THEN
    APEX_DEBUG.MESSAGE('Grade: A');
  ELSIF v_score >= 80 THEN
    APEX_DEBUG.MESSAGE('Grade: B');
  ELSE
    APEX_DEBUG.MESSAGE('Grade: C or lower');
  END IF;
END;

Step 5: Using CASE Statements
CASE statements are useful when checking multiple fixed values.

DECLARE
  v_day VARCHAR2(10) := 'MON';
BEGIN
  CASE v_day
    WHEN 'MON' THEN APEX_DEBUG.MESSAGE('Start of the week');
    WHEN 'FRI' THEN APEX_DEBUG.MESSAGE('End of the week');
    ELSE APEX_DEBUG.MESSAGE('Midweek');
  END CASE;
END;

Step 6: Using Conditional Logic with Page Items
You can reference APEX page items using the : syntax and apply logic accordingly.

BEGIN
  IF :P1_ROLE = 'ADMIN' THEN
    :P1_ACCESS := 'FULL';
  ELSE
    :P1_ACCESS := 'LIMITED';
  END IF;
END;

Best Practices

• Keep your conditions clear and readable.

• Always include an ELSE or DEFAULT branch for complete logic.

• Use APEX_DEBUG.MESSAGE for debugging or DBMS_OUTPUT.PUT_LINE in SQL Workshop.

• Keep conditions simple and use nested logic only when necessary.

• Use page item values carefully and check for NULL where needed.

Using conditional logic in PL/SQL is essential for building responsive and flexible Oracle APEX applications. It allows you to control how data is processed, displayed, and managed based on your business rules.

Conclusion
Conditional logic in PL/SQL gives your Oracle APEX applications the ability to react intelligently to different scenarios. By using IF statements and CASE expressions, you can guide your application’s behavior, validate data, handle exceptions, and manage different outcomes with ease. Learning how to use these control structures effectively is key to building smart and reliable PL/SQL code in any Oracle APEX project.

ADDITIONAL EXAMPLE

Conditional statements control program flow based on specific conditions.

DECLARE

    v_salary NUMBER;

BEGIN

    SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID;

    IF v_salary > 50000 THEN

        :P1_STATUS := 'High Salary';

    ELSE

        :P1_STATUS := 'Standard Salary';

    END IF;

END;

This example updates a page item (P1_STATUS) based on the salary amount.

How Do I Use PL/SQL Variables in APEX

 Introduction

Using PL/SQL variables in Oracle APEX allows you to store, manipulate, and pass values within your application logic. These variables are useful in page processes, validations, computations, and dynamic actions where you need to work with data temporarily without storing it in a table. Understanding how to declare, assign, and use PL/SQL variables helps you build smarter, more responsive APEX applications with better control over business rules and data flow.

Using PL/SQL variables in Oracle APEX is an important technique for storing temporary data, performing calculations, or controlling logic flow. These variables allow you to work with values during execution without writing them to the database. You can declare PL/SQL variables in page processes, computations, validations, dynamic actions (server-side), or PL/SQL anonymous blocks.

Step 1: Where to Use PL/SQL Variables in APEX
PL/SQL variables can be used in the following places inside Oracle APEX:

• Page processes (Before Submit, After Submit)

• PL/SQL Dynamic Content regions

• Page computations

• Server-side conditions in dynamic actions

• Validations and branches

• PL/SQL function body returning value

Step 2: Declare a Variable
To use a PL/SQL variable, start by declaring it. In any PL/SQL block, use the DECLARE section:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  -- logic here
END;

If you're working inside a Page Process or Computation, APEX automatically wraps your code in a PL/SQL block, so you can start directly with the variable declaration.

Step 3: Assign a Value to the Variable
You can assign values using the := operator or with a SELECT INTO query:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
  -- Optional: output result
  APEX_DEBUG.MESSAGE('Name: ' || v_name);
END;

Step 4: Use the Variable in Logic
Once a value is stored in the variable, you can use it in IF conditions, loops, or any logic block:

DECLARE
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = :P1_EMP_ID;

  IF v_salary > 5000 THEN
    APEX_DEBUG.MESSAGE('High salary');
  ELSE
    APEX_DEBUG.MESSAGE('Normal salary');
  END IF;
END;

Step 5: Passing Data from Page Items to Variables
Use colon : notation to reference APEX page items:

DECLARE
  v_email VARCHAR2(100);
BEGIN
  v_email := :P1_EMAIL;
  -- You can now use v_email in any logic
END;

Step 6: Returning Values to Page Items
You can assign values from variables back to APEX items like this:

:P1_STATUS := 'ACTIVE';

This sets the value of the page item P1_STATUS based on logic inside your PL/SQL code.

Step 7: Example – Full Use in a Page Process

DECLARE

  v_total NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_total FROM orders WHERE customer_id = :P1_CUSTOMER_ID;

  IF v_total > 0 THEN
    :P1_ORDER_COUNT := v_total;
  ELSE
    :P1_ORDER_COUNT := 0;
  END IF;
END;

Best Practices

• Always use meaningful variable names.

• Keep variable scope limited to what is necessary.

• Use %TYPE or %ROWTYPE to match variable data types with table columns.

• Use exception handling for SELECT INTO queries to avoid runtime errors.

• Use APEX_DEBUG.MESSAGE or DBMS_OUTPUT.PUT_LINE (in SQL Workshop) to debug.

Using PL/SQL variables in APEX allows you to write dynamic logic, respond to user input, and perform custom actions efficiently without hardcoding values or creating unnecessary database columns. It makes your applications more flexible, intelligent, and easier to maintain.

Conclusion

PL/SQL variables give you the flexibility to handle dynamic logic within your APEX pages and applications. By using them effectively, you can simplify processes, reduce the need for repeated queries, and improve performance. Whether you're using variables to calculate values, validate inputs, or pass data between procedures, they are a core part of efficient PL/SQL programming in Oracle APEX.

ADDITIONAL EXAMPLE: 

PL/SQL allows declaring variables to store and manipulate data within a session.

DECLARE

    v_salary NUMBER(10,2);

BEGIN

    SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID;

    :P1_SALARY := v_salary;  -- Assigns value to APEX page item

END;

This example retrieves the employee’s salary and assigns it to an APEX page item (P1_SALARY).