Feed aggregator

When developping Oracle APEX apps I like to instrument my code with APEX_DEBUG and/or Logger.
become
had With our APEX Office Print (AOP) PL/SQL API and APEX Plug-in we did the same, we use APEX_DEBUG and Logger behind the scenes to allow you to see what it going on. But when I tried to view the APEX debug messages in SQL Developer, I didn't see any. Christian Neumueller of the APEX Dev team, gave me the answer: APEX debug is buffering it's output, to reduce the I/O overhead. Buffering is disabled for LEVEL9, but the other levels only write:
eva  - after 1000 records
 - at the end of request processing
 - when you detach the session

This explained perfectly what was happening for me.

Here's a quick example when you want to debug the AOP PL/SQL API from PL/SQL and SQL Developer. The code downloads an Interactive Report of page 200 to Excel and stores it in a table.

declare
l_return blob;
l_output_filename varchar2(100) := 'output';
begin
apex_session.create_session(p_app_id=>498,p_page_id=>200,p_username=>'DIMI');

apex_debug.enable(p_level => apex_debug.c_log_level_info); 
-- for more details, use: c_log_level_app_trace
apex_debug.message(p_message => 'Debug enabled.');

l_return := aop_api_pkg.plsql_call_to_aop (
p_data_type => aop_api_pkg.c_source_type_rpt,
p_data_source => 'report1',
p_template_type => aop_api_pkg.c_source_type_apex,
p_template_source => 'aop_template_ir_customers.xlsx',
p_output_type => aop_api_pkg.c_excel_xlsx,
p_output_filename => l_output_filename,
p_aop_url => apex_app_setting.get_value('AOP_URL'),
p_api_key => apex_app_setting.get_value('AOP_API_KEY'),
p_app_id => 498,
p_page_id => 200);

insert into aop_output (output_blob,filename) 
values (l_return, l_output_filename);
commit;

dbms_output.put_line('To view debug messages:');
dbms_output.put_line('select * from apex_debug_messages where session_id = '
||apex_util.get_session_state('APP_SESSION') ||' order by message_timestamp');

apex_session.detach;
end;

Running the SQL statement to view the debug messages:
select * from apex_debug_messages where session_id = 16458652970080 order by message_timestamp

Et voila... the APEX debug info is available straight away :)

In my previous post on Indexing The Autonomous Warehouse, I highlighted how it might be necessary to create indexes to improve the performance and scalability of highly selective queries, as it might on any Data Warehouse running on an Exadata platform. In the post, I created a Bitmap Index and showed how improve SQL performance […]

When the Autonomous Data Warehouse was initially released in March 2018, one of the notable restrictions was that it did not support typical Data Warehouse performance related structures such as Indexes, Partitioning or Materialized Views. The platform was deemed efficient enough to not require these features, which would unnecessarily complicate the autonomous environment. In September […]

Yesterday Oracle released Oracle APEX 19.1. We typically upgrade our environment within a week.

The question is always, when is a good time to upgrade?

As we are hosting multiple applications and sites in our environment, we want to find a spot with minimal impact to our customers. For some applications which are only accessed by some people in Belgium, it's very easy to schedule an upgrade at night as the chances are not high people accessing the app. But in the case of APEX Office Print, we have customers all over the world in different timezones. So they might want to look at their dashboard or download the latest version at any moment in time.

For our AOP service itself, we have an internal policy we don't allow downtime. We load balance, so even when performing upgrades of AOP, there will always be a server available to handle the requests. For our Oracle database and APEX environment, we are fine with a few minutes downtime while upgrading the server.

In Oracle APEX itself, when you go to Monitor Activity - Page Views by User by Hour you get a graphical overview when your applications are being used:

This screen works well for a given workspace. If you want to look cross workspaces, for your entire APEX instance, you could build something yourself similar to the above. An overview like that would give you an idea based on historical usage which hours have the least amount of impact.

Now that you found the window, you still want to check if somebody is using our APEX apps.

So before I start an upgrade, I always check the last activity in our APEX apps.
The script I use to monitor the activity in our entire APEX environment - as SYS user run in SQL>

SELECT
workspace_name,
apex_session_id,
user_name,
remote_addr,
TO_CHAR(session_created, 'DD-MON-YYYY HH24:MI') AS session_created,
TO_CHAR(session_idle_timeout_on, 'DD-MON-YYYY HH24:MI') AS session_idle_timeout_on,
TO_CHAR(session_idle_timeout_on-(session_max_idle_sec/24/60/60), 'DD-MON-YYYY HH24:MI') AS last_activity,
TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI') AS time_now,
round((sysdate-(session_idle_timeout_on-(session_max_idle_sec/24/60/60)))*24*60) as minutes_ago
FROM apex_workspace_sessions
WHERE user_name NOT IN ('APEX_PUBLIC_USER','nobody')
ORDER BY minutes_ago, workspace_name, session_idle_timeout_on DESC;

This gives me the following overview:


So I see how many minutes ago (last column) which APEX workspace was used by which user. I could even follow the APEX session to find out more about the APP etc. but that is not really relevant for me. In the script, I only check for logged in users.

In case I also want to see for direct connections to the database, or I want more real-time info from the APEX session, I run the following script to check the session in the Oracle Database:

