Page History

Activate Java Code in Oracle

1. Java-Source: SOSSchedulerCommand.java

vor der Klassendefinition bitte einfügeninsert before the class definition

  create or replace and compile java source named
    SOSSchedulerCommand
  as

und hinter der letzten Klammer ein Semikolon (

...

and after the last bracket a semicolon ";"

Put the source code to db using SQL Worksheet or similar.

2. Java-QuelleSource: DocumentFactorySchedulerCommandYourSchedulerCommand.java (if you have one)

insert before the class definitionvor der Klassendefinition bitte einfügen

  create or replace and compile java source named
    DocumentFactorySchedulerCommand
  as

and after the last bracket a semicolon ";"

Put the source code to db using SQL Worksheet or similar.und hinter der letzten Klammer ein Semikolon (> Quell-Code mit SQL Worksheet in DB übergeben

3. Wrapper Procedure: Scheduler_Add_Order.sql

   Create a PL/SQL-Prozedur mitwith folgendemthe Inhaltfollowing erzeugencontent:

   create or replace procedure SCHEDULER_ADD_ORDER(host varchar2, port number, letter number, status number, model number, job_chain varchar2)
   as language java
   name 'DocumentFactorySchedulerCommandSOSSchedulerCommand.addOrder(java.lang.String, int, int, int, int, java.lang.String)';

and

 create or replace PROCEDURE SCHEDULER_START_JOB(host varchar2, port number, jobName varchar2, jobParams varchar2)
   as language java  name 'SOSSchedulerCommand.startJob(java.lang.String, int, java.lang.String, java.lang.String)';

Replace SOSSchedulerCommand with YourSchedulerCommand if you have an own Class to call Job Scheduler XML-Command.

4. Add execution rights for 4. Ausführungsberechtigungen für TCP/UDP-Verbindungenconnections
a) Bei Verwendung von if using TCP:

   call dbms_java.grant_permission( 'FACTORY', 'SYS:java.net.SocketPermission', 'localhost', 'resolve' );
   call dbms_java.grant_permission( 'FACTORY', 'SYS:java.net.SocketPermission', '127.0.0.1:4363', 'connect,resolve' );

b) Bei Verwendung von if using UDP (default):

   call dbms_java.grant_permission( 'FACTORY', 'SYS:java.net.SocketPermission', 'localhost:1024-', 'listen,resolve' );
   call dbms_java.grant_permission( 'FACTORY', 'SYS:java.net.SocketPermission', '127.0.0.1:4363', 'accept,resolve' );

A. Aufruftest mittels Testing with SQL Worksheet (or similar):

a) ProzedurProcedure

   /* host, port, letter, status, model, job_queue */
   CALL SCHEDULER_ADD_ORDER( 'localhost', 4363, 4711, 0, 2, 'factory_print' );

b) Debugging

   set serveroutput on size 5000
   call dbms_java.set_output(5000);

B. Zugriff via JDBC-Treiber muss getauscht werden

Sollen DB-Zugriffe erfolgen, dann muss der JDBC-Treiber mit einer Oracle-internen Connection verwendet werden

/** Oracle Version */

Defining the trigger in Oracle (example)

 create or replace TRIGGER "myTrigger" AFTER
 INSERT OR UPDATE ON "Any_Table" FOR EACH ROW
 DECLARE
   jobid     

 /**  Datenbankverbindung herstellen */
   public void db_connect() throws SQLException\{
      try \{
         DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()) binary_integer;
 BEGIN
 
  IF :new."DOWNLOAD" = 2 THEN
 conn =  DriverManager.getConnection("jdbc:default:connection:");  errState = 1;
      \} catch(SQLException e) \{
         throw new SQLException ("DB connect failed" + e);
      \}
   \}

/** Java Version */
/** Datenbankverbindung herstellen */

   public void db_connect() throws SQLException\{
      try \{
         DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
         conn =  DriverManager.getConnection("jdbc:oracle:thin:@sag:1521:sag","sosdev","sosdev");
      \} catch(SQLException e) \{SCHEDULER_START_JOB('localhost', 4363, 'myJobname', '<params><param name="queue" value="' ||  :new."NORMALIZED_NAME" || '"/><param name="directory" value="download_einzeldrucker"/></params>');
  END IF;
 
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     BEGIN
       /* Default: raise an error */
       RAISE_APPLICATION_ERROR( -20001,'TRIGGER:LF_QUEUES_SIGNAL: job could not be started: myJobname' );
     END;
   WHEN OTHERS THEN
     BEGIN
       /* Default: raise an error */
       RAISE_APPLICATION_ERROR(  throw new SQLException ("DB connect failed" + e-20002, 'TRIGGER:LF_QUEUES_SIGNAL: an error occurred: ' || SQLCODE );
     END;
 \}
   \}END;