This discussion is archived
7 Replies Latest reply: Jan 18, 2013 3:26 AM by MarkoGoricki RSS

accessing data from apex views using pl/sql job

skapex Newbie
Currently Being Moderated
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
    MarkoGoricki Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarkoGoricki Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarkoGoricki Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarkoGoricki Journeyer
    Currently Being Moderated
    Yes, they are the same, I wrote that several times in previous posts.

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points