CHANGE NOTIFICATION

[PedroFBPereira]

I have a dotnet application giving error while connecting on an Oracle 11 g bank.
"The Database Change Notification Feature Is Not Supported By This version of the database."
Oracle 11g supports Change Notification, it was even improved in this version.
I have already revised if the user I am using has permissions for Change Notification and run in dbms_change_notification. Everything is OK.
Tips? Suggestions?

[fbifabio]

@Pedrofbperereira,

I never used this feature, but it is likely that you are not able to use it because it should only work in Oracle Enterprise Edition. Run a SELECT in VIV VERSION VISION and see that editing or oracle version you are using, okay?

Note: Many Oracle Database features do not work in Standard or Express Edition.

[PedroFBPereira]

Thanks for the help!
But I do not know if I get it right. Would VSversion be a view? This object does not exist on my bank!


Question: I rode PL-SQL commands that use Change Notification, "Inside" from the bank, logged in as the user that my application .NET uses. It seemed It does not mean that feature exists? See below the commands I used (copied from Oracle's documentation, with small adjustments).

Select all

Rem create a table to record changes to registered tables 
CREATE TABLE nftablechanges(regid number, table_name varchar2(100), 
                            table_operation number); 
  
Rem create a table to record rowids of changed rows. 
CREATE TABLE nfrowchanges(regid number, table_name varchar2(100),  
                          row_id varchar2(30)); 
  
Rem Create a PL/SQL callback handler to process notifications. 
CREATE OR REPLACE PROCEDURE chnf_callback(ntfnds IN SYS.CHNF$_DESC) IS 
   regid           NUMBER; 
   tbname          VARCHAR2(60); 
   event_type      NUMBER; 
   numtables       NUMBER; 
   operation_type  NUMBER; 
   numrows         NUMBER; 
   row_id          VARCHAR2(20); 
 BEGIN 
     regid      := ntfnds.registration_id; 
     numtables  := ntfnds.numtables; 
     event_type := ntfnds.event_type; 
  
  INSERT INTO nfevents VALUES(regid, event_type); 
  IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN 
    FOR i IN 1..numtables LOOP 
      tbname          := ntfnds.table_desc_array(i).table_name; 
      operation_type  := ntfnds.table_desc_array(I). Opflags; 
      INSERT INTO nftablechanges VALUES(regid, tbname, operation_type); 
      /* Send the table name and operation_type to client side listener using UTL_HTTP */ 
      /* If interested in the rowids, obtain them as follows */ 
      IF (bitand(operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN 
        numrows := ntfnds.table_desc_array(i).numrows; 
      ELSE  
        numrows :=0;   /* ROWID INFO NOT AVAILABLE */ 
      END IF; 
       
      /* The body of the loop is not executed when numrows is ZERO */ 
      FOR j IN 1..numrows LOOP 
          Row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id; 
          INSERT INTO nfrowchanges VALUES(regid, tbname, Row_id); 
         /* optionally Send out row_ids to client side listener using UTL_HTTP; */ 
      END LOOP; 
       
    END LOOP; 
  END IF; 
  COMMIT; 
END; 
/  
 
Rem Create a REGISTRATION on the EMPLOYEES TABLE     
DECLARE 
  REGDS      SYS.CHNF$_REG_INFO; 
  regid      NUMBER; 
  family_key     NUMBER; 
  dept_id    NUMBER; 
  qosflags   NUMBER; 
BEGIN 
 qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + 
         DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS; 
REGDS := SYS.CHNF$_REG_INFO ('chnf_callback', qosflags, 0,0,0); 
regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS);  
SELECT fmly_key INTO family_key FROM mi_families WHERE fmly_caption_tx = 'Equipment'; 
DBMS_CHANGE_NOTIFICATION.REG_END; 
END; 
/ 
 
SELECT * FROM nfevents; 
SELECT * FROM nftablechanges; 
select * from nfrowchanges; 
 
select fmly_caption_tx, fmly_desc_tx, fmly_key from mi_families where fmly_caption_tx = 'Equipment'; 
 
UPDATE mi_families SET fmly_desc_tx = 'Equipment Updated' where fmly_caption_tx = 'Equipment'; 
COMMIT; 
 
SELECT * FROM nfevents; 
SELECT * FROM nftablechanges; 
select * from nfrowchanges;

[fbifabio]

V $ Version is a view that exists in qualquer Oracle Database. See connected as SYS.

When an MSG appears containing "... Feature is not supported by this version of the database." [/ I] It's usually because of what I told you.

[PedroFBPereira]

I can not find vsversion ...
I am connected as a user that "theoretically" has admin permissions ...
I do

Select all

select * from sys.vsversion

and tells me that " Table or View does not exist. "

[PedroFBPereira]

I researched the differences between 11g Enterprise and 11g.
I did not even find Change Notification ...
Maybe it's related to "Query Cache"?

[PedroFBPereira]

Using a 10G client with a server 11g could cause this type of problem?

[fbifabio]

@Pedrofbperereira,

You are typing the name of the wrong view. Copy the code below and run on your BD:

Select all

select * from sys.v$version

S

[PedroFBPereira]

TABLE OR VIEW DOES NOT Exists.
I tried with a "common" user and with another that "must" have admin grants.

[fbifabio]

@Pedrofbperereira

Common user does not have access to V $ Version Vision. You must run the query with a user who actually has administrative privileges in the BD. Ask the DBA to run it connected as sys, send you the result and post it here for us.

[dr_gori]

Here you have other ways to see version: http://en.glufke.net/oracle/viewtopic.php?t=248
In this table you can see the fetures:

Select all

select * from DBA_FEATURE_USAGE_STATISTICS

(but I did not find anything about Change Notification there)

Did you get to do Grant?

Select all

grant change notification to USUARIO

http://docs.oracle.com/cd/E11882_01/jav ... JJDBC28815

Here is a step by step: http://www.oracle.com/webfolder/technet ... hg_otn.htm

[PedroFBPereira]

The Grant was made ... in the first chapters of the novel!
Thanks for step-by-step!

[danilo_zg]

Peter, the syntax you posted is incorrect

Select all

select * from sys.v$version

with $ dollar.
What posts is as

Select all

select * from sys.vsversion 

with the letter S.

Check that this was the misconception.

[PedroFBPereira]

Danilo, thank you very much for the support!
I tried with "this", I tried with "dollar", nothing worked.
until suddenly started working!
Needless to type "SYS.V $ VERSION".
I can only imagine that DBA admin realized that I had given me the wrong permissions, it was there and corrected!

Then the question that is still tormenting me is
"Oracle 11g Standard supports Change Notification?"
"Is this default or do you need any specific license?"
"Where, in Oracle documentation, this is stated?"

I stay here in the hope ...

[fbifabio]

@Pedrofbperereira

Now that we know that your BD is Standard Edition is easier to help you.

You already know that Feature Database Change Notification (dbms_change_notification) does not work on Standard Edition, because the error message that returned to you is clear and says exactly that the resource is not supported in the your version of BD. This is common, there are many features that do not work in Oracle Standard Edition (see complete list in http://www.oracle.com/us/products/datab ... index.html Refer to the links below, I could not identify any information about this feature require additional licensing in the Enterprise Edition version, so I suggest that You contact some Oracle consultant LMS (eg arnaldo.tramontano@oracle.com) and ask this question. After you find out, please register here and share the knowledge ... just like you, others will need this Reply!

- Database Change Notification: http://docs.oracle.com/cd/E11882_01/jav ... JJDBC28815
- Options and packs: http://docs.oracle.com/cd/B28359_01/lic ... m#DBLIC164
[] S

[PedroFBPereira]

Guys, thank you very much for the help!
I think I managed to solve the problem.
The cause was that my .NET application uses ODP.NET to connect to Oracle.
So I needed to update ODP.NET to version 11G, otherwise would not be able to connect to an Oracle 11G!

But when I installed the new version of ODP.NET, another problem arose.
It seems that the tnsnames.ora file is "visible" to my application when I am using the machine "locally", that is, using it directly or through remote desktop.
When I try to activate the application through the network, or using Citrix, apparently the TNSNAMES.ORA is not found, because the aliases are not solved!

Any tips on how to solve this?
Thanks!

[PedroFBPereira]

Everything's solved! It was just restarting IIS after updating ODP.NET! You're bombing now!