7 Replies Latest reply: Jan 18, 2013 5:26 AM by Marko Goricki RSS

    accessing data from apex views using pl/sql job

    skapex
      Hi

      APEX 4.1
      Oracle 11g

      I have created a procedure which inserts data from apex_activity_log into a table user_activity_log.

      Procedure-
      CREATE OR REPLACE PROCEDURE user_activity_log (p_app_id NUMBER)
      IS
      cnt NUMBER;
      v_sqlcode VARCHAR2(200);
      v_sqlerrm VARCHAR2(200);
      BEGIN

      SELECT count(username) INTO cnt FROM chv_user_activity;


      IF cnt = 0 THEN
      insert into tt values('first insert',p_app_id);

      FOR i IN (SELECT l.userid,
      a.application_name,
      l.step_id page_no,
      l.TIME_STAMP,
      l.ir_report_id,
      l.elap,
      l.session_id,
      l.ip_address
      FROM apex_activity_log l, apex_applications a
      WHERE l.flow_id = a.application_id
      AND l.flow_id = p_app_id
      )
      LOOP

      INSERT INTO user_activity (USERNAME,APPLICATION_NAME,PAGE_NO,DATE_ACCESSED,IR_REPORT_ID,ELAP,SESSION_ID,IP_ADDRESS)
      VALUES (i.userid,
      i.application_name,
      i.page_no,
      i.TIME_STAMP,
      i.ir_report_id,
      i.elap,i.session_id,
      i.ip_address);
      END LOOP;

      ELSE

      DELETE FROM user_activity WHERE TRUNC(DATE_ACCESSED) > TRUNC(SYSDATE) - 7;

      insert into tt values('delete',p_app_id);


      INSERT INTO user_activity (USERNAME,APPLICATION_NAME,PAGE_NO,DATE_ACCESSED,IR_REPORT_ID,ELAP,SESSION_ID,IP_ADDRESS)
      ( SELECT l.userid,
      a.application_name,
      l.step_id page_no,
      l.TIME_STAMP,
      l.ir_report_id,
      l.elap,
      l.session_id,
      l.ip_address
      FROM apex_activity_log l, apex_applications a
      WHERE l.flow_id = a.application_id
      AND l.flow_id = p_app_id
      AND TRUNC(l.time_stamp) > TRUNC(SYSDATE) - 7
      );

      insert into tt values('second insert',p_app_id);

      END IF;
      COMMIT;
      EXCEPTION WHEN OTHERS THEN
      v_sqlcode := sqlcode;
      v_sqlerrm := sqlerrm;

      insert into tt values(v_sqlcode,v_sqlerrm);

      END chv_user_activity_log;
      /

      PL/SQL job-runs every 1 minute

      BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
      JOB_NAME => 'User_Activiy_Log'
      ,JOB_TYPE => 'PLSQL_BLOCK'
      ,JOB_ACTION => 'BEGIN chv_user_activity_log(2202);COMMIT; END;'
      ,START_DATE => systimestamp
      ,repeat_interval => 'freq=minutely;interval=1'
      );
      END;

      BEGIN
      DBMS_SCHEDULER.ENABLE( 'User_Activiy_Log');
      END;

      BEGIN
      DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'User_Activiy_Log');
      END;

      Procedure works as required. Job runs and inserts into temporary tables created for testing.

      It doesnot insert any data into my desired table user_activity.But if i run job manually using RUN_JOB in apex SQL WORKSHOP it inserts data,through toad it does not insert when manually run
      Is it that when job is enable the environment/session is different and we cant details using apex views??

      I do have select access on above apex views.

      Edited by: skapex on Jan 18, 2013 10:40 AM
        • 1. Re: accessing data from apex views using pl/sql job
          Marko Goricki
          Hi skapex,

          look at apex_activity_log source. You have to set security_group_id (workspace_id).

          Look at:
          http://apexbyg.blogspot.com/2012/11/view-data-from-apex-collections-in-ide.html :)

          Br,
          Marko Goricki
          -------------------------------------
          http://apexbyg.blogspot.com/
          • 2. Re: accessing data from apex views using pl/sql job
            skapex
            Its for apex_collection..i wanted for apex views like apex_activity_log

            Can u pls explain me what is this code doing?? What is security group id??
            • 3. Re: accessing data from apex views using pl/sql job
              Marko Goricki
              Hope that this is clear enough:
              declare
                v_workspace_id apex_workspaces.workspace_id%type;
              begin
                select workspace_id
                  into v_workspace_id
                  from apex_workspaces
                 where workspace = '&WORKSPACE_NAME';
                 
                 -- Set Workspace ID
                 apex_util.set_security_group_id(v_workspace_id);
              end;
              Br,
              Marko Goricki
              ------------------------
              http://apexbyg.blogspot.com/
              • 4. Re: accessing data from apex views using pl/sql job
                skapex
                What does apex_util.set_security_group_id do..googled but didnot understand much.

                My application is in workspace "project".

                When i do SELECT * FROM apex_workspaces I get details of project workspace in toad.

                Also select * from apex_applications gives me all applications in that schema while select * from apex_activity_log does not give any data..why so??

                Edited by: skapex on Jan 17, 2013 6:01 AM
                • 5. Re: accessing data from apex views using pl/sql job
                  Marko Goricki
                  Did you read my previous posts and tried to run code that I've sent you?
                  If you've looked at source of view apex_activity_log, you would see that there's filter on workspace_id (same as security_group_id) and the view returns activity log for the WORKSPACE.
                  As you may guess, apex_util.*set*securitygroup_id SETS the value of this filter so you can get any data. The only thing you have to do is to replace &WORKSPACE_NAME with the name (in uppercase) of the workspace you want to see activity log.

                  Br,
                  Marko Goricki
                  --------------------------
                  http://apexbyg.blogspot.com/
                  • 6. Re: accessing data from apex views using pl/sql job
                    skapex
                    Hey thanks a lot your code worked!!

                         I wrote it in my procedure and ran it through my job.Its working as needed now.
                         
                         I analysed what your code does. Let me know if I'm right or wrong
                         
                         The view apex_activity_log is based on query like
                         SELECT * FROM wwv_flow_activity_log
                    WHERE security_group_id = (SELECT wwv_flow.get_sgid
                    FROM DUAL
                    WHERE ROWNUM = 1);

                    What i understand is when i run query in toad it gets null value in above where clause so my below query doesnot retrieve any data.
                         
                         But when i set security_group_id it gets some value through wwv_flow.get_sgid and my query retrieves the data for my app_id
                         
                         SELECT l.userid,
                    a.application_name,
                    l.step_id page_no,
                    l.TIME_STAMP,
                    l.ir_report_id,
                    l.elap,
                    l.session_id,
                    l.ip_address
                    FROM apex_activity_log l, apex_applications a
                    WHERE l.flow_id = a.application_id
                    AND l.flow_id = 200; --app_id
                                        
                    Can u pls explain me what is security_group_id and its significance??Are workspace_id and security_group_id same??
                    • 7. Re: accessing data from apex views using pl/sql job
                      Marko Goricki
                      Yes, they are the same, I wrote that several times in previous posts.

                      Br,
                      Marko Goricki
                      ------------------------------
                      http://apexbyg.blogspot.com/