ORA-00928 selecting from view over DB link, but works locally

Question

I have a view with a query of the following form which works perfectly when I query it on the local server from any schema:

WITH dates AS (
    SELECT  /*+ materialize */ ...  FROM ( SELECT ... FROM table@link)
    UNION ALL
    SELECT * FROM ( SELECT /*+ materialize */ FROM table@link )
)
SELECT ... FROM (
    SELECT ... FROM (
        SELECT ... FROM (
            SELECT ... FROM (
                SELECT ... FROM 
            )
        ) foo

        LEFT OUTER JOIN (
            SELECT /*+ USE_MERGE(hle dates) */ ... FROM
        )  bar ON conditions
    )
)
UNION  ALL
SELECT ... FROM (
    SELECT ... FROM (
        SELECT ... FROM (
            SELECT ... FROM (
                SELECT ... FROM 
            )
        ) foo

        LEFT OUTER JOIN (
            SELECT /*+ USE_MERGE(hle dates) */ ... FROM
        )  bar ON conditions
    )
)

When I run the query from any remote db link on any other server, e.g. SELECT * from someschema.my_view@db_link, I get:

ORA-00928: missing SELECT keyword
ORA-02063: preceding line from PLLDB
00928. 00000 -  "missing SELECT keyword"
*Cause:    
*Action:
Error at Line: 2 Column: 9

Oracle thinks line 2 is a problem. Here are the first five actual lines:

WITH dates AS (
    -- Get days
    SELECT /*+ materialize */
        row_number() OVER (ORDER BY begin_period DESC) rn,
        'D' AS interval_type,

All other views work perfectly over the DB link (once edited to work around any related Oracle bugs).

Why does this view work perfectly locally but not over a db link?

Look like a Bug with oracle9i(BUG 768478). Please read [here](http://www.dbasupport.com/forums/showthread.php?43937-Error-ORA-00928-when-querying-view-through-dblink) — Maheswaran Ravisankar, Mar 04 '14 at 18:08
@OracleUser: Looks like you are right. Add as an answer and I'll mark it as solved. — Charles Burns, Mar 04 '14 at 19:42
in the patch list [here](http://www.eygle.com/Notes/9208_buglist.htm) I dont see it.. — Maheswaran Ravisankar, Mar 04 '14 at 20:13

Maheswaran Ravisankar · Accepted Answer · 2014-03-04T20:08:53.357

Referring from Here

BUG 768478 The message "ORA-00928: missing SELECT keyword" can occur when Oracle cost-based optimization attempts to rewrite a query that contains a set operator (e.g. UNION, MINUS, or INTERSECT) with a materialized view.

There are three workarounds:
1. Disable query rewrite with an "ALTER {SESSION|SYSTEM} DISABLE QUERY REWRITE" statement.
2. Use a NOREWRITE hint with all SELECT statements referenced by the set operator.
3. Use a REWRITE(mv) hint with all SELECT statements in the set operator to tell the optimizer to explicitly use a materialized view.

Scalar expressions referencing SQL factoring elements ( WITH ... AS ) are not fully supported within view's subqueries. Acessing it locally work fine but remote access via dblinks errors out every time.

ORA-00928 selecting from view over DB link, but works locally

1 Answers1