Saturday, January 17, 2015

Understanding the APEX url - passing variables to items and (interactive) reports

Did you know you can recognise an Oracle APEX application through the url?

Here's the syntax:

f?p=appid:pageid:session:request:debug:clearcache:params:paramvalues:printerfriendly&p_trace=YES&cs=...

Let me give some examples:

  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::::

The first part: https://www.apexrnd.be/ords/ depends on your configuration. I'm using https, my domain name is apexrnd.be and I'm using the Oracle REST Data Services as connection to the database. This is a configuration in your webserver.

The next part is f?p= f is a procedure in the database with as paramaters (p=). f comes from flow - once APEX was called "Project Flows".

209 is my application id, 12 is my page id and 12351378808570 is my session
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:CSV::::
The first part is the same, but now as request I've CSV, page 12 is an Interactive Report and the fastest way to see your IR data as CSV is to give CSV as the request in the url
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:IR_REPORT_BASIC::::
I changed the request again to the name of a saved Interactive Report (Alternative) IR_REPORT_: so the page goes straight to that layout of the report.


  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::YES:::
Here I'm calling the page in DEBUG mode
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::::
In the clear cache section you have a couple of options, specifying the below will clear the cache for : 
- APP: whole application
- SESSION: current user session
- RIR: reset interactive report to primary report
- CIR: reset to primary report but with custom columns
- RP: reset pagination
- 12: page 12
- P12_ID: item P12_ID
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::P12_ID:1:
Here I pass a variable with the url, we'll fill P12_ID with the value 1
You can use comma separated list of items and values for example: P12_ID,P12_NAME:1,DIMITRI
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::::IREQ_CUST_LAST_NAME:Dulles
If you want to filter an interactive report you can call IR??_:
In the above case we set the customer last name = Dulles.
There are many other parameters: IREQ_ (equal), LT_ (less), IRLTE_ (less or equal), IRGT_, IRGTE_ (greater or equal), IRLIKE_ (like), IRN_ (null), IRNN_ (not null), IRC_ (contains), IRNC_ (not contains).
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::::::YES
In this case we run the page in Printer-friendly mode
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::::::&p_trace=YES
This url will generate a trace file on the system for this request.

You find some more information in the APEX Documentation or you can try online at https://www.apexrnd.be/ords/f?p=209:12:0:.

One remark: if you enable Session State Protection (SSP) you might not be able to call the parameters like this as it requires a checksum at the end which you have to calculate for example by using apex_util.prepare_url().

And finally, if you want more readable urls, some people did some posts about that, here's one of Peter.

5 comments:

Tony said...

I am glad you covered this:

https://www.apexrnd.be/ords/f?p=209:12:12351378808570:IR_REPORT_BASIC::::
I changed the request again to the name of a saved Interactive Report (Alternative) IR_REPORT_: so the page goes straight to that layout of the report.

This is something I use quite a bit but don't think it is widely known.

Knowing this syntax, you can query the apex_dictionary to get a list of saved reports for a given page and create your own dynamic list of saved report links. Sometimes instead of using the standard LOV with the saved report names, I'll create a list of horizontal links that users can click and view the different saved links. For example:

Report A | Report B| Report C

Pavel said...

Thist APEX URL Parser\Decoder can help
https://apex.oracle.com/pls/apex/f?p=67837:1:0:CHANGES:NO:::

Anonymous said...

Hello!
Generating trace file for page request is easy. I just need to add &p_trace=YES to page url.

Could you tell me how to turn on tracing for ajax plugin?
I need to find out what is going on when I press on column header of interactive report.

Unknown said...

Hi Tony,

I have an Interactive Report which has some saved private reports with the year filter as YEAR=2015. In another process, say when I click on a button, I want to copy the same IR into another year together with the saved reports. So at the end, the saved report which has been newly created should have the year filter as YEAR = 2016.

I tried using delete all the saved reports but that didn't work successfully.

Can you please help me here.

Thank you...

Anonymous said...

Hi Dimitri,

It might sound stupid, however is this kind of url safe? The end user may manipulate the url and pass in some data that he/she shouldn't, like navigate to another page, change the value of an item, etc.

Is there any way to have incomprehensible by humans urls in oracle apex?

Thank you in advance.