OCIError: ORA-00907: missing right parenthesis

[NZX]

Hi, I am using oracle-enhance for a project using refinerycms with oracle DB backend. When I tried to create a new page from cms, I get this error : OCIError: ORA-00907: missing right parenthesis. The cms is setup properly. Do you know what might have caused this issue?

[NZX]

For more info:

The sql line in postgres(it works):
SELECT "refinery_pages".* FROM "refinery_pages" ORDER BY "refinery_pages"."rgt" desc LIMIT 1

However the same sql but for oracle becomes:
SELECT * FROM (SELECT "REFINERY_PAGES".* FROM "REFINERY_PAGES" ORDER BY "REFINERY_PAGES"."RGT" desc FOR UPDATE) WHERE ROWNUM <= 1

It complains OCIError: ORA-00907: missing right parenthesis.

[ugisozols]

@NZX what are you doing in Refinery CMS when this query gets executed?

[rsim]

Most probably the issue is due to this line in awesome_nested_set gem https://github.com/collectiveidea/awesome_nested_set/blob/master/lib/awesome_nested_set/awesome_nested_set.rb#L540

As you can see Oracle cannot support .lock(true).first type of query as there is not LIMIT clause in Oracle and therefore it tries to generate inline view and add additional ROWNUM condition but FOR UPDATE is not supported in inline view.

Probably you can try to patch awesome_nested_set gem and replace this line

highest_right_row = nested_set_scope(:order => "#{quoted_right_column_full_name} desc").limit(1).lock(true).first

with

highest_right_row = nested_set_scope(:order => "#{quoted_right_column_full_name} desc").limit(1).select('id').first
highest_right_row = highest_right_row.class.find(highest_right_row.id).lock(true)

Didn't test it :) but the idea is at first to get ID of first record and then do separate find by ID with FOR UPDATE lock.

[kasperite]

Hi @ugisozols, it's NZX but I'm using a different account. This query gets called after I create a new page in Refinery.
@rsim , I was indeed noticing this awesome_nested_set gem from the stack trace but wasn't sure about it. Thanks for confirming the Oracle "lock" issue and suggesting a hint:). I will give it a try at work tomorrow.

[yahonda]

Closing since it has not been updated recently.