See APEX Debug info in PL/SQL and SQL Developer

When developping Oracle APEX apps I like to instrument my code with APEX_DEBUG and/or Logger.

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