DevOps, CI/CD and Automation

I'm posting this in the hope that:

a) My workaround might help someone if they ever run into the same problem, and/or,

b) Someone might have a better workaround.

The problem is that I get unexpected ORA-00942 (table or view does not exist) errors when I try to set the SQL Query Statement property of a Query object in Reports Builder to certain SELECT statements that contain a WITH clause (aka subquery factoring clause).

For example, the following SELECT statement executes as expected in SQL*Plus...

SQL> WITH 
  2      SUB_QUERY AS
  3      (
  4          SELECT
  5              1 AS X
  6          FROM 
  7              DUAL
  8      )
  9  SELECT
 10      INLINE_VIEW.X
 11  FROM    
 12      (
 13          SELECT
 14              NESTED_INLINE_VIEW.X
 15          FROM
 16              (
 17                  SELECT
 18                      SUB_QUERY.X
 19                  FROM
 20                      SUB_QUERY
 21              ) NESTED_INLINE_VIEW
 22      ) INLINE_VIEW;

         X
----------
         1

...but when I try to use it as the SQL Query Statement for a Query object in Reports Builder, I get the following error:

ORA-00942: table or view does not exist
==>SUB_QUERY

My Reports Builder version is 10.1.2.0.2 version and my database version is 10.2.0.3.0.

The "real" query I have been trying to use is much more complex than this -- this is just the simplest statement I have been able to come up with that causes the problem. In fact, I have some queries that have a similar structure (a WITH clause subquery referenced inside a nested inline view, along with some other things), but strangely do not cause the problem.

I spent some time researching the problem on Google and Metalink but did not come up with any satisfactory answers. The problem sounds similar to bug 3896963, but bug 3896963 involved UNION ALL, and is supposedly fixed in my version(s).

I tried various ways of restructuring my "real" query, but with no success -- it's going to be hard to get rid of the WITH clauses. As a "wild guess", I tried various hints (MATERIALIZE, NO_PUSH_PRED, NO_MERGE), again with no success.

I ended up working around the problem by creating a database package with a function that returns a REF CURSOR based on the query, and then used that in a REF CURSOR query in Reports Builder. It might not be a very elegant workaround, but it works. I just wish I had "given up" and tried it sooner -- I might have saved myself some grief.