Setting an apex-item from PL/SQL

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

Got it from Andy’s Blog:

Within ApEx PL/SQL Processes, regions and items you can use the bind variable syntax (:PX_MY_ITEM) both to read and set the value of that item held in session state. Like so:

:PX_MY_ITEM := 'wibble';
l_my_local_variable := :PX_MY_ITEM;

However, in stored PL/SQL packages, procedures and functions you cannot use the bind variable syntax. Rather, you must use the v(‘PX_MY_ITEM’) syntax. But this is read only.

So how do you set the value held in session state for a given page item from within a stored package, procedure or function?

The answer lies in the set_session_state procedure found in the APEX_UTIL package. E.g.

        p_name  => 'PX_MY_ITEM'
      , p_value => 'wibble');

13 thoughts on “Setting an apex-item from PL/SQL”

  • How and where do we set value of apex item from a page in application ,(i.e) run time value of page item in a page to be passed to the package where in the package value is derived using v function. Kindly mention with example

    • I am new to this apex form. So sorry if things are not explained clearly.
      I have certain page items like empno,job etc in apex application. I have four buttons like create,cancel,delete and update.
      I am entering values of empno ,jobno at run time and clicking on create button
      I have a database package at back end which is supposed to process these buttons based on clicking of this
      like one seen in a url for a specific page [6 in my case]

      PROCEDURE p6_process(p_request in varchar2) IS
      request VARCHAR2(100) := p_request;
      –request VARCHAR2(100) := APEX_APPLICATION.g_request;
      p VARCHAR2(100) := ‘:P’ || APEX_APPLICATION.g_flow_step_id;



      msg(‘p6_process ‘ || request);

      CASE request
      — insert into my_my_table
      –values(p || ‘_empno’,p || ‘_job’);

      insert into my_my_table


      Alternatively I saw we can use v(’empno’) for strings
      So this can be given I believe from back end asgiven above

      But value always received is null in this case

      So I need to set up value in apex based on runtime input
      How do I achieve that and when and where in apex application I have to give it
      Say for instance at run time , empno is 1000 and job is 1
      I need in my_table values to be inserted as 1000 and 1 respectively

      Trust above details are explanatory

      • Of course in dynamic action for create button I am calliong package as my_package.p6_process(‘CREATE’) to invoke specific functionality.

      • Give your (packaged) procedure parameters p_empno, p_deptno etc.

        In you apex-application you can then run it as for example

        p6_process( p_empno => :p6_empno, p_ename => :p6_ename );

        this prevents you from having to use the V and NV functions, and allows for running your procedure from outside of apex.

        • Thanks, but when I do that like p6_process( p_empno => :p6_empno, p_job=> :p6_job) calling from apex application still value received is only null and not actual run time value for empno and job

          • Also timing of the event is to be just before when create button is pressed, this has to have all run time values entered on screen

          • Make sure you make the database server aware of the changes the client has made by utilizing the “Page Items to Submit” atrribute

  • Hi ,

    I have apex URL integrated within my application.

    URL like :

    Here from the other application we are passing :P_USER which is the user name of the current user .
    I want to use that :P_USER variable value in my apex report for user authentication and other query operations . Please guide me how i can use the :P_USER value passed from the URL in APEX authentications .

    Issues I am facing
    1. I can’t able to use the :P_USER variable value in APEX to authenticate the user so the report is open for all now .(For now i am using apex authentication for temporary).

    2. If somebody copy the URL and paste in other browser then the APEX report UI opens with out any security . How to prevent the copy paste URL working for customer ?

    3. What are the other security method i can apply in the APEX by using the variable passed in URL ? :P_USER .

    4. How i can achieve the SSO (Single sign on) APEX . We have integrated the APEX URL in other application and we have a requirement to implement the SSO . But i am new to apex and i have no idea how to implement . Could you please guide me on solving these issues .

    Please send some steps how to do that in my email: .

    Thank you in advance .


Leave a Reply