Script categories

Thursday, 20 October 2011

JMS connection within an Oracle instance


The schema must have the following privileges;
granted from SYS/password@orcl as sysdba to schema

connect sys/password@orcl as sysdba

grant AQ_ADMINISTRATOR_ROLE to jms_schema
/
grant aq_user_role to jms_schema
/
grant JAVA_ADMIN
     ,JAVAUSERPRIV
     ,JAVASYSPRIV
     ,JAVADEBUGPRIV 
to jms_schema
/
grant execute on DBMS_AQIN to jms_schema;
/
grant execute on DBMS_JMS_PLSQL to jms_schema
/

connect as jms_schema/password@orcl

SQL> create or replace and compile java source named BasicAQConnection as
  2  import java.sql.Connection;
  3  import java.sql.Driver;
  4  import java.sql.DriverManager;
  5  import java.sql.PreparedStatement;
  6  import java.sql.ResultSet;
  7  import javax.jms.QueueConnection;
  8  import javax.jms.QueueConnectionFactory;
  9  import javax.jms.QueueSession;
 10  import javax.jms.Session;
 11  import oracle.jms.AQjmsFactory;
 12  import oracle.jdbc.*;
 13  import oracle.jdbc.aq.*;
 14  import oracle.jms.*;
 15 
 16  public class BasicAQConnection
 17    {
 18      public static void main (String args[]) throws Exception
 19        {
 20          Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 21          QueueConnection qc = AQjmsQueueConnectionFactory.createQueueConnection(conn);
 22          QueueSession qs = qc.createQueueSession(true, Session.CLIENT_ACKNOWLEDGE);
 23 
 24          PreparedStatement pStmt = conn.prepareStatement("select count(*) from emp");
 25          ResultSet rSet = pStmt.executeQuery();
 26          rSet.next();
 27          System.out.println("Number of Employees is: " + rSet.getString(1));
 28          rSet.close();
 29 
 30          qc.stop();
 31          conn.close();
 32          qc.close();
 33 
 34        }
 35    }
 36  /


Java created.


SQL>
SQL> create or replace procedure BasicAQConnection as
  2  language java name 'BasicAQConnection.main(java.lang.String[])';
  3  /


Procedure created.


SQL>
SQL> exec dbms_java.set_output(10000);


PL/SQL procedure successfully completed.


SQL>
SQL> select count(*) from emp
  2  /


  COUNT(*)
----------
         1


SQL> set serveroutput on
SQL> begin
  2    BasicAQConnection;
  3  end;
  4  /
Number of Employees is: 1


PL/SQL procedure successfully completed.

No comments:

Post a Comment