Apex: Report Sort Option showing strange behaviour …

A few days ago I was making a basic report page in ApEx, but when I tried to run the page I got the next error:

Although the report was based on a simple select-from-one-table, it took me quite some time to found out what went wrong:

The first thing I did was checking my SQL Query:

My query is running smoothly in my SQL Developer and I didn’t modify anything on the column/report attributes; so I expected my report to display normally.

So why is it producing this “ORA-00911: invalid character” error?

On the Oracle apEx Forum I made a post explaining my problem.

Meanwhile I did some further investigations; I removed my custom header-names and my sort option. I was amazed when all of a sudden my report was displaying normally like nothing ever happened. When enabling the sort function again , the above error showed once more.

When posting this new info on the forum, someone made it clear to me that I had to remove the ‘;’ at the end of my query. I followed this advice and enabled the sort option again; and indeed my report was displaying normally with the sort option on. The explanation is that when you establish a sort via the wizard, Apex is dynamically extending your SQL statement to apply the order by clause and the “;” makes the SQL invalid.

So to cut a long story short: don’t use a ‘;’ at the end of your SQL query in your report source, the query runs fine without it and you have no problems enabling other options.


2 thoughts on “

  1. t (title click on columns) will revoke this query and adding “order by colxx”. So if there is “;” at the end then this quesry looks like:select… from ; ordery by colxxAnd this is wrong SQL sentence…;D

  2. I am trying to sort on multiple columns,how do i do that?.what is the importance of sort sequence? and can i have a default sort like sorting based on col1,col2,col3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

About Olivier Dupont