0 Replies Latest reply on Oct 10, 2018 12:38 PM by Fabio Crozoe

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

    Fabio Crozoe

      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.