Feed aggregator

  AUSOUG will again be running their excellent CONNECT 2019 conference series this year at the following great venues: Monday 14th October – Rendezvous Hotel In Melbourne Wednesday 16th October –  Mercure Hotel in Perth As usual, there’s a wonderful lineup of speakers from both Australia and overseas including: Connor McDonald Scott Wesley Guy Harrison […]

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below: I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index. To start, I create a […]

I’ll be presenting at a number of Oracle events over the remainder of the year. Details as follows: Oracle Open World – San Francisco (16-19 September 2019)

I have just scheduled a new “Oracle Performance Diagnostics and Tuning” webinar to run between 20-23 August 2019, specifically for my friends in New Zealand, but is open to anyone. It will run between 7am-11am AEST each day, which is perfect for those in NZ, but also other parts of the world such as Western […]

In this post, I’ll discuss how to configure the new Automatic Indexing capability introduced in Oracle Database 19c. The intent of Oracle here is to make the configuration of Automatic Indexing as simplistic as possible, with as few levers for DBAs to potentially stuff up as possible. The ultimate goal would be to have a […]

For the past month or so I’ve been playing around extensively with the new Oracle 19c “Automatic Indexing” feature, so I thought it was time to start blogging about it. Considering it’s only in “Version 1” status, my initial impression is very positive in that it works extremely well doing at what it’s initially designed […]

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