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).

Understanding PL/SQL in Oracle APEX

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension of SQL, and it plays a critical role in Oracle APEX development. In Oracle APEX, PL/SQL is used to define business logic, control data processing, interact with the database, and automate workflows within applications. Understanding PL/SQL is essential for building powerful, data-driven applications that go beyond basic form and report functionality.

PL/SQL is essential in APEX because it allows you to:

• Implement business logic within an application

• Create dynamic actions and processes

• Perform validations and error handling

• Write stored procedures and functions

• Manage triggers and packages for better modularity

Where PL/SQL is Used in Oracle APEX

• Page Processes: PL/SQL is commonly used in page processes to perform actions such as inserting, updating, or deleting data after a form is submitted.

• Validations: You can write PL/SQL logic to validate user input before saving it to the database.

• Dynamic Actions: When a page element triggers a dynamic action, PL/SQL can be used on the server side to execute logic and return results to the client.

• Application Processes: For actions that apply across multiple pages or during session initialization, PL/SQL is written in application-level processes.

• PL/SQL Regions: These allow developers to embed PL/SQL directly in a page to generate custom HTML output or perform logic-driven display.

PL/SQL Code Structure
PL/SQL code is typically written in the form of anonymous blocks or stored procedures and follows this structure:

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

What Are the Differences Between SQL and PL/SQL Code Structure

SQL and PL/SQL are both used in Oracle APEX and Oracle Database, but they have different code structures and purposes. Understanding the difference helps you use each correctly in your applications.
1. SQL Code Structure
SQL is a declarative language.
It works with one statement at a time.
Each SQL command performs a specific task like retrieving or modifying data.
SQL does not support control structures like IF or LOOP.
Example of SQL code structure:
SELECT first_name, last_name FROM employees WHERE department_id = 10;

Other SQL examples:
INSERT INTO departments (department_name) VALUES ('Marketing');
UPDATE employees SET salary = 5000 WHERE employee_id = 101;
DELETE FROM employees WHERE employee_id = 102;

2. PL/SQL Code Structure
PL/SQL is a procedural language that includes SQL and additional programming features.
It allows multiple statements in a block.
It supports variables, loops, conditions, and error handling.
PL/SQL is used to build stored procedures, functions, and anonymous blocks.

Basic PL/SQL block structure:
DECLARE
  v_name VARCHAR2(50);
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found.');
END;

Key differences in structure:
SQL has no DECLARE, BEGIN, or EXCEPTION sections.
PL/SQL uses a block structure: DECLARE (optional), BEGIN, EXCEPTION (optional), and END.
SQL is for querying or updating data. PL/SQL is for building logic and control.
Summary
Use SQL when you need a single query or data manipulation command.
Use PL/SQL when you need to write logic that includes multiple steps, variables, or error handling.

Both are essential in Oracle APEX development and often work together to build dynamic, data-driven applications.

Common Use Cases in APEX

• Automatically updating audit fields (created date, modified by)

• Performing complex validations based on multiple inputs

• Sending emails or notifications using APEX_MAIL

• Inserting or updating multiple related tables in one process

• Calling stored procedures and packages for backend operations

Benefits of Using PL/SQL in APEX

• Performance: Code runs inside the database, reducing network overhead.

• Reusability: Logic written once in procedures or functions can be reused across different pages or applications.

• Security: Business rules are enforced at the database level, reducing reliance on UI-based validation.

• Maintainability: Keeps complex logic out of the page and centralized in packages or procedures.

SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL) are both used in Oracle databases, but they serve different purposes and have different capabilities. Here's a clear breakdown:

1. Purpose

• SQL is a declarative language used to interact with the database — for querying, inserting, updating, and deleting data.

• PL/SQL is a procedural language that extends SQL with programming features like loops, conditions, variables, and error handling.

2. Execution

• SQL executes one statement at a time (single-line operations).

• PL/SQL can execute multiple statements as a block, including logic and control flow.

3. Language Type

• SQL is a data manipulation language (DML).

• PL/SQL is a procedural language that embeds SQL statements inside structured code.

4. Capabilities

• SQL can:

  • Select data (SELECT)

  • Insert, update, delete records

  • Create and modify database objects

• PL/SQL can:

  • Declare variables and constants

  • Use loops (FOR, WHILE) and conditions (IF, CASE)

  • Handle exceptions (EXCEPTION block)

  • Define procedures, functions, packages, and triggers

5. Reusability

• SQL is typically written and run per use case.

• PL/SQL allows you to create reusable blocks like stored procedures and functions.

6. Performance

• PL/SQL improves performance by reducing round-trips between application and database, especially in complex logic.

• SQL may require multiple calls to achieve the same, especially in procedural logic.

7. Example

SQL Example:

SELECT first_name FROM employees WHERE department_id = 10;

PL/SQL Example:

