Putting it all in one place: Using APEX_DEBUG outside of your application

Oracle Application Express (APEX) is a low-code, zero latency, feature-rich web application development framework that runs directly out of the Oracle Database. A web browser is all you need to develop robust, secure, and scalable database-driven applications.

With only a limited knowledge of SQL and PL/SQL, you can use APEX to get an application up and running in no time. But, when business requirements become more complex, so does application development. It’s time to slow down and think about best practices and how to get the job done in the right way.

APEX is a highly flexible framework, but perhaps that is its greatest downfall. When an application page is submitted, you can run custom code to do pretty much anything. A page process can enforce business rules, manipulate and augment data, perform DML against database tables, interact with external systems and everything else needed to accomplish a task. You can make an application page as smart as you want, but you’re creating a monster

What if the business rules change? Now, you have to find logic buried away in page processes. It’s a nightmare to maintain and support. Ideally, all of the business logic ought to reside in the database where it belongs. It’s easier to make changes to procedures and functions than endlessly modify an application.

Okay, so now we’ve written PL/SQL stored procedures and functions to encapsulate the business logic. Happy days! Except, now your application has lost sight of what is really going on. When something goes wrong, you’ll need to review the APEX application debug messages. If the custom database code is the culprit, you’ve got to trundle over to another tool to view errors logged by some other utility.  What if we could bring all of the information together in one place?

The APEX_DEBUG API can be used to store logging and debug messages in a single repository. By using the API in database stored procedures, all log messages will be visible in the APEX_DEBUG_MESSAGES view. This means when an application page is submitted, you have access to both the standard application messages as well as the debug messages logged through your database code.

Implementing APEX_DEBUG in external database code only requires a few steps. Let’s get started.

Preparing your application

 

When you run a page in your application, debug messages are automatically associated with the page

Every page in your application should make use of an authorization scheme, and the error page is no exception. In fact, securing this page is critical for mitigating malicious activity. Users must not be able to navigate to this page.

Later, as an option, you can add an interactive report to the page to help developers find specific messages generated by custom code. But you can leave the page empty for now.

Another option is to create an entirely new application containing the error page. This way, multiple applications can make use of generic calls to APEX_DEBUG.

APEX_DEBUG debug levels

 

Just like any other logging and debugging utility, APEX_DEBUG has a range of debug levels. These

levels are defined as constants in the APEX_DEBUG API.

 

c_log_level_error — critical error

c_log_level_warn — less critical error

c_log_level_info — default level

c_log_level_app_enter — logs start of procedure / function

c_log_level_app_trace — other messages within procedures/functions

c_log_level_engine_enter — Application Express engine: messages

 

You should define the debug level in the declaration section of your package.

 

CREATE OR REPLACE PACKAGE BODY MY_DEBUG_SAMPLE

AS

 

— constants used for debug

gc_debug_level NUMBER := APEX_DEBUC.c_log_level_warn;

—  gc_debug_level NUMBER := apex_debug.c_log_level_app_enter ;

Creating an APEX session

 

For some of the advanced features, APEX_DEBUG requires an active APEX session. Remember, your database code is running outside of your application, so it doesn’t have access to the current session. No worries. Let’s create one.

We need to declare a few variables to hold the information needed to create a session:

g_app_id number := 101;

g_page_id number := 500;

g_session_user varchar2(30) := ‘my_degub_sample’;

g_workspace_name varchar2(10) := ‘TEST’;

 

It’s a good practice to create a procedure for creating the session. This way, you only have to write the code once.

 

procedure set_debug_session

is

l_workspace_id number;

begin

 

l_workspace_id := apex_util.find_security_group_id (p_workspace => gc_workspace_name);

apex_util.set_security_group_id (p_security_group_id => l_workspace_id);

 

if v(‘APP_ID’) is null then

apex_session.create_session(

p_app_id => g_app_id,

p_page_id => g_page_id, p_username=>g_session_user

);

end if;

 

end set_debug_session;

 

Using APEX_DEBUG in a procedure or function

 

At the start of every procedure or function, declare a local variable to store the name of the routine being called.

 

procedure my_sample_proc (

p_customer_id in number,

p_transaction_id in number

)

is

l_routine_name varchar2(30) := ‘my_sample_proc’;

— then all my other declarations go here

L_account_id number;

l_customer_id number := p_customer_id;