SELECT
sid,
serial#,
username,
osuser,
machine,
program,
--sql_id, sql_exec_start, event,
logon_time,
client_info AS workspace_id_auth_user,
module AS db_schema_apex_app_id_page_id,
action AS action,
client_identifier AS auth_user_apex_session
FROM gv$session
WHERE type = 'USER'
AND service_name = 'apex_pdb';

This gives me the following overview:


In the above screen you see I'm connected to the database with SQL Developer. The other sessions are coming from our connection pool. What is interesting is that APEX is instrumented to populate session info in the database like module, client_info, and client_identifier. This is important as APEX/ORDS is working with a connection pool so it would be hard to see what APEX session corresponded to which database session in case they didn't.

The above helps me to find a good time to perform upgrades, without impacting too many people.
So I thought to share my scripts as it might help you too.

Ever got the error that there's an issue with your environment because the Application Express files have not been loaded when you try to open Oracle APEX in your environment?

Since the Announcement of the availability of Oracle APEX Static Resources on a Content Delivery Network I typically configure APEX to use the CDN.

Steps to do so for the entire environment:

• Navigate to the apex/utilities subdirectory from the directory you unzipped APEX to
• Connect to your database as the SYS user
• Run:  @reset_image_prefix.sql
• when prompted for the image prefix, enter the correct path, e.g. https://static.oracle.com/cdn/apex/18.2.0.00.12/

Now, when I got the above error, I forgot which images folder APEX was using. Was there an issue with the connection to the CDN, was it a local issue?

Below is how I found out how the APEX images directory was configured and if it was using the CDN or local files.
Connect as SYS in a SQL window (SQLcl, SQL Developer, SQL Plus, ...):

SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(apex_180100.wwv_flow_image_prefix.g_image_prefix);
3 end;
4 /
/i/

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_output.put_line(apex_180200.wwv_flow_image_prefix.g_image_prefix);
3 end;
4 /
https://static.oracle.com/cdn/apex/18.2.0.00.12/

PL/SQL procedure successfully completed.

Above you see that in APEX 18.1 the /i/ directory is used on the local web server.
On APEX 18.2 the CDN is being used.

Note: whenever you run the above command change to the correct APEX user (version), so for APEX 19.1 you use apex_190100.

I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this. Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous […]

I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Webinar will run between 9-12 July 2019 (6pm-10pm AEST): Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST):  This is a must attend seminar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  […]

I’ve recently received a number of correspondences regarding one of my most popular blog posts, dating back to February 2008: Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones). In the comments section, there’s an interesting discussion where I mention: “If Oracle19 does everything for you and all the various indexes structures get […]

I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Seminar throughout Australia and New Zealand this coming winter 2019. (See my Oracle Performance Diagnostics and Tuning Seminar page for all the seminar content and details). This is a must attend seminar aimed at Oracle professionals (both DBAs […]

Some people use APEX Office Print (AOP) to send out HTML emails based on a custom HTML template they created. Today we got a question in our AOP support inbox how to include images in those emails.

In an AOP Template, you can use a tag {%image} and AOP will replace that tag with a base64 encoded image. This works great when you open the resulting HTML file in a browser, but when using emails it's not so great as some email clients don't support base64 encoded images e.g. Outlook.

That the images don't always work is not really an AOP issue, it's more an issue with HTML emails.
Here's a good read on supporting images in HTML emails.

In short, it's better to include an URL to the image as that is supported by most, if not all, email clients. Typically the images are stored in a BLOB column in a table or people want to reference an image that they uploaded in APEX > Shared Components > Static Application Files.

This blog post shows a technique to reference an image stored in the database through a URL.

1. Create a new Public Page in an Oracle APEX Application e.g. Page 22

2. Create a region on the page

3. Create a hidden item on the page e.g. P22_FILE_NAME

4. Create a Before header Process "Retrieve image from DB" with following PL/SQL Code:

declare
l_file_name apex_application_static_files.file_name%type;
l_mime_type apex_application_static_files.mime_type%type;
l_file_charset apex_application_static_files.file_charset%type;
l_file_content apex_application_static_files.file_content%type;
begin
select nvl(file_name, 'output') as file_name,
nvl(mime_type, 'application/octet-stream') as mime_type,
nvl(file_charset, 'utf-8') as file_charset,
file_content
into l_file_name,
l_mime_type,
l_file_charset,
l_file_content
from apex_application_static_files
where application_id = :APP_ID
and file_name = :P22_FILE_NAME;

sys.htp.flush;
sys.htp.init;
owa_util.mime_header(l_mime_type, false);
sys.htp.p('Content-length:'||dbms_lob.getlength(l_file_content));
sys.htp.p('Content-Disposition:attachment; filename="'||l_file_name||'"; '||'filename*='||l_file_charset||''''''||l_file_name);
owa_util.http_header_close;
wpg_docload.download_file(l_file_content);
apex_application.stop_apex_engine;

exception
when no_data_found
then
sys.htp.p('');
apex_application.stop_apex_engine;
end;

5. That's it.

Now you can reference the image with the following URL: https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png

If you want to test the functionality in an email, go to SQL Workshop >SQL Commands and use the following code:

begin
apex_mail.send(
p_to => 'my@email.com',
p_from => 'my@email.com',
p_subj => 'Mail from APEX with inline image',
p_body => 'Please review the email in html.',
p_body_html => 'Here the inline image: <br/> <img alt="Image" height="150" width="150" src="https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png">');

apex_mail.push_queue;
end;

Now you can send HTML emails in APEX with references to images that are stored in the database.