Wednesday, August 15, 2012

How to Create an APEX Session in PL/SQL

Debugging a function or procedure which references APEX items using the V/NV functions or trying to debug APEX collections can be frustrating when the only way to set, and view, the values is to actually do it through the application. Thankfully, there's a way to create an APEX session from a PL/SQL session to test out your code.

They're various examples on the APEX forums on how to create an APEX session in PL/SQL. Here's one version of it.

CREATE OR REPLACE PROCEDURE sp_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;
To create an APEX session (in PL/SQL) to mimic some tests I can do the following:
SQL> BEGIN
  2    sp_create_apex_session(
  3      p_app_id => 106,
  4      p_app_user => 'MARTIN',
  5      p_app_page_id => 10);
  6  END;
  7  /

PL/SQL procedure successfully completed.
View some APEX session state variables:
SQL> SELECT v('APP_USER') app_user, v('APP_SESSION') app_session,
  2    v('APP_PAGE_ID') page_id, v('P1_X') p1_x
  3  FROM dual;

APP_USER   APP_SESSION     PAGE_ID    P1_X
---------- --------------- ---------- ----------
MARTIN     374363229560201 10

SQL> -- Set P1_X
SQL> exec apex_util.set_session_state('P1_X', 'abc');

PL/SQL procedure successfully completed.

SQL> SELECT v('APP_USER') app_user, v('APP_SESSION') app_session,
  2    v('APP_PAGE_ID') page_id, v('P1_X') p1_x
  3  FROM dual;

APP_USER   APP_SESSION     PAGE_ID    P1_X
---------- --------------- ---------- ----------
MARTIN     374363229560201 10         abc

SQL> -- Clear APEX Session State
SQL> exec APEX_UTIL.CLEAR_APP_CACHE(p_app_id => 106);

PL/SQL procedure successfully completed.
You can also create and view collections using the APEX_COLLECTION APIs and the APEX_COLLECTION view.

15 comments:

  1. Good post Martin.

    One small addon I always use because most of our applications use LDAP and post_login lets APP_USER empty when called with a non-apex user:

    if v('APP_USER') is null
    then
    apex_application.g_user := p_app_user;
    end if;

    This might help someone who runs your procedure but is confronted with an empty APP_USER.

    ReplyDelete
  2. You could replace this
    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);

    By this
    WWV_FLOW_API.SET_SECURITY_GROUP_ID(APEX_APPLICATION.GET_CURRENT_FLOW_SGID(p_app_id));

    ReplyDelete
  3. ALTER SESSION SET CURRENT_SCHEMA = APEX_040100

    ------se creaza o procedur aca sa fier mai simplu
    CREATE OR REPLACE PROCEDURE sp_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;

    --se face o sesiune in apex

    BEGIN
    sp_create_apex_session(p_app_id => 111, p_app_user => 'ADMIN', p_app_page_id => 1);
    END;



    View some APEX session state variables:

    SELECT v('APP_USER') app_user, v('APP_SESSION') app_session, v('APP_PAGE_ID') page_id, v('P1_X') p1_x FROM dual;



    DECLARE
    l_clob CLOB;
    l_file_name VARCHAR2(255);
    p_workspace_id number;
    BEGIN
    select WORKSPACE_ID into p_workspace_id from apex_workspaces where WORKSPACE_DISPLAY_NAME='REPO';
    wwv_flow_api.set_security_group_id(p_workspace_id);
    l_clob := apex_040100.wwv_flow_utilities.export_workspace_to_clob(p_workspace_id => p_workspace_id, p_include_team_development => false);
    DBMS_XSLPROCESSOR.clob2file( l_clob, 'DATA_PUMP_DIR', 'WSrepo.sql');
    END;

    --------------------------aplicatia ramane la fel
    begin
    DBMS_XSLPROCESSOR.clob2file(wwv_flow_utilities.export_application_to_clob(111, 'N', 'N', 'N'), 'DATA_PUMP_DIR', 'f111.sql');
    end;

    ReplyDelete
  4. Awesome post, Martin!

    Can you tell, please, how can we pass the password to this procedure and provide authentication via PL/SQL procedure?

    ReplyDelete
    Replies
    1. You don't need to create the password. This is all "post authentication" i.e. it assumes that the user's login credentials have been validated.

      Delete
  5. When you finish the work in the session that you created, how you close or kill or clear this session...Thanks for you answer!

    ReplyDelete
    Replies
    1. You could try: APEX_CUSTOM_AUTH.LOGOUT http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_auth.htm#BABICDIB) I haven't tested it but it's worth a try.

      Delete
  6. Hi Martin,

    Have you tried this in 4.2.3 or above? I've copied your code and get an error with a no_data_found:

    ORA-20987: APEX - Error in PLSQL code raised during plug-in processing. - ORA-20001: Error while doing post authentication_process - ORA-01403: no data found
    ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 861
    ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 896
    ORA-06512: at "APEX_040200.WWV_FLOW_CUSTOM_AUTH_STD", line 783
    ORA-06512: at "APEX_040200.WWV_FLOW_CUSTOM_AUTH_STD", line 647
    ORA-06512: at "APEX_040200.HTMLDB_CUSTOM_AUTH", line 238
    ORA-06512: at line 31

    Have you encountered this&

    ReplyDelete
    Replies
    1. I've seen it before but can't recall the exact situation. Do you have a custom authentication that may cause an issue like this?

      Delete
  7. Thanks for the quick response. Yes I do have a custom authentication on my application. That's why I was a bit skeptical that it would work, but since the post_login assume the authentication worked, I thought it wouldn't be an issue.

    So, yes I do have a custom authentication. Then I tried doing it with DEFINE_USER_SESSION which according to the Doc it registers a session:

    APEX_CUSTOM_AUTH.DEFINE_USER_SESSION (
    :APP_USER,
    APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID);

    But then when I created the collection I had a parent key not found error:

    ORA-20104: create_collection_from_query_b Error:ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_COLLECTION_FK) violated - parent key not found
    ORA-06512: at "APEX_040200.WWV_FLOW_COLLECTION", line 1878
    ORA-06512: at "APEX_040200.WWV_FLOW_COLLECTION", line 1915
    ORA-06512: at line 54

    ReplyDelete
    Replies
    1. Hmm makes sense but not sure why your collection is failing as I know I've done that before to test some of my packages. I'm a bit tied up at the moment and won't be able to investigate further. Perhaps post something on the OTN Forums? http://forum.oracleapex.com If you do resolve it please post back here for future reference. Thanks.

      Delete
    2. Hi Martin, i also testet it with creating a session in the database and than creating a collection, but i still get the error:
      ORA-20104: create_collection_from_query Error:ORA-20001: Invalid parsing schema for current workspace ID

      any ideas?

      Delete
    3. Unfortunately not. Do you have any additional login code as part of a custom login?

      Delete
  8. Hi Martin,

    I just want to give you some update on my "no data found" issue. It was caused by a query that I had in my post_authentication code of my custom authentication.

    I thought that the "post_login" would replace the post_authentication, but it looks like it also runs.

    All in all, thanks a lot!

    ReplyDelete