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.

APEX_UTIL.set_session_state(
        p_name  => 'PX_MY_ITEM'
      , p_value => 'wibble');

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

  1. 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

    Reply
    • 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;

      –APEX_APPLICATION.g_request;

      BEGIN

      msg(‘p6_process ‘ || request);

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

      insert into my_my_table
      values(v(’empno’),v(‘job’));

      END;

      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

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

        Reply
      • 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.

        Reply
        • 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

          Reply
          • 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

  2. Hi ,

    I have apex URL integrated within my application.

    URL like : https://slms.ABCDEF.com/apex/f?p=107:3:::::P_USER:c2FyZ2JlaGU=

    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: gyanabehera2013@gmail.com .

    Thank you in advance .

    Regards
    Gyana

    Reply

Leave a Reply