-
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;
-
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 04:37 PM.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|