Oracle 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 you 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 you 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 needs 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.
What do you mean by “the next 9 available parameters”?