OCIError: ORA-00907: missing right parenthesis

[coorasse]

On some Oracle databases the following query:

create view "entries" as SELECT 'posting' as type,
FROM postings

UNION ALL

SELECT 'reservation' as type,        
FROM reservations

returns the error in subject.

[cdinger]

Thanks for reporting, @coorasse. Do you have an example CREATE VIEW statement that's triggering the error? Also, which Oracle version are you using?

I know ORDER BY statements in a view definition can cause this confusing error (https://stackoverflow.com/questions/27068078/missing-right-parenthesis-error-with-my-view-statement-oracle). Is it possible that you're running into one of these limitations?

[coorasse]

I actually could test the second query:

create view "entries" as (SELECT 'posting' as type,
FROM postings

UNION ALL

SELECT 'reservation' as type,        
FROM reservations)

and it triggers exactly the same error. I'll investigate further and keep this issue updated

[cdinger]

Those trailing commas are causing a ORA-00936: missing expression. If I remove the commas (I assume you're stripping out some extra columns), it seems work work fine on Oracle 12.2.0.1:

create table postings (posting varchar2(50));
create table reservations (reservation varchar2(50));

create view "entries" as (
  SELECT 'posting' as type
  FROM postings
  UNION ALL
  SELECT 'reservation' as type
  FROM reservations
)

-- View "entries" created.

[coorasse]

The error was caused by a part that I cut out of my example, a call to json_object. the function didn't exist but the error was misleading. It has nothing to do with this gem.

[cdinger]

👍 Thanks, @coorasse. Glad it's working.