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:
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?
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.