"ORA-22902: CURSOR expression not allowed" in ORDS and APEX and how to fix them

When you want to define different blocks of data, some hierarchical, some not, you can do that by using the cursor expressions in SQL. An example of a query looks like this:

  select
    c.cust_first_name,
    c.cust_last_name,
    c.cust_city,
    cursor(select o.order_total, order_name,
              cursor(select p.product_name, i.quantity, i.unit_price
                       from demo_order_items i, demo_product_info p
                      where o.order_id = i.order_id
                        and i.product_id = p.product_id
                    ) product                
             from demo_orders o
            where c.customer_id = o.customer_id
          ) orders
  from demo_customers c

In the above query you see you can nest the cursor expressions. But you can also define the cursors next to each other. We use this technique a lot when defining where the data comes from in the APEX Office Print (AOP) APEX plugin:

In Oracle Application Express 18.1 there's a small bug (Bug 28298260 - REGRESSION: SQL QUERY CONTAINING CURSOR EXPRESSION CAN'T BE PARSED) that when you validate the query you get "ORA-22902: CURSOR expression not allowed".

The APEX Dev team already fixed it - you can download from Oracle Support the bundle PSE patch #28128115. Once applied everything is validating correctly again.

AOP also supports REST web services, and some people define those in ORDS (Oracle REST Data Services). Depending the version of ORDS you might get the same error: "Error during evaluation of resource template: GET test/cursor/, SQL Error Code: 22,902, SQL Error Message: ORA-22902: CURSOR expression not allowed"

This doesn't mean your environment can not use the cursor syntax, you just have to set the pagination size to 0 and it's fixed.

In the latest version of ORDS (18.2) you get by default the 500 error without the error number:

Fix is the same, set pagination to 0 and you are good to go.

Hide certain objects on an APEX page

A few days ago I got a question on how to hide the title row from the Interactive Report Pivot view.
So the person didn't want to show the red area:

The solution to this problem is to add the following CSS to your page:

table.a-IRR-table--pivot tr:nth-child(3) {
    display:none;
}

The result is this - the title is gone:

Doing this blog post is not about giving the solution to the above problem. I find it more important to show you the process to come to your answer. It comes down to find the right elements on the page which you can manipulate with CSS or JavaScript. To hide something, you can either use CSS with display: none or a JavaScript function (or JQuery hide()). The first thing you do is a search for the element. You want to use the Developer Tools of your browser for that. Most of the time you can right click on your page and do Inspect Element. The browser will show the HTML that is behind what you see on the page.

In the above screenshot, I see that row is a TR in a Table.
So the next step is to find a way to select that element. Typically you would use the id or class tag and look that up. The TR in our case doesn't have any of those, so I went up a line in the hierarchy until I find a good selector. The table has a class a-IRR-table--pivot which we can use.
Once we have the selector, we want to go to the real element, so we navigate back down. Now you need to know a bit of JavaScript or CSS or search on the internet how to do that. You can add elements after each other and it will drill down in the hierarchy again.
In our case, the TR is the third TR in the table, and there's a function to select that, which I used in CSS (nth-child).

If this is all new to you, learning about JavaScript and CSS selectors is a great start. For example, W3School is a nice site to get started learning more about HTML, CSS, JavaScript, and general web.


Note that in Oracle APEX, you can also use a dynamic action to hide or show certain elements. A dynamic action is a declarative way to do JavaScript, so when you use the Hide / Show in a dynamic action, behind the scenes it will do the necessary call for you. If the item or region is not known for APEX you would use the same technique as I described above to find the right element which you can reference in the dynamic action with a JavaScript selector (. for class # for id).
Typically doing something with CSS is more performant than doing it with JavaScript, but it all depends on your use case what technique makes sense to use.

I'll be at APEX Meetup Munich: Thu 19 Jul 2018

Just a quick note I'll do two presentations at the APEX Meetup in Munich on Thursday, July 19th 2018.

In the first presentation I'll bring you to a virtual and augmented world, entirely build in Oracle Application Express (APEX). There are 30 Google Cardboards available to make the experience complete. Fun guaranteed! :)

At KScope I was also interviewed by Bob Rhubart on my talks over there, which the AR/VR presentation was one of them.

In my second presentation at Munich I'll show the upcoming version of APEX Office Print (AOP).
I'll show some features nobody has seen before :) With every major release of AOP I feel like this:

If you are in the Munich area I would love to meet you at the meetup.

My top 5 APEX 18.1 Plug-ins

With every new version of Oracle Application Express (APEX) new features are added and the life of a developer is made even easier. If the feature set is not enough or you see you need to build the same functionality more often, you can always extend APEX with plug-ins.

There are six different types of plug-ins: dynamic action, item,  region, process, authentication scheme and authorization scheme.

Plug-ins are absolutely fantastic to extend the native functionalities of APEX in a declarative way. The APEX plug-in becomes a declarative option in the APEX Builder and has the [Plug-in] text next to it. In the next screenshot, you see the dynamic actions being extended by two Plug-ins.

If you are searching for an APEX plug-in, I typically go to APEX World > Plug-ins. The nice thing about that site is that the plug-ins seem to be maintained, so if a plug-in is not supported anymore it gets the status deprecated.

!! And here lays the catch with using plug-ins. When you decide to use a plug-in in your project, you become responsible for this and need to make sure it's compatible with every release of Oracle APEX. Many plug-ins are open source and many plug-in developers maintain their plug-ins, but it's really important you understand that at the end you are responsible for things you put in your application. If the plug-in is not secure or it breaks in the next release of APEX, you need to find a solution. So use plug-ins with care and see for example how many likes the plug-in has or what the comments are about the plug-in or author. Oracle is not reviewing or supporting the plug-ins !!

When I saw the tweet of Travis, I thought to do a blog post on my top 5 plug-ins I use in almost every project.

Here we go:

1. Built with love using Oracle APEX

I'm proud to built applications with Oracle Application Express, and this plug-in makes it very clear :) At the bottom of the app, you will see this text:

Note that in Oracle APEX 18.1 this text in included by default and you don't even need to add the plug-in. Nevertheless, I wanted to include it in this list as it should be there in every app, even the ones built before APEX 18.1 :)

2. Select2

When a select list (or drop-down) has many values, it takes too long to find the right value. Select2 makes it easy to search for values, it also supports lazy loading and multiple select.

3. APEX Office Print

APEX Office Print extends APEX so it becomes possible to export to native Excel files and generate documents in Word, Powerpoint, PDF, HTML and Text, all based on your own template. It has many more features, I blogged about some before.

4. Dropzone

APEX 18.1 has declarative multi-file upload, but still, I love the Dropzone plug-in developed by Daniel Hochleitner. You can drag multiple files from your desktop straight in your APEX app. Daniel is one of my favorite plug-in developers. When he releases something, you know it will be good.

5. Modal LOV

This is a newer plug-in and I haven't used it that much yet, but I'm sure I will do. The nice thing with this item type plug-in is that it also supports Interactive Grid. Where Select2 stays within the page, this Modal LOV comes with a modal list of values (pop-up) which is great if you want to show multiple columns or need more context for the record you need to select.

There are many more plug-ins out there, most of them work on APEX 5.x and upwards. For example, Pretius has some cool plug-ins too, the one to create nested reports I recently used in a project. Another site you can find plug-ins is APEX-Plugin.com.