5 Replies Latest reply: Feb 26, 2014 3:56 AM by RenédH RSS

    Is there a way to get v('APP_USER')  to return a value in sql developer?

    RenédH

      Hi all,

       

      I had a view that looked like this:

      select *
      from projects
      where prj_manager = SYS_CONTEXT('MY_CONTEXT', 'USER_NAME');
      

       

      I found out that replacing the call to sys_context (like below) makes my query 10 times faster.

       

      select *
      from projects
      where prj_manager = v('APP_USER');
      

       

      The issue I have now is testing my view from sql developer. Is there a way to get v('APP_USER')  to return a value in sql developer?

       

      Thanks,

       

      René

        • 1. Re: Is there a way to get v('APP_USER')  to return a value in sql developer?
          kvlek

          Take a look at this blog form Martin: Martin Giffy D'Souza on Oracle APEX: How to Create an APEX Session in PL/SQL

           

          Regards,

          Kees Vlek

          ----

          < Multi-URL-link signature removed by moderator.    As per the site FAQ, that is the same as spam and you put your user account in jeopardy when you do that. >-

          • 2. Re: Is there a way to get v('APP_USER')  to return a value in sql developer?
            fac586

            RenédH wrote:

             

            Hi all,

             

            I had a view that looked like this:

            1. select
            2. from projects 
            3. where prj_manager = SYS_CONTEXT('MY_CONTEXT', 'USER_NAME'); 

             

            I found out that replacing the call to sys_context (like below) makes my query 10 times faster.

             

            1. select
            2. from projects 
            3. where prj_manager = v('APP_USER'); 

             

            That's unexpected. How did you reach that conclusion?

            • 3. Re: Is there a way to get v('APP_USER')  to return a value in sql developer?
              RenédH

              Hi fac586,

               

              I jumped to conclusions based on the output of sql developer 'Task completed in x seconds'.

               

              I tried to reproduce in SQL*Plus and got the following results.

               

              first:

               

              -- set context
              
              exec cg_cape_context_pck.set_session_2 ('REHEER');
              
              -- set session
              
              BEGIN
              
                sp_create_apex_session(
              
                  p_app_id => 1102,
              
                  p_app_user => 'REHEER',
              
                  p_app_page_id => 28);
                END;
              
              /
              

               

              Using sys_context:

              SQL> select distinct prj_id
                from cg_project_keymembers pkm
                join cg_employees emp on pkm.emp_id = emp.id
                join cg_ref_codes ref on pkm.pkm_type = ref.short_name
                where emp.inlogid = SYS_CONTEXT(cg_cape_context_pck.get_context_name, 'USER_NAME')
                and ref.reference = 'KEY_MEMBERS'
                  and ref.custom1 = 'Y'
                  and pkm.start_date < sysdate
                  and (pkm.end_date is null or pkm.end_date > sysdate);
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:01.26
              SQL> /
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:01.01
              SQL> /
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:00.99
              SQL> /
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:01.01
              

               

              Using v('APP_USER'):

               

              SQL> select distinct prj_id
                from cg_project_keymembers pkm
                join cg_employees emp on pkm.emp_id = emp.id
                join cg_ref_codes ref on pkm.pkm_type = ref.short_name
                where emp.inlogid = v('APP_USER') --SYS_CONTEXT(cg_cape_context_pck.get_context_name, 'USER_NAME')
                and ref.reference = 'KEY_MEMBERS'
                  and ref.custom1 = 'Y'
                  and pkm.start_date < sysdate
                  and (pkm.end_date is null or pkm.end_date > sysdate);
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:00.29
              SQL> /
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:00.35
              SQL> /
              
                  PRJ_ID
              ----------
                   30194
                   30215
                   30293
                   30277
                   30195
                   30193
                   30197
                   30314
              
              8 rijen zijn geselecteerd.
              
              Verstreken: 00:00:00.36
              

               

              Conclusion: v('APP_USER') is 3 times faster.

              • 4. Re: Is there a way to get v('APP_USER')  to return a value in sql developer?
                fac586

                Database version?

                 

                Definition of the g_cape_context_pck.get_context_name function?

                • 5. Re: Is there a way to get v('APP_USER')  to return a value in sql developer?
                  RenédH

                  Hi fac586,

                   

                  Your question about the get_context_name function helped me a lot!!

                   

                  It was defined as a cursor selecting the context_name from our application settings table...

                  I now use a global variable and fill that with the context_name once on initializing the package and in the function simply return the variable.

                   

                  Now sys_context is a lot faster :-)

                   

                  Thanks for you help!

                   

                  René