Skip to main content

Oracle APEX - Few Interesting Points

In this blog post, I am going to touch upon few interesting points I have discovered in my Oracle APEX journey. If you are new to Oracle APEX development, you may find this blogpost useful.

PL/SQL Expression vs SQL Expression

APEX offers us several condition types for server-side conditions. However, there are two condition types which looks almost same. They are "PL/SQL Expression" and "SQL Expression". So, are there any differences between these two condition types or are they actually same?

Here is simple definition for these condition types, in my own words.
  • PL/SQL Expression: As the name says, any condition which evaluates to TRUE or FALSE and which can be used as condition with IF statement in PL/SQL can be specified as "PL/SQL Expression".
  • SQL Expression: Similarly, any condition which evaluates to TRUE or FALSE and which can be used in SQL Query can be specified as "SQL Expression".
However, there are few "PL/SQL Expression" conditions that won't work as "SQL Expression" conditions. Few examples are
  • Just TRUE or FALSE: TRUE or FALSE are BOOLEAN types. We can use them in PL/SQL Expression, but not as SQL Expression condition.
  • Conditions using APEX_APPLICATION.G_X01 or APEX_APPLICATION.G_F01: G_X01 to G_X10 are variables and G_F01 to G_F50 are arrays declared in APEX_APPLICATION package. These variables and arrays can be used only in PL/SQL Expression condition type. e.g. condition APEX_APPLICATION.G_F01.COUNT > 0
Similarly, there is a "SQL Expression" condition which won't work for "PL/SQL Expression", that is DECODE. e.g. condition DECODE(:P1_GENDER,'M','Male','F','Female','Others') = 'Male'

I generally go with "PL/SQL Expression" condition type, instead of "SQL Expression" and I use CASE statement instead of DECODE. You may refer "Available Conditions" section in documentation for complete list of condition types.

APEX Collections vs Global Temporary Tables

When we have Global Temporary Tables (GTT) in Oracle DB, then why do we need APEX Collections? Can't we just use GTT in APEX? Before answering this question, first we need to understand Oracle APEX Architecture. When a request is sent from browser, then ORDS picks any free DB session lying in the pool or creates new DB session (when all existing sessions are busy) to connect to DB. That means, there is no guarantee that all requests from same APEX Session will be mapped with same DB session. Also APEX Session != DB Session.

As we know, data in GTTs can be preserved either for the whole session (ON COMMIT PRESERVE ROWS), or just for the current transaction (ON COMMIT DELETE ROWS). If we know that given APEX session always maps with a specific DB Session (i.e. there is 1-1 mapping between APEX and DB Session), then using GTTs with ON COMMIT PRESERVE ROWS clause makes sense. However, it's not the case. So, we can't use GTTs in Oracle APEX. If we use GTTs in APEX, then we may get unpredicted results.

When you are here, I recommend reading "Correlating APEX Sessions to Database Sessions" post from Joel Kallman on related topic.

Before Header vs After Header

In APEX, Page Designer > Rendering > Pre-Rendering section, we can use three rendering points. Before Header, After Header and Before Regions. "Before Regions" is clear, but what about "Before Header" and "After Header"? Are there any differences between these two? To understand this, first let’s find out what is "header".

APEX - HTML Header
HTML Header

In APEX, in general, below content is rendered into HTML header section. 
  •     Menu Toggle Icon
  •     Application Logo
  •     Application Name
  •     Navigation bar list items
  •     Navigation menu items (if menu position defined as "Top")
We can also observe it in the above screenshot. Page debug is another good place to see what is being executed before header, after header and in between these two rendering points.

So, if the process code should affect any of components rendered in HTML header section, then we should define process point as "Before Header". Otherwise, we can define it as "After Header". I generally go with "Before Header" process point and I can't recollect when I have used "After Header" process point.

Display Only Item vs Read Only Item

What is the difference between "Display Only" item and item with read only condition set as "Always"? We may get this question when choosing item type for "Primary Key" field. Because we don't want users to enter or modify primary key value, but in some cases we may want to display it in UI. So, in those cases, which approach can we choose? Which one is better?

Both "Display Only" item (with "Send On Page Submit" as "ON") and "item with read only condition set as Always" behaves exactly the same way. Once the page is loaded, if we try to modify these page item values using JavaScript and submit the page, then we will get "Session state protection violation" error. So, we can use either of these approaches for the primary key field example.

Note: For "Display Only" item, when we set "Send On Page Submit" as "OFF", then that page item value will not be submitted to server. However, when we create "Display Only" item, by default, "Send On Page Submit" is set to "ON". If you are using "Display Only" item for displaying primary key, then you may want to ensure "Send On Page Submit" flag is set to "ON".

Page Item Source vs Item Default

Martin Giffy D'Souza has written an excellent blogpost on this topic. You may read it here 

Why APEX_AUTHORIZATION.IS_AUTHORIZED returns BOOLEAN?

There is APEX API APEX_AUTHORIZATION.IS_AUTHORIZED using which we can verify whether current user passes specific authorization scheme or not in PL/SQL code. However, this returns a BOOLEAN value. That means, we can't use it inside SQL query. But, why can't it return Y or N, so we could use it in SQL as-well?

Answer to this question lies in "Validate authorization scheme" option of "Authorization Schemes". By default, "Validate authorization scheme" is set as "Once per session". That is, when "Authorization Scheme" is called for the first time, then APEX executes "Authorization Scheme" logic and stores the result in APEX internal tables (kind of server cache) for current APEX Session. On the subsequent calls to the same authorization scheme, in the same APEX session, APEX will get "Authorization Scheme" result from server cache, instead of executing the code again. This makes perfect sense. Because, in most of the cases, result of "Authorization Scheme" may not change through out the APEX session. 

So, if APEX_AUTHORIZATION.IS_AUTHORIZED returns Y or N, then we may use it in SQL queries. If we use it in SQL queries, then we will get "ORA-14551 cannot perform a DML operation inside a query" error, whenever the given "Authorization Scheme" is called for the first time and call is being made via a SQL query.

Well, that's it for now and I hope you may find this blog post interesting, if not useful 😀

Thank you.

Comments

Toufiq said…
Interesting blog Hari. Thanks for sharing.

Regarding apex_authorization.is_authorized, returning Y or N could still have been possible by making the DML on internal tables as "autonomous transaction". That would then allow us to use this API in queries.

As an Idea,to use such APIs in queries, we can have a wrapper around such APIs and make those wrapper procedures/ Functions autonomous transactions

Cheers
Toufiq
Hari said…
Hi Toufiq,

I have not tried "autonomous transaction" approach. In general, I have used "Application Item" to store "Authorization Scheme" result, and I have used application item SQL queries. Sometimes I have directly used "SQL Query" which is used in authorization scheme.

Regards,
Hari
Najeeb Khan said…
a very interesting topics and explained well.

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with