Oracle Apex Reports显示不同用户的数据

Question

我正在开发Oracle Apex，但对它并不熟悉。我正在开发的应用程序已经由其他离开公司的开发人员开发。问题是-有一个主/超级用户与之建立了实际的数据库连接（使用DADS.conf文件条目）。之后，用户将看到一个Login屏幕，在该屏幕中他输入db subuser凭据。我们定义的应用程序级上下文很少，所以在subuser登录后，我们为该subuser设置类似用户名的上下文。在应用程序中，有一个报告应该显示已登录子用户的数据。现在，当第一个用户登录时，报告会显示正确的数据。当第二个用户从不同的计算机登录时，报告开始在两个应用程序实例中显示第二个使用者的数据。有时第二个用户的报告会显示第一个用户的数据。这种行为是随机的。除了报告之外，其他应用程序运行良好。在这种情况下，Apex似乎使用了相同的物理数据库连接，因此上下文被覆盖了。我可能错了。我很困惑，找不到任何解决办法。感谢您的帮助。

这是报告查询（非常复杂）-

With 
Date_range as (
   select * from X_LOHAC_DateRANGE_WK
    )
  --
    ,Haud_a
        AS (            select wor_works_order_no haud_pk_id, WOR_CHAR_ATTRIB110 HAUD_NEW_VALUE, WOR_DATE_ATTRIB131 HAUD_TIMESTAMP
          from work_orders
          where 1=1
             AND WOR_CHAR_ATTRIB110 in  ('REV', 'REVUPD','REVCOMM','APPCOMM','APP','REJCOMM','REJ','INTREJ','APPUN')
             AND WOR_DATE_ATTRIB131 BETWEEN (select min(st_range) from Date_range) AND (select max(end_range) from Date_range)
            )            
      , haud as (
          select h.*, range_value from haud_a h, Date_range 
          where   HAUD_TIMESTAMP BETWEEN st_range AND end_range 
          )
--
--
--
, claims as
(
        select a.woc_works_order_no, a.woc_interim_no, woc_claim_value, claim_previous from
            (select woc_works_order_no, woc_interim_no, woc_claim_value
            , lag(woc_claim_value) over (partition by woc_works_order_no order by woc_interim_no Asc)  as claim_previous 
            from work_order_claims
            order by woc_works_order_no, woc_interim_no asc) a
            , (select woc_works_order_no, max(woc_interim_no) woc_interim_no from work_order_claims group by woc_works_order_no) b
        where a.woc_works_order_no = b.woc_works_order_no
        and a. woc_interim_no = b.woc_interim_no 
)
--
, main as (
SELECT DISTINCT
    '<p title="Click for forms"  id="'||works_order_number||'"  onmouseover="showWOLDetails(this);">'||  WORKs_ORDER_NUMBER||'</p>' WORKS_ORDER_NUMBER
    ,decode( DECODE (mai_sdo_util.wo_has_shape (hig.get_sysopt ('SDOWOLNTH'), wor.works_order_number),
                    'TRUE', 'Y','N'),
            'N',
        '<img width=24 height=24 src="/im4_framework/images/grey_globe.png" title="No Location">'
        ,'<a href="javascript:showWODefOnMap('''||WORKs_ORDER_NUMBER||''',''~'');" ><img width=24 height=24 src="/im4_framework/images/globe_64.gif" title="Find on Map"></a>') map
    ,decode(im_framework.has_doc(works_order_number,'WORK_ORDERS'),0,
        '<img width=24 height=24 src="/im4_framework/images/mfclosed.gif" title="No Documents">'
        ,'<a href="javascript:showWODocAssocs('''||works_order_number||''',&APP_ID.,&APP_SESSION.,''WORK_ORDERS'')" ><img width=24 height=24 src="/im4_framework/images/mfopen.gif" title="Show Documents"></a>') DOCS
    ,(select ial_meaning from nm_inv_attri_lookup where ial_domain = 'INVOICE_STATUS' and ial_value = wor_char_attrib110) INVOICE_STATUS
    ,WOR_CHAR_ATTRIB111 as "INVOICE_STATUS_COMMENT"
    ,works_order_description
    , claim_previous Previous_Claim_Amount
    , woc_claim_value New_Claim_Amount
    , WOR_CHAR_ATTRIB111  Claim_Comments
     ,bud.work_category_description  Budget_Description
    , 'BOQ' BOQ
        ,WOR_CHAR_ATTRIB115    "Correct area of work "
        ,WOR_CHAR_ATTRIB116    "Quality of Work OK"
        ,WOR_CHAR_ATTRIB70    "Correct BOQ_Uplifts" 
        ,WOR_CHAR_ATTRIB113    "Before_After_Photos_Present" --"Before After Photos Present"
        ,WOR_CHAR_ATTRIB114 "Certification Comments"
--,(select hus_name from hig_audits, hig_users where haud_pk_id = haud.haud_pk_id and haud_timestamp = haud.haud_timestamp and haud_new_value = haud.haud_new_value and haud_attribute_name = haud.haud_attribute_name and  rownum =1) Reviewed_By
,  (select HUS_NAME from hig_users where hus_user_id = WOR_NUM_ATTRIB04 )   Reviewed_By  
  , works_order_number wor_number
, 'Edit' rec_edit
--
    FROM imf_mai_work_orders_all_attrib wor,
        imf_mai_work_order_lines wol,
        haud
    ,claims        
    ,imf_mai_budgets bud
        ,pod_nm_element_security,
        pod_budget_security
    WHERE    1=1
        and wol.budget_id = bud.budget_id
        AND works_order_number = haud_pk_id
        AND works_order_number = claims.woc_works_order_no(+)
        AND works_order_number = work_order_number
        AND pod_nm_element_security.element_id = wol.network_element_id
        AND pod_budget_security.BUDGET_CODE = wol.work_category
        AND  WOR_CHAR_ATTRIB110 = haud_new_value
        AND range_value = :P40_DAYS
        AND WOR_CHAR_ATTRIB110  = :P40_PRIORITY
        )     
--
Select * from main;

---

设置上下文只使用DBMS_SESSION包，如下所示-

DBMS_SESSION.SET_CONTEXT('NM3SEC', p_attribute, p_value);

在这种情况下，-p_attribute是USERNAME

Answer 1

您不能将DBMS_SESSION与apex一起使用，因为它是一个在多个会话之间汇集多个连接的无状态框架。

Apex使用基于url的会话id。

你应该选择

1） 在共享组件中创建应用程序项。2） 使用apex_util.set_session_state 将项目的值设置为登录后过程

在SQL代码中使用该项的值。