1

I'm using database change notifications (DCN) in an application I'm writing with ODP.NET. One instance of Oracle 11g 11.2.0.2.0 accepts DCN registrations, and another rejects them for the same query. To my knowledge, the schema of both databases is exactly the same. Both users have change notification privileges. What could be causing one database to reject registration requests that the other accepts?

The query is similar to select * from my_table; and the error message is ORA-29983: Unsupported query for Continuous Query Notification.

Thanks!

Improve this question
2
  • Are you sure the schemas are the same, as far as the connecting user is concerned - that there are no synonyms or views involved in the 'failing' instance? Commented Oct 17, 2012 at 7:17
  • @Alex - good call. The failed instance was using a synonym. Add an answer and I'll accept it. Commented Oct 17, 2012 at 20:06

1 Answer 1

Reset to default
2

As noted in the Continuous Query Notification documentation, there are a number of restrictions on queries that can register for Query Result Change Notification, including referring to synonyms.

From your comment it seems you have a synonym in the failed instance, which explains the error you're seeing.

The (misspelled) error message action also refers to views and synonyms:

ORA-29983: Unsupported query for Continuous Query Notification
Cause: The query cannot be registered for Continuous Query Notification.
Action: The query has some constructs that make it incompatible with Continous Query Notification like synonyms or views. Please check the documentation for complete list.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Link is outdated. I found it here. But I have to question whether information there is correct. I'm trying to change registrations in software that has been in production for more than a decade, before I got involved. It has a registration to a procedure in a user defined package. Documentation states following is not possible: "Calls to user-defined PL/SQL subprograms"
By the way, procedure which is used to create dependency just executes a bunch of select count(*) on a number of data tables. Aggregation functions, such as count are supposedly also not allowed. And I'm hitting a wall with selecting rows from a table... Oracle is a real miracle.
@NikolaRadosavljević - thanks, I've updated to still point to 11.2 docs as that was what the original question was about. The docs still refer to user-defined PL/SQL subprograms in 12.1 and 12.2 and 18. You can't call a procedure from a query though, so I'm not sure what you're doing. Maybe you should ask a new question seeking clarification, if it's relevant to the problem you're seeing.
Thanks. Dependency is to an OracleCommand. CommandType = StoredProcedure, CommandText = "<PackageName>.<ProcedureName". Link to SP Image. It works since 2005. But now I'm worried :D Anyway, I solved my problem of not getting notifications. Incredibly, you must first register dependency, and only then hook into OnChange event. If you attach to OnChange even first, Oracle just discards the event handler without letting you know.
according to docs.oracle.com/cd/B28359_01/appdev.111/b28424/… You can use synonyms in OCN registrations, but not in QRCN registrations. So your answer makes no sense.. but it works.. I can confirm that on 11g you can't use synonyms.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.