Categories
ApEx Oracle

LOV query is invalid in Oracle ApEx 4.1 when using subquery

So you create a List of Values in Oracle Application Express 4.1 using a simple in-line query similar to the one below:


SELECT d, r FROM
(
SELECT 1 d, 1 r
from dual
UNION ALL
select 2 d, 2 r
from dual
UNION ALL
select 3 d, 3 r
from dual
)

You get the following error:

1 error has occurred
LOV query is invalid, a display and a return value are needed, the column names need to be different. If your query contains an in-line query, the first FROM clause in the SQL statement must not belong to the in-line query.

LOV query invalid
LOV query invalid

You copy the query, paste it in SQL Commands in SQL Workshop, and it ran successfully. You try to create the LOV one more time, but you get the same error again. By this time your brain is working overtime thinking of a thousand different workarounds. There are a hundred strands of hair on your desk resulting from your frustration.

Lucky for you, you stumbled upon this site because the solution is simple.

Put a space after the first “FROM” keyword in your query.

LOV query invalid workaround
LOV query invalid workaround
Categories
ApEx Bug Oracle

Oracle ApEx 4.1 Incorrect Sort Order Image for Classic Reports

We recently upgraded our Oracle ApEx installation to 4.1. No major issues but a significant annoyance is that when you click on a sortable column on a classic report, the sort image (up and down arrow/pointers) are reversed. It appears that the problem is currently not patched by Oracle, but fortunately, there’s a workaround which works quite nicely and I’ve provided step by step instructions here with screen shots.

There are three main steps to apply the workaround. First step is to add a JavaScript to your page template.

Step 1. Add JavaScript

  • Login to Application Express
Login to Oracle Application Express
Login to Oracle Application Express
  • Click on “Application Builder”
  • Click on the application you want to modify
  • Click on “Shared Components”
  • Under “User Interface”, click on “Templates”
  • In the list of templates, scroll down to “Page” and edit the default template (indicated by a tick)
Edit Default Page Theme
Edit Default Page Theme
  • Paste the following javascript in the Header definition just before the closing head tag (</head>):

<script type="text/javascript">
<!--
function correctStdReportSortImage(pThis){
var $img = pThis instanceof jQuery ? pThis : $(pThis);

//Only run the code if the image actually exists.
//Since this code will be listening to the entire document’s refresh events it could get other events (such as IR)
if ($img.length > 0) {
//If the href has ‘desc’ in it then make sure the image is ascending as the presence of “desc” means column is sorted asc
$img.attr(‘src’, $img.parent().children(‘a’).attr(‘href’).indexOf(‘desc’) > 0 ? $img.attr(‘src’).replace(‘down’, ‘up’) : $img.attr(‘src’).replace(‘up’, ‘down’));
}
}//correctStdReportSortImage
//–>
</script>

Paste Code in Header
Paste Code in Header
  • Apply changes then click on “Application Builder”

Step 2. Add First Dynamic Action on Page 0

  • Click on the application you want to modify
  • Click on page 0 to edit it. If you haven’t created page zero, you need to create one. Instructions on how to create page 0 can be viewed here.
Click Page 0
Click on Page 0
  • Under “Dynamic Actions” click on the icon to add a new action
Add New Dynamic Action
Add New Dynamic Action
  • Click on “Advanced”
Click On Advanced
Click On Advanced
  • Type in a name (anything will do, for this example I typed in “Page Sort Image Fix Page Load”) then click on the “Next >” button
Type in Dynamic Action Name
Type in Dynamic Action Name
  • Under “Event” select “Page Load”, leave condition as is then click on the “Next >” button
Select Page Load Event
Select Page Load Event
  • Under “Action” select “Execute JavaScript Code” then paste the following JavaScript into “Code”:

$(this.affectedElements).each(function(){
correctStdReportSortImage(this);
});

Select Execute JS Code
Select Execute JS Code
  • Click Next
  • Under “Selection Type” select “jQuery Selector” then paste the following text under “jQuery Selector”:

.rpt-sort img

jQuery Selector
jQuery Selector
  • Click on the “Create” button

Step 3. Add Second Dynamic Action on Page 0

  • Under “Dynamic Actions” click on the icon to add a new action
  • Click on “Advanced”
  • Type in a name (anything will do, for this example I typed in “Page Sort Image Fix After Refresh”) then click on the “Next >” button
Type in 2nd Dynamic Action Name
Type in 2nd Dynamic Action Name
  • Under “Event” select “After Refresh”
  • Under “Selection Type” select “DOM Object”
  • Under “DOM Object” enter the following text:

document

After Refresh Action Parameters
After Refresh Action Parameters
  • Leave condition as is then click on the “Next >” button
  • Under “Action” select “Execute JavaScript Code” then paste the following JavaScript into “Code”:

correctStdReportSortImage($(this.browserEvent.target).find('.rpt-sort img'));

2nd Dynamic Action JS
2nd Dynamic Action JS
  • Click Next
  • Leave “Selection Type” as is then click on the “Create” button

That’s it. Good luck.