Integration of APEX (4.2.4.00.08) in OBIEE 11g - After login in OBIEE the APEX login mask is displayed
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.