Sunday, March 9, 2014

View Item Session State from PL/SQL IDE

One and a half year ago I had a post about viewing data from APEX collections from your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...). Similar to that you can view current item session state. First step should be getting grants from some internal APEX packages (executing as sys user):

grant execute on apex_040200.wwv_flow_security to test;
grant execute on apex_040200.wwv_flow_session_state to test;

After that you have to register application and session ID in your PL/SQL IDE (running in parsing schema, e.g. test):

   -- Set Application ID       
   apex_040200.wwv_flow_security.g_flow_id := 121;
   -- Set Session ID  
   apex_040200.wwv_flow_security.g_instance := 802090201361;   

Any time you want to see current session state from some item you have to refresh current session state values by executing:

   -- Refresh Item Session State Values

After that you can see value from any item in current session using function v or apex_util.get_session_state, for example:

select v('P1_TEST') from dual

Tested with APEX and Oracle XE database.

NOTE: It wouldn't be a good idea to give these grants on production instances. :)

Sunday, November 10, 2013

APEX Security Holes - Page Items to Submit Property (Part 1)

I've discovered some interesting things about APEX checksum and item session state. I'll show you some tricks (security holes) and how to avoid them.

Suppose you are authorized to see only departments 10 and 30. Your query should look like this:

  SELECT deptno, dname 
    FROM dept 
   WHERE deptno in (10,30)

and the report page:

where link "View Employees" points to page where you can see all employees in selected department. SQL on that page should look like this:

  SELECT ename    as "Name"
       , job      as "Job"
       , hiredate as "Hire Date"
       , sal      as "Salary"
       , deptno
   FROM emp
  WHERE deptno = :P2_DEPTNO

On this page checksum is turned on so you can't manipulate with URL and change department number:

The link that points to second page should look like this:


But if you accidently put item that is used for filtering employees (in my case P2_DEPTNO) to "Page Items To Submit" property of report region there is an easy way to change it and to see employees that you are not supposed to see.

If you clicked on Accounting department on first page you should see 3 employees:

After that, you just have to open your favorite web debug tool, in my case FireBug, and run following code (where R12587121803287243 is ID of your report region) that sets value of item P2_DEPTNO to 20 and refreshes report region:

Voilà! Now you should see employees in department 20:

The refresh of region can be done in other ways. For example, by clicking on pagination.
To see if report region have some items to submit in report properties you can execute this line in FireBug console:


When you change session state protection property of item P2_DEPTNO to "Checksum Required - ..." refresh of report will not work but neither the pagination on that report.

So watch out with property "Page Items to Submit"!!

In next post I'll show you how can you easily change item session state on some other ways.

Tested on APEX

Monday, October 14, 2013

Monday, September 16, 2013

Presentation on Croatian Oracle User Group conference (HROUG 2013)

This year I'll have presentation on HROUG (Croation Oracle User Group) conference that will take place in Rovinj from October 15th - 19th 2013. There will be some great presentations held by Tomas Kyte, Deneš Kubiček, Jože Senegačnik, Melanie Cafrrey and many more...

My presentation is about hybrid mobile applications in APEX.

I'm looking forward to it!

Monday, July 8, 2013

APEX static files bug

I noticed a bug regarding uploading static files in APEX. When you try to upload file with same name as existing one you'll get error message that static file name already exists. But if you query apex_application_files view you can see that this file was uploaded (like workspace file). Funny fact is that when you go to static files page in APEX builder you won't see that "hidden" file.

This can lead to problems with static files in runtime because APEX sometimes uses some of the "hidden" files and not the ones you see in APEX builder. To clean up this mess you should delete files manually.

One way to do it is to use APEX SQL Workshop, but it's also easy to do it from your favorite PL/SQL IDE. The only thing you have to do is to set workspace id (security_group_id).

    v_workspace_id apex_workspaces.workspace_id%type;
  select workspace_id
    into v_workspace_id
    from apex_workspaces w
   where workspace = upper(&p_workspace_name);

After that you can manage your static files in current session of IDE by using view apex_applicaton_files (or wwv_flow_files).

This was tested in APEX, but I know that same problem existed in earlier versions of APEX.

Tuesday, June 11, 2013

Show processing image on every submit action

There's an easy way to show processing image on each submit action in APEX.

Just create DA on global page (page 0) that fires on event "Before Page Submit" and in Action section choose "Execute JavaScript Code". The code that you should add is:


Tested on APEX version

Wednesday, June 5, 2013

Saving Values of Disabled Items to Session State

As you may know disabled items are not submitted in HTML forms (see It's same for APEX items, at least for ones disabled with dynamic actions or JavaScript/jQuery.

There's a simple workaround for this. You can create dynamic action on event Before Page Submit that executes JavaScript code that will remove HTML attribute disabled from all items on page:


If you want this to work on all pages, put this dynamic action on global page (page 0).

As I said, this will not work for text field items that have property Disabled set to Yes and Save Session State to No. To save this kind of text field items set property Save Session State to Yes. In this case input item will have HTML attribute readonly, instead of disabled.