Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Integration of APEX (4.2.4.00.08) in OBIEE 11g - After login in OBIEE the APEX login mask is display

Fabio CrozoeOct 10 2018

After login in OBIEE the APEX login mask is displayed.

OBIEE uses this function to create a APEX session and store the APEX session-id and username in the table apex_biee_session:

create or replace PACKAGE BODY "APEX_BIEE_INT" IS 

  -- ---------------------------------------------

  -- Function GET_APEX_SESSION_ID

  -- sets up an APEX session for a BIEE user

  -- ---------------------------------------------

  FUNCTION get_apex_session_id (p_username IN VARCHAR2,p_days_valid IN NUMBER DEFAULT 1) RETURN VARCHAR2

  IS

    pragma       autonomous_transaction;

    l_session_id NUMBER;

    l_valid_to   DATE;

    l_count      NUMBER;

    l_password   VARCHAR2(4000);

  BEGIN

    l_valid_to := SYSDATE + NVL(p_days_valid,1);

    -- Let us delete expired records:

    BEGIN

      DELETE FROM apex_biee_session

        WHERE valid_to < TRUNC(SYSDATE,'DD');

      COMMIT;

    EXCEPTION

      WHEN NO_DATA_FOUND THEN NULL;

    END;

    -- get next APEX session id:

    l_session_id := apex_custom_auth.get_next_session_id;

    -- Insert the BIEE user and the APEX session id in table APEX_BIEE_SESSION

    INSERT INTO apex_biee_session (username, sessioN_id, valid_to)

        VALUES (UPPER(p_username),l_session_id,l_valid_to);

    COMMIT;

    -- define an APEX user session:

    apex_custom_auth.define_user_session(

      p_user       => UPPER(p_username),

      p_session_id => l_session_id);

      htmldb_application.g_unrecoverable_error := TRUE; -- tell apex engine to quit

    RETURN l_session_id;

  EXCEPTION

    WHEN OTHERS THEN RETURN '-99';

  END get_apex_session_id;

  

  -- ---------------------------------------------

  -- Function PAGE_SENTRY

  -- used as page sentry function in APEX applications

  -- ---------------------------------------------

  FUNCTION page_sentry RETURN BOOLEAN

  IS

    l_current_sid NUMBER;

    l_biee_userid VARCHAR2(255);

    l_cookie      owa_cookie.cookie;

    l_c_value     VARCHAR2(255) := NULL;

    l_cookie_tom  owa_cookie.cookie;

    l_c_value_tom VARCHAR2(255) := NULL;

    l_session_id  NUMBER;

    l_biee_auth   VARCHAR2(1) := 'N';

  BEGIN

    BEGIN

      -- If normal APEX user authentication is used, cookie LOGIN_USERNAME_COOKIE will be used

      l_cookie_tom  := owa_cookie.get('LOGIN_USERNAME_COOKIE');

      l_c_value_tom := l_cookie_tom.vals(1);

      l_biee_userid := UPPER(l_cookie_tom.vals(1));

    EXCEPTION

      WHEN OTHERS THEN NULL;

    END;

   

    l_session_id := apex_custom_auth.get_session_id;

    --

    -- Do we have a record in table APEX_BIEE_SESSION with the current session id

    --

    BEGIN

      SELECT UPPER(username) INTO l_biee_userid

        FROM apex_biee_session

        WHERE session_id = l_session_id AND valid_to > SYSDATE;

      l_biee_auth := 'Y';

    EXCEPTION

      WHEN NO_DATA_FOUND THEN l_biee_userid := 'Failed';

    END;

   

    IF l_biee_userid = 'Failed' THEN

      IF l_c_value_tom IS NULL THEN

        l_biee_userid := NULL;

      ELSE

        l_biee_userid := UPPER(l_c_value_tom);

      END IF;

    END IF;

    -- If l_biee_userid is NULL we need to call the APEX login page (done by RETURN FALSE)

    IF l_biee_userid IS NULL THEN

      RETURN FALSE;

    END IF;

   

    IF l_biee_auth = 'N' THEN

      l_current_sid := apex_custom_auth.get_session_id_from_cookie;

    ELSE

      l_current_sid := l_session_id;

    END IF;

    --

    -- This is the built-in part of the session verification

    --

    IF apex_custom_auth.is_session_valid THEN

      wwv_flow.g_instance := l_current_sid;

      IF apex_custom_auth.get_username IS NULL THEN

        apex_custom_auth.define_user_session(

          p_user       => UPPER(l_biee_userid),

          p_session_id => l_current_sid);

        RETURN TRUE;

      ELSE

        IF UPPER(l_biee_userid) = UPPER(apex_custom_auth.get_username) THEN

          apex_custom_auth.define_user_session(

            p_user       =>UPPER(l_biee_userid),

            p_session_id =>l_current_sid);

          RETURN TRUE;

        ELSE -- username mismatch. Unset the session cookie and redirect back here to take other branch

          apex_custom_auth.logout(

            p_this_app=>v('APP_ID'),

            p_next_app_page_sess=>v('APP_ID')||':'||nvl(v('APP_PAGE_ID'),0)||':'||l_current_sid);

            wwv_flow.g_unrecoverable_error := true; -- tell htmldb engine to quit

            RETURN FALSE;

        END IF;

      END IF;

    ELSE -- application session cookie not valid; we need a new apex session

      IF l_biee_auth <> 'Y' THEN

        l_session_id := apex_custom_auth.get_next_session_id;

      END IF;

      apex_custom_auth.define_user_session(

        p_user       => l_biee_userid,

        p_session_id => l_session_id);

       

      wwv_flow.g_unrecoverable_error := true; -- tell htmldb engine to quit

      --

      IF owa_util.get_cgi_env('REQUEST_METHOD') = 'GET' THEN

        wwv_flow_custom_auth.remember_deep_link(

          p_url=>'f?'||wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));

      ELSE

        wwv_flow_custom_auth.remember_deep_link(

          p_url=>'f?p='||

          TO_CHAR(wwv_flow.g_flow_id)||':'||

          TO_CHAR(nvl(wwv_flow.g_flow_step_id,0))||':'||

          TO_CHAR(wwv_flow.g_instance));

      END IF;

      --

      apex_custom_auth.post_login( -- register session in htmldb sessions table, set cookie, redirect back

        p_uname     => l_biee_userid,

        p_app_page  => wwv_flow.g_flow_id||':'||nvl(wwv_flow.g_flow_step_id,0));

      RETURN FALSE;

    END IF;

  END page_sentry;

END apex_biee_int;

What can I do to get this working? After login in OBIEE a APEX page will be called without login in APEX.

Comments

Post Details

Added on Oct 10 2018
0 comments
104 views