Mastering Error Handling in Oracle APEX: A Comprehensive Guide

Oracle Application Express (APEX) offers a wide range of error-handling capabilities that help developers create robust and user-friendly applications. In this blog post, we will explore various methods for error handling in Oracle APEX, including PL/SQL, JavaScript, and APEX API.

PL/SQL Error Handling

PL/SQL allows developers to handle errors using the RAISE_APPLICATION_ERROR procedure or by catching exceptions in an EXCEPTION block.

Example

 

SQL

xxxxxxxxxx

16

 

1

SET SERVEROUTPUT ON;

2

​

3

DECLARE

4

   -- Example: Check if a certain condition is met

5

   v_value VARCHAR2(4000) := 'Max Musterman';

6

BEGIN

7

​

8

   -- Raise an error if the variable value is null

9

   IF v_value IS NULL THEN

10

      RAISE_APPLICATION_ERROR(-20001, 'value is null!');

11

   ELSE

12

      -- If the value is not null

13

      RAISE_APPLICATION_ERROR(-20001, 'value: ' || v_value);

14

   END IF;

15

END;

16

/

JavaScript Error Handling

In APEX, JavaScript can be used to handle client-side errors, which is useful for implementing dynamic interactions and validations.

Example

 

Javascript

xxxxxxxxxx

11

 

1

// Get the current value of the APEX item (e.g., P60_ITEM)

2

var currentValue = apex.item('P60_ITEM').getValue();

3

​

4

// Create the new value by appending ' Mustermann'

5

var newValue = currentValue + ' Mustermann';

6

​

7

// Set the new value back to the same APEX item

8

apex.item('P60_ITEM').setValue(newValue);

9

​

10

// Output the result in the console

11

console.log('New value set: P60_ITEM =', apex.item('P60_ITEM').getValue());

APEX API Error Handling

Oracle APEX provides APIs to effectively handle errors within applications.

Example

 

SQL

xxxxxxxxxx

16

 

1

DECLARE

2

    v_message        VARCHAR2(4000) := 'A critical error has occurred.';

3

    v_page_item_name VARCHAR2(100)  := 'P60_ITEM'; -- The name of the APEX item where the error should be displayed

4

BEGIN 

5

​

6

    if :P60_ITEM is null then

7

​

8

        -- Add an error message

9

        APEX_ERROR.ADD_ERROR(

10

            p_message          => v_message

11

          , p_display_location => APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF

12

          , p_page_item_name   => v_page_item_name

13

        );

14

15

    end if;

16

END;

In Oracle APEX, the APEX_ERROR.ADD_ERROR procedure offers several options for the p_display_location parameter, allowing you to control where and how an error message is displayed. Here are the available constants you can use:

1. APEX_ERROR.C_INLINE_IN_NOTIFICATION

• Description: Displays the error message in a global notification region (typically at the top of the page as a banner).
• Usage: Useful when you want the error message to be displayed as a general notification across the page.

2. APEX_ERROR.C_INLINE_WITH_FIELD

• Description: Displays the error message directly next to the affected field (item).
• Usage: Ideal for showing the error message in immediate context with a specific input field.

3. APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF

• Description: Displays the error message both next to the affected field and in the global notification region.
• Usage: Use this option to ensure the error message is prominently visible both at the top of the page and next to the specific field.

4. APEX_ERROR.C_ON_ERROR_PAGE

• Description: Instead of displaying the error message on the current page, the user is redirected to an error page where the message is shown.
• Usage: Suitable for severe errors where it's appropriate to interrupt normal page navigation and direct the user to a dedicated error page.

SQL Error Handling

SQL errors can be caught using PL/SQL blocks that execute SQL operations.

Example

 

SQL

xxxxxxxxxx

22

 

1

DECLARE

2

    v_emp_name APNO_EMPLOYEE.EMPL_NAME%TYPE;

3

BEGIN

4

    -- Attempt to retrieve the employee name with EMPNO 1234

5

    SELECT EMPL_NAME 

6

      INTO v_emp_name

7

      FROM APNO_EMPLOYEE 

8

     WHERE EMPL_ID = 155

9

    ;

10

​

11

    -- Output the retrieved name

12

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);

13

​

14

EXCEPTION

15

    -- When no record is found

16

    WHEN NO_DATA_FOUND THEN

17

        DBMS_OUTPUT.PUT_LINE('No record found.');

18

19

    -- When another error occurs

20

    WHEN OTHERS THEN

21

        DBMS_OUTPUT.PUT_LINE('SQL Error: ' || SQLERRM);

22

END;

Conclusion

Effectively utilizing error handling and debugging in Oracle APEX enhances the stability and user-friendliness of your applications. By combining PL/SQL, JavaScript, and APEX-specific error handling features, developers can create comprehensive and robust application solutions.

Key Takeaways for SEO:

• Oracle APEX error handling: Emphasize throughout the article to target relevant search queries.
• PL/SQL, JavaScript, APEX API: Highlight these technologies to attract developers interested in these specific areas.
• Practical examples: Include code snippets to improve engagement and provide real-world application.

Action Items:

• Share this guide: Post on social media and developer forums to reach a wider audience.
• Implement examples: Encourage readers to try out the examples in their own APEX environments.
• Feedback: Invite comments and questions to foster community interaction.

 {fullWidth}