DECLARE
  v_name employees.first_name%TYPE;
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;

Summary

• Use SQL when you need to interact with data directly (query or modify).

• Use PL/SQL when you need to perform logic, automation, or control flow inside the database.
  Both are often used together in Oracle APEX and database development to build powerful, efficient applications

When to Use PL/SQL vs. SQL

SQL is used when you need to perform simple operations directly on the data, such as selecting, inserting, updating, or deleting rows in a table. It is best for straightforward queries and data manipulation tasks. Use SQL when you want to retrieve data from a table, filter it with conditions, or make basic changes to one or more records.

PL/SQL should be used when you need to include logic, decision-making, or processing that involves more than one SQL statement. It is ideal when your task requires conditions, loops, error handling, or variables. PL/SQL is useful for writing stored procedures, functions, packages, and triggers where the operations are more complex or need to be reused across different parts of the application.

Use SQL for single operations on data. Use PL/SQL when you need to wrap SQL in a logical structure that includes control flow, calculations, or exception handling. In Oracle APEX, PL/SQL is often used in page processes, validations, and backend logic, while SQL is used for queries, report sources, and data display.

Where Do We Use PL/SQL and SQL in Oracle APEX

In Oracle APEX, both PL/SQL and SQL are used throughout the application to manage data and implement business logic. SQL is used for querying and manipulating data, while PL/SQL is used when procedural logic, conditions, or loops are needed. They often work together to power the dynamic behavior of APEX applications.

Where SQL is Used

• In report regions to fetch data using SELECT statements

• In form regions to read and write table data

• In LOVs (List of Values) to populate dropdowns with query results

• In validations and computations to retrieve or calculate values

• In dynamic actions that execute SQL statements to get results quickly

Where PL/SQL is Used

• In page processes to insert, update, delete, or validate data with logic

• In page-level or application-level validations using conditional rules

• In page-level or region-level dynamic PL/SQL code blocks

• In PL/SQL anonymous blocks for executing stored procedures or functions

• In before and after submit processes to control page flow or behavior

• In custom authentication, authorization, and session management logic

SQL is best used when you are interacting with the data directly. PL/SQL is used when you need to wrap those interactions with logic, conditions, or exception handling. Oracle APEX makes it easy to combine both, so you can create responsive, intelligent, and secure applications.

Where Inside Oracle APEX Do We Add PL/SQL and SQL Code

Oracle APEX provides several areas where you can directly add PL/SQL and SQL code to control how your application behaves and interacts with the database. These areas are built into the APEX Page Designer and SQL Workshop, making it easy to use SQL for data access and PL/SQL for procedural logic.

1. SQL Workshop

• Use SQL Commands to write and run ad hoc SQL and PL/SQL.

• Use SQL Scripts to save and execute longer SQL or PL/SQL blocks.

• Use Object Browser to create tables, views, procedures, and functions using SQL and PL/SQL.

2. Report and Form Regions

• In Classic or Interactive Reports, you use SQL queries to define the data source.

• In Forms, SQL is used to fetch and save data directly from tables or views.

• You can also use PL/SQL for logic-based column values using expressions or functions.

3. Page Processes

• PL/SQL is added here to run after page submission or on page load.

• Used for inserting, updating, or deleting records with custom logic.

• Useful for calling stored procedures or performing validations.

4. Dynamic Actions

• PL/SQL can be added as server-side code when a dynamic action is triggered.

• SQL can be used as part of true/false condition checks or value assignments.

5. Computations

• Add SQL or PL/SQL code to calculate or set page item values before or after page rendering.

6. Validations

• PL/SQL expressions or functions are used to create custom validation rules for form items.

• SQL queries can be used to check if a value exists or meets specific conditions.

7. LOVs (List of Values)

• SQL queries are used to populate dropdowns, radio groups, and checkbox groups with dynamic values from the database.

8. Page Items and Regions

• You can set default values using SQL queries.

• PL/SQL expressions can be used in conditions to show or hide items or regions.

9. Application Processes and Shared Components

• PL/SQL can be defined as a global process or function to be reused across pages.

• Useful for authentication, authorization, and session-level operations.

10. PL/SQL Dynamic Content Regions

• Allows you to write full PL/SQL blocks that generate HTML output directly within a region.

Oracle APEX is tightly integrated with SQL and PL/SQL, allowing you to apply database logic exactly where it’s needed — whether it’s displaying data, validating inputs, or controlling behavior at the page or application level.

Conclusion
PL/SQL is the backbone of dynamic and powerful Oracle APEX applications. It allows developers to implement complex logic, enforce business rules, and interact efficiently with the database. By mastering PL/SQL, you gain full control over your application's behavior, making it more robust, secure, and scalable. Whether you're processing data, validating input, or building custom logic, PL/SQL is an essential tool in your APEX toolkit.