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.

Invalid commenting

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 of the problem

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.