I was creating an advanced query (see post OutSystems – Getting Database Information) for the DBCleaner application to display table space usage. When I executed the query in OutSystems Service Studio using the “Test” button, it gave a (the expected) result, so publish the application an go! Publishing the application went fine, but when I went to the page with the result of my query, it showed me an “Internal Error”. Looking in ServiceCenter at the error, it told me that the error was ORA-00907: missing right parenthesis. Very strange to get an error on the syntax of the query, while testing the query in Service Studio worked like a charm.

After some puzzling it turned out that this error was due to some lines in the query I removed by commenting them using “–“. When I removed them the error disappeared. I started testing this with some other queries (a lot simpler) and whenever I use the “–” to comment something, Service Studio will gladly accept and run the query, but the platform will return with an internal error.

It turns out that the only acceptable way to add a comment in a query, for the OutSystems platform at least, is to use /* …. */.

So for example, the next query (using the — comment syntax) will result in an internal error:

SELECT
   name,
   -- espace_id,
   physical_table_name,
   is_active
FROM ossys_entity;

And this query (using the /* */ comment syntax) will run fine:

SELECT
   name,
   /* espace_id, */
   physical_table_name,
   is_active
FROM ossys_entity;

Cause

The problem is caused by the fact that the OutSystems platform removes all Carriage Returns from the advanced querty, so the query with the “–” comment that I have listed above, will be translated by the runtime OutSystems platform to:
SELECT name,     — espace_id,     physical_table_name,      is active    FROM ossys_entity;

This way Oracle (and other database systems) will see everything after “–” as comment, thus including all other columns and the FROM clause.