1 2 Previous Next 24 Replies Latest reply: Oct 5, 2011 3:26 AM by Joel_C RSS

    How to create an Apex 3.1 a session context programmatically?

    Luis Cabral
      Hi

      I want to do some unit testing on some pl/sql procedures that use apex collections. As I am using PL/SQL Developer and I am not connected to the database via Apex, inevitably they fail with the error "invalid parsing schema for the current workspace ID".

      How can I create programmatically a session in Apex so I can unit test my code? I am sure I have seen code to do it, but cannot find it anywhere.

      Thanks
      Luis
        • 1. Re: How to create an Apex 3.1 a session context programmatically?
          60437
          Luis,

          You need to set these package variables (and you'll have to have execute permission on the wwv_flow_security package to do so -- dangerous):

          apex_application.g_flow_id (numeric application ID)

          wwv_flow_security.g_flow_id (numeric application ID)

          apex_application.g_instance (session ID)

          wwv_flow_security.g_instance (session ID)

          wwv_flow_security.g_parse_as_schema (parsing schema)

          Scott
          • 2. Re: How to create an Apex 3.1 a session context programmatically?
            Dimitri Gielis
            Hi Scott,

            What about this one: wwv_flow_api.set_security_group_id;
            Isn't that enough to get an APEX session?

            Regards,
            Dimitri
            -- http://dgielis.blogspot.com/
            -- http://apex-evangelists.com/
            -- http://apexblogs.info/
            • 3. Re: How to create an Apex 3.1 a session context programmatically?
              60437
              Forget what I posted earlier. This should be all you need to do:
              declare
                l_workspace_id number;
                l_application_id number;
                l_user varchar2(30);
                l_owner varchar2(30);
                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 );
              --

                l_user := 'BENEDICT'; -- authenticated username

                l_application_id := 931; -- application ID

                l_owner := 'HR'; -- application owner (parsing schema)
               
                for c1 in (select workspace_id from apex_applications where application_id = l_application_id) loop
                  l_workspace_id := c1.workspace_id;
                  dbms_output.put_line('l_workspace_id:'||l_workspace_id);
                end loop;

                wwv_flow_api.set_security_group_id(l_workspace_id);
               
                apex_application.g_instance := 1;
                apex_application.g_flow_id := l_application_id;
                apex_application.g_flow_step_id := 1; 
               
                wwv_flow_custom_auth_std.post_login(
                  p_uname => l_user,
                  p_session_id => null,
                  p_flow_page => apex_application.g_flow_id||':'||1);
                 
                --flows_030100.wwv_flow_security.g_parse_as_schema := l_owner;
                    
              end;
              /
              Uncomment that last statement only if you need to (then you will need execute privilege on the package).

              Scott
              • 4. Re: How to create an Apex 3.1 a session context programmatically?
                Luis Cabral
                Scott

                Thanks for the help!

                You say "uncomment the last line if you need to". How do I know if I need to? If the connected user is different from the application parsing schema?

                In any case, the connect user is the same as the app parsing schema, and when I ran it without uncommenting that last line I got the error:

                ORA-20104: create_collection_from_query_b Error:ORA-20001: Invalid parsing schema for current workspace ID
                ORA-06512: at "FLOWS_030100.WWV_FLOW_COLLECTION", line 1223

                Then I uncommented it (I granted execute to the package beforehand) but got the same error.

                As I said, I am connected as the same database user as the application parsing schema, and the application does not require authentication. Where did it go wrong?

                Please see below the code I am using.

                Thanks
                Luis
                declare
                  l_workspace_id   number;
                  l_application_id number;
                  l_user           varchar2(30);
                  l_owner          varchar2(30);
                  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);
                
                  l_user           := 'ADMIN'; -- authenticated username
                  l_application_id := 999; -- application ID
                  l_owner          := 'PS'; -- application owner (parsing schema)
                
                  for c1 in (select workspace_id
                             from   apex_applications
                             where  application_id = l_application_id)
                  loop
                    l_workspace_id := c1.workspace_id;
                    dbms_output.put_line('l_workspace_id:' || l_workspace_id);
                  end loop;
                
                  wwv_flow_api.set_security_group_id(l_workspace_id);
                
                  apex_application.g_instance     := 1;
                  apex_application.g_flow_id      := l_application_id;
                  apex_application.g_flow_step_id := 1;
                
                  wwv_flow_custom_auth_std.post_login(p_uname      => l_user,
                                                      p_session_id => null,
                                                      p_flow_page  => apex_application.g_flow_id || ':' || 1);
                
                  flows_030100.wwv_flow_security.g_parse_as_schema := l_owner;
                
                  -- Call the procedure
                  my_pkg.my_proc;    --- ERROR HERE
                end;
                • 5. Re: How to create an Apex 3.1 a session context programmatically?
                  60437
                  revised:
                  declare
                    l_workspace_id number;
                    l_application_id number;
                    l_user varchar2(30);
                    l_owner varchar2(30);
                    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 );
                  --

                    l_user := 'BENEDICT';

                    l_application_id := 931;

                    l_owner := 'HR';
                   
                    for c1 in (select workspace_id from apex_applications where application_id = l_application_id) loop
                      l_workspace_id := c1.workspace_id;
                      dbms_output.put_line('l_workspace_id:'||l_workspace_id);
                    end loop;

                    wwv_flow_api.set_security_group_id(l_workspace_id);
                   
                    apex_application.g_instance := 1;
                    apex_application.g_flow_id := l_application_id;
                    apex_application.g_flow_step_id := 1; 
                   
                    wwv_flow_custom_auth_std.post_login(
                      p_uname => l_user,
                      p_session_id => null,
                      p_flow_page => apex_application.g_flow_id||':'||1);
                     
                     wwv_flow.show (
                         p_flow_id      => apex_application.g_flow_id,
                         p_flow_step_id => apex_application.g_flow_step_id,
                         p_instance     => apex_application.g_instance,
                         p_request      => 'FSP_SHOW_POPUPLOV'
                         );
                               
                    apex_collection.create_collection_from_query_b('FOO','select * from emp');
                        
                  end;
                  /
                  Scott
                  • 6. Re: How to create an Apex 3.1 a session context programmatically?
                    Luis Cabral
                    Scott

                    It works now, thanks.

                    If you don't mind, can you explain why did you have to add the call to wwv_flow.show?

                    And why is the request FSP_SHOW_POPUPLOV? I am assuming that call does something behind the scenes, but it is not clear to me.

                    Thanks,
                    Luis
                    • 7. Re: How to create an Apex 3.1 a session context programmatically?
                      60437
                      Luis,

                      It's actually issuing a request for a page, so it goes through the whole authentication and session setup steps, setting the correct global variables. There are other request values or ways to go through authentication but this works.

                      Scott
                      • 8. Re: How to create an Apex 3.1 a session context programmatically?
                        wcoleku
                        Hi,

                        I followed the revised code from Scott and I'm getting the following error

                        ORA-20104: create_collection_from_query_b Error:ORA-20102: Application
                        collection FOO does not exist
                        ORA-06512: at "FLOWS_030100.WWV_FLOW_COLLECTION", line 1223
                        ORA-06512: at "APEX_APPS.EMGT_NIGHT_REPORTS", line 72
                        ORA-06512: at line 1.

                        I set l_user to the same name that i use for the username when logging into apex development
                        I set l_owner to the same name that is used for the workspace when logging into apex development

                        I substituted another table for emp

                        Thanks for any suggestions

                        Wayne
                        • 9. Re: How to create an Apex 3.1 a session context programmatically?
                          60437
                          Where I refer to page 1 in these lines:

                          apex_application.g_flow_step_id := 1;

                          p_flow_page => apex_application.g_flow_id||':'||1);

                          Change that to the ID of any public page in the application.

                          Scott
                          • 10. Re: How to create an Apex 3.1 a session context programmatically?
                            wcoleku
                            Thanks Scott,

                            Changing g_flow_step_id and g_flow_id fixed my problems.

                            Wayne
                            • 11. Re: How to create an Apex 3.1 a session context programmatically?
                              659457
                              Hi Scott,
                              I'm trying to write a pl/sql script that can test an item on an Apex page, and ideally test the page for errors as well.

                              Using the sample code you've provided, how to I then obtain session state for an item?

                              For example the line:
                              APEX_UTIL.GET_SESSION_STATE('P1_MY_ITEM');
                              returns null, even though it should have been initialized when I loaded page 1 per your example.

                              Also, is there a way to catch any error when loading a page in pl/sql?

                              Thanks,
                              Galit
                              • 12. Re: How to create an Apex 3.1 a session context programmatically?
                                60437
                                Please show all your code and explain what tool you're using, etc.

                                Scott
                                • 13. Re: How to create an Apex 3.1 a session context programmatically?
                                  659457
                                  To start with I'm just trying to run this test in toad.
                                  I'm connected to the database that's running apex under some other user that I've set up.
                                  The page var is initialized on page load and should not be returning null.
                                  Using your sample code above:

                                  DECLARE
                                  VAL NUMBER;
                                  l_workspace_id number;
                                  l_application_id number;
                                  l_user varchar2(30);
                                  l_owner varchar2(30);
                                  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 );

                                  l_application_id := 200;
                                  l_owner := 'MYSCHEMA';

                                  for c1 in (select workspace_id, application_id from apex_applications where application_id = l_application_id) loop
                                  l_workspace_id := c1.workspace_id;
                                  dbms_output.put_line('l_workspace_id:'||l_workspace_id);
                                  l_application_id := c1.application_id;
                                  dbms_output.put_line('l_application_id:'||l_application_id);
                                  end loop;

                                  wwv_flow_api.set_security_group_id(l_workspace_id);

                                  apex_application.g_instance := 1;
                                  apex_application.g_flow_id := l_application_id;
                                  apex_application.g_flow_step_id := 1;


                                  wwv_flow_custom_auth_std.post_login(
                                  p_uname => l_user,
                                  p_session_id => null,
                                  p_flow_page => apex_application.g_flow_id||':'||1);

                                  wwv_flow.show (
                                  p_flow_id => apex_application.g_flow_id,
                                  p_flow_step_id => apex_application.g_flow_step_id,
                                  p_instance => apex_application.g_instance,
                                  p_request => 'FSP_SHOW_POPUPLOV'
                                  );

                                  --apex_collection.create_collection_from_query_b('FOO','select * from pcy_bb_users');

                                  VAL := APEX_UTIL.GET_SESSION_STATE('P1_MY_VAR');
                                  dbms_output.put_line('P1_MY_VAR = ' || val);

                                  END;


                                  Thanks,
                                  Galit
                                  • 14. Re: How to create an Apex 3.1 a session context programmatically?
                                    60437
                                    I'm connected to the database that's running apex under some other user that I've set up.
                                    You must connect as the application's parsing schema.

                                    Scott
                                    1 2 Previous Next