2 Replies Latest reply: Jan 17, 2013 1:30 AM by Hilton RSS

    Backend testing with User ID

    Hilton
      Hi there,
      I would like to test some procedures and functions on the backend for a customer application.
      The procedures have insert and update functionality and I have a table that stores last_updated_by value.
      This should be the id of the user making the changes.

      I have a procedure that returns the user_id:
      FUNCTION user_id RETURN NUMBER IS
      action_name CONSTANT VARCHAR2(30) := 'USER_ID';
      l_user_id NUMBER;
      BEGIN
      l_user_id := htmldb_util.get_user_id(nvl(v('APP_USER'), USER));

      RETURN l_user_id;
      EXCEPTION
      WHEN OTHERS THEN
      RETURN - 1;
      END user_id;

      This works fine in APEX application but back end it is returning NULL as I am not logged in as a user.
      For testing purposes is there a way to create a session programatically that will return a user id?
        • 1. Re: Backend testing with User ID
          Nick B
          Hello,

          Creating an APEX session in PL/SQL should fix your problem.
          Martin Giffy D'Souza wrote a great article in which he provides the code necessary to create an APEX session from PL/SQL:
          http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html

          Good luck,
          Nick
          • 2. Re: Backend testing with User ID
            Hilton
            Thanks Nick,
            Worked like a charm.
            This is what I used for anyone who might be interested:

            procedure create_apex_session
            (
            p_app_id in apex_applications.application_id%type,
            p_app_user in apex_workspace_activity_log.apex_user%type,
            p_app_page_id in apex_application_pages.page_id%type default 1
            )
            as
            l_workspace_id apex_applications.workspace_id%type;
            l_cgivar_name owa.vc_arr;
            l_cgivar_val owa.vc_arr;
            begin
            htp.init;
            l_cgivar_name (1) := 'REQUEST_PROTOCOL';
            l_cgivar_val (1) := 'HTTP';
            owa.init_cgi_env ( num_params => 1, param_name => l_cgivar_name, param_val => l_cgivar_val) ;
            select
            workspace_id
            into
            l_workspace_id
            from
            apex_applications
            where
            application_id = p_app_id;
            wwv_flow_api.set_security_group_id (l_workspace_id) ;
            apex_application.g_instance := 1;
            apex_application.g_flow_id := p_app_id;
            apex_application.g_flow_step_id := p_app_page_id;
            apex_custom_auth.post_login ( p_uname => p_app_user, p_session_id => null,
            -- could use APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID
            p_app_page => apex_application.g_flow_id||':'||p_app_page_id) ;
            end;