Oracle Alerts.

              In my previous article I described how to send notifications from MS SQL server with the help of extended stored procedure "xp_event".
      However as you can imagine, it is not so simple to perform this operation on Oracle. I know that Oracle has a close relationship with Java
      language. Thus I implemented my solution in java source code which is very easy to load into Oracle database. You can call Java  methods
      from PL/SQL after publishing them via PL/SQL wrapper.

              Class OracleAlerter has three methods: send_alert, intToByteArray and swapInt. The last two methods are needed for compatibility with
      applications written in C++ or any other high level languages. Java virtual machine always uses  a big-endian byte order, but applications with
      which you communicate may be not java applications and use a little-endian byte order. In this case you have to  use these methods for converting
      byte order for a record id value.

      

               Oracle provides a command-line utility called loadjava which uploads our compiled Java code into the database. The first parameter 
       is username with password, the second parameter is TNS service name and the last one is our compiled java source code:
     
       loadjava -user scott/tiger@ORACLE_SERVER OracleAlerter.class

               We can verify the load status with a query from USER_OBJECTS:
      
       select object_name, status FROM user_objects where object_type = 'JAVA CLASS';

               It is time to create call specification for send_alert method now, which will map a Java method's parameters and return type to Oracle SQL types:

       create or replace
       procedure send_alert (hostname in varchar2, message in varchar2, login in varchar2, idnum in number)
       as
       language java
       name 'OracleAlerter.send_alert(java.lang.String, java.lang.String, java.lang.String, int)';
       /
       
               It looks like good, but if you try to use send_alert stored procedure somewhere, you will see message that your stored procedure
        hasn't permission required for socket connection. Let’s grant necessary permission in script below:

       begin
       dbms_java.grant_permission('scott','SYS:java.net.SocketPermission', '223.1.1.255:3338', 'connect,resolve');
       dbms_java.grant_permission('scott','SYS:java.net.SocketPermission', 'localhost:1024-', 'resolve,listen');
       end;
       /
      
       Conclusion: We created server side solution for sending alerts from Oracle server. You can use TSQLAlerter C++Builder component or JSQLAlerter
       Java component in your own client side applications.      
      


Сайт создан в системе uCoz