Error ORA-00928 when querying view through dblink
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Error ORA-00928 when querying view through dblink

  1. #1
    Join Date
    Feb 2001
    Posts
    2

    Error ORA-00928 when querying view through dblink

    Hi guys,

    I have this remote view I'm trying to select from through a database link using this simple SQL:

    SELECT * FROM EMP@OTDB;

    Each time I do I get the following error:

    SQL> select * from emp@OTDB;
    select * from emp@OTDB
    *
    ERROR at line 1:
    ORA-00928: missing SELECT keyword
    ORA-02063: preceding line from OTDB

    Strangely enough, when I run the query locally on OTDB, it works without any problems

    One of the columns on this view is generated using a scalar subquery. I found that if I remove that field and recompiled, the view can be queried locally and remotely without problems.

    I have searched the knowledge base, the NET and documentation unsuccessfully so far.

    Both local and remote instances are running 9.2.0.4.0.


    Any help is greatly apreciated.

    Thanks!

    A.


    The code for the view is:

    CREATE OR REPLACE VIEW EMP(ID_EMPLOYE,
    ID_SCENAR,
    ONDATE,
    FROMDATE)
    AS
    WITH ALL_MODIF_EMPLOYE AS(SELECT EMPID,
    SCENARIOID,
    ONDATE
    FROM EMPLOYEE_ATTRIBUTE
    UNION
    SELECT EMPID,
    SCENARIOID,
    ONDATE
    FROM EMPLOYEE_SCHEDGROUPS
    UNION
    SELECT EMPID,
    SCENARIOID,
    ONDATE
    FROM SL5_EMPLOYEE_ABILITIES)
    SELECT TME.EMPID,
    TME.SCENARIOID,
    TME.ONDATE,
    NVL((SELECT MIN(TME2.ONDATE) -1
    FROM ALL_MODIF_EMPLOYE TME2
    WHERE TME2.EMPID = TME.EMPID
    AND TME2.SCENARIOID = TME.SCENARIOID
    AND TME2.ONDATE > TME.ONDATE), TO_DATE('9999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
    FROM ALL_MODIF_EMPLOYE TME;

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    Below is an 8.1.6 bug, but it might be worth trying on yours as well.
    1) Try disabling query rewrites.
    2) Try setting STAR_TRANSFORMATION_ENABLED = FALSE in your session then run the query.
    If it doesn't help, open a TAR.
    ===============================

    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.
    Last edited by Axr2; 07-26-2004 at 05:37 PM.

  3. #3
    Join Date
    Feb 2001
    Posts
    2
    Thanks for the input Axr2.

    I found it on Metalink as well but it doesn't change the behavior. Read in the 9i patch release that was supposed to be fixed already...

    After torturing the view subquery for 2 hours, I come to the conclusion that 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 bombs out every time.

    I hate it when they introduce a new feature but don't fully test the thing. Makes me feel like i'm on their debugging team...

    Following you sugestion; a tar is on the way as I write this note...

    Thanks again!

    Vuzz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width