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.

26 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. This comment has been removed by the author.

    ReplyDelete
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. Hello Martin , I need from my application on apex redirect to a page of an external provider, that provider then redirects me back to a specific page within my application, they almancenan the url of my pg , but when I redirect my application loses session as could come and go without losing the session ? thanks

    ReplyDelete
    Replies
    1. See https://community.oracle.com/message/13084532 (specifically answer from Christian)

      Delete
  11. Hi Martin,
    would it be possible to launch the apex application page from within PL/sql by extending this procedure?
    thanks,
    Frantisek

    ReplyDelete
    Replies
    1. By launching an APEX page do you mean simulating a page load?

      Delete
  12. I mean to show an Apex page. We have a pure mod_plsql application and some Apex applications on the same scheme. I wonder if this is a possible way how the mod_plsql application user could see seamlessly an Apex page?

    ReplyDelete
    Replies
    1. Why not either iFrame or just re-direct them to your APEX app using straight HTML?

      Delete
  13. Hi Martin,

    thank you for this post. I want to run through different workspaces. How can I "unset" the old and set a new security_group_id in one PL/SQL call? I tried to use APEX_CUSTOM_AUTH.LOGOUT, but it doesn't work.

    Thanks,
    Markus

    ReplyDelete
    Replies
    1. Right now we don't have any way of doing it. This code has been merged into OOS Utils: https://github.com/OraOpenSource/oos-utils/blob/master/source/packages/oos_util_apex.pkb I've created a new issue for the logout/unset idea you mentioned: https://github.com/OraOpenSource/oos-utils/issues/127

      Delete
  14. Hi Martin,
    Do you know why the sessions created using this method do not appear in Apex Monitor Activity -> Active Sessions? Nothing about the access is shown in Monitor Activity -> By View (or any other option).

    Is there something that coud be done to have these logs working? (Apex 5.0)

    Thank you,
    Florin

    ReplyDelete
    Replies
    1. Not sure. Can you please post your question here: https://github.com/OraOpenSource/oos-utils We've moved this code into OOS Utils and will handle all support issues there.

      Delete