begin

 

Next, we need to call the procedure we created earlier to create the APEX session. Then we can enable debugging.

 

set_debug;

apex_debug.enable(gc_debug_level);

 

Notice how debugging is enabled by passing in the debug level we set in the declaration section of the package.

As your custom code runs, it may pass control to other stored procedures or functions. Obviously, it is important to know where we are in the processing. This is accomplished by logging a message  at the beginning of every routine. We can also log the values passed into the procedure or function.

 

apex_debug.enter (

l_routine_name,  — we declare this earlier

‘p_customer_id’, — name of the parameter

p_customer_id, — value of the parameter

‘p_transaction_id’,

p_transaction_id

) ;

 

The call above is logging just two parameters, but you can continue passing parameter names and their values as needed.

You can continue to instrument your code by logging APEX_DEBUG messages.

 

apex_debug.info(‘fetching the customer account’);

select account_id

into l_account_id

from my_customers

where customer_id = l_customer_id;

 

apex_debug.info(‘customer account %s found’,l_account_id);

 

The first parameter passed to the apex_debug.info procedure is the message to be logged. All instances of ‘%s’ will be replaced by the value assigned to the next 9 available parameters. The message logged above might read something like ‘customer account 71242 found’;

 

In the exception section of your code, an error can be logged by calling apex_debug.error.  This procedure logs messages at the c_log_level_error level.

 

exception

when others then

apex_debug.error(‘Critical error %s’, sqlerrm);

/* then handle the error */

 

end;

 

After the stored procedures and functions are executed, the logged messages can be found By viewing debug messages in your application, or by querying the APEX_DEBUG_MESSAGES. If your writing and testing your code in SQL Developer, you can run the SQL below to view the messages.

select
message_level,
application_id,
page_id,
message_timestamp,
message,
apex_user,
call_stack
from APEX_DEBUG_MESSAGES
where application_id = ‘101’  — the application ID your declared in your code
and message_level < 5
order by message_timestamp desc

;

 

Advantages of using APEX_DEBUG

 

Unlike some other logging utilities, APEX_DEBUG is designed to help you develop and support APEX applications. It can be used within any APEX environment. You don’t need to corner your DBA to grant permissions or run an install script. If you’ve got APEX, you’ve got APEX_DEBUGG.

When an application is deployed to another environment such as QA or production, there is no need to modify code or install a logging utility. Everything simply works as it should.

Having all log and debug messages in the same repository as your application improves development. An APEX developer need only query the APEX_DEBUG_MESSAGES view to troubleshoot an issue. Log and error messages are reported within the context of an application page, making it easy to get a complete picture of what is going on.

By including calls to APEX_DEBUG in stored procedures and functions, the code is automatically instrumented in a way that makes the code much easier to read and understand. No longer do you have to read through lines and lines of code to understand what’s happening, and more importantly, what went wrong.

We’ve got you covered

 

Established in 2008, MaxAPEX is an Oracle APEX hosting company providing diverse and customized hosting solutions for its clients. MaxAPEX’s expertise, commitment and subsequent benefits for its clients enabled the hosting company to quickly expand its operations. Today, MaxAPEX has an incredible clientele of hundreds of highly satisfied and growing businesses across 60 countries.

 

MaxAPEX employs and maintains qualified, experienced and committed application hosting professionals in the industry. Seasoned team leaders and project managers lead the enterprising experts at MaxAPEX, getting the best work out of our application hosting every day.

 

visit us at www.maxpex.com .

 

 

 

 

Inventory Application Development in Oracle APEX

Have you ever wondered how e-commerce companies build their ¬¬applications yet keep track of their inventory? Sometimes keeping track of …

Advanced JavaScript in Oracle Application (APEX)

With APEX 20.2 or later, there are quite a few updates that have taken place. That however should not stop …

What’s new in Oracle APEX 21.2 – MaxAPEX Cloud

Oracle APEX 21.2 has recently been released. In this article, we share our top 5 features in Oracle APEX 21.2. …

Create your first Oracle APEX mobile Apps

Environnement: Oracle Apex 18+ version Oracle Database 9+   Mobile Application Design   Oracle Application is a framework integrated into …

Great new Features in Oracle APEX 21.1

APEX is a low-code, zero latency, feature-rich web application development framework that runs directly out of the Oracle Database. A …

Share This
0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments