collect data via API from the table "apex_activity_log" in APEX ORACLE

January 31, 2022

I have one question, I want to have one page view statistics and send them through REST.
here is my request:

         select count (*)
           from apex_activity_log l
             where flow_id = 100
                 and time_stamp> = sysdate - (1/24/60/60 * 2419200)
                 and userid is not null
                 and step_id = 172
            ;

    answer : 867

This query works great in "SQL Commands". But when I use this query in Packages I get 0. Although the answer should be completely different. How do I give Packages access to apex_activity_log. That the correct answer came back to me. Thanks)

My api in Packages :

PROCEDURE post_connect_service (   
        p_status   OUT NUMBER,   
        p_blob     IN BLOB   
    ) IS   
   
        v_blob blob :=    p_blob;   
        v_clob            CLOB;   
        tv                apex_json.t_values;   
        v_id    varchar2(1000);   
        v_number    varchar2(1000);      
        v_date_last date;  
        v_count_v_pl     int;
        v_count_sum_v     int;
    BEGIN   
        v_clob := iot_general.blob_to_clob(v_blob);   
        apex_json.parse(tv,v_clob);    
        v_id  := apex_json.get_varchar2(p_path => 'id', p_values => tv);   
     
         select  count(*) into v_count_sum_v
            from apex_activity_log;

        p_status := 200;   
        apex_json.open_object;   
        apex_json.write('success', true);          
        apex_json.write('count_views', v_count_v_pl);  
        apex_json.write('count_sum_views', v_count_sum_v);
        apex_json.close_object;   
    EXCEPTION   
        WHEN OTHERS THEN   
            p_status := 500;   
            apex_json.open_object;   
            apex_json.write('success', false);   
            apex_json.write('message', substr(sqlerrm,1,4000));   
            apex_json.close_object;   
END post_connect_service;

>Solution :

The view apex_activity_log has data for the current apex context. If it is queried outside of an apex context, it will return no rows. Easiest way is to create an apex session before querying it.

koen>SELECT COUNT(*) FROM apex_activity_log;

   COUNT(*) 
___________ 
          0 

koen>DECLARE
  2  BEGIN
  3    apex_session.create_session (
  4       p_app_id => 286,
  5       p_page_id => 1,
  6       p_username => 'KOEN.LOSTRIE@ORACLE.COM');
  7  END;
  8* /

PL/SQL procedure successfully completed.

stapp_dev--STTAXONOMY>SELECT COUNT(*) FROM apex_activity_log;

   COUNT(*) 
___________ 
       9327 

stapp_dev--STTAXONOMY>