I have an insert/update trigger for a Oracle table. Is there a way to send the details of the affected row(all columns) as a message to JMS? I can write a Java Program, 'loadjava' that and call from the trigger. Does this way affect performance?
Is there any native way of achieving this?
There is indeed a native way: use AQ JMS from PL/SQL, see https://docs.oracle.com/database/121/ADQUE/jm_exmpl.htm#ADQUE1600. In short you create an AQ queue with a JMS payload type; then you can post messages with PL/SQL from the trigger. An external Java client can connect to the database and read the messages with JMS.
I don't know how much a call into Java would affect performance, but I try to avoid it. It was a nice idea but it never really caught on, so it remains a fringe case and at least early on there were always issues. PL/SQL on the other hand works.
If you need to send data to another message queue product (tags activemq and mq) you can read the messages in Java and forward them. It adds an extra step, but it is straightforward.
loadjava have many problems and not stable if there is many classes loaded and many business, take a look Calling Java from Oracle, PLSQL causing oracle.aurora.vm.ReadOnlyObjectException
Oracle AQ as i know is not free.
I have implemented the same need after trying many possibilities by creating only 1 class loaded to oracle with loadjava which is called as a procedure by a trigger and have the responsability to call an external java program with all needed parameters and log external process output to a table, as below.
i have encoded text mesage to BASE64 because i used JSON format and some specials caracters can causes problems as a parameters to external java program.
i have used "#*#jms_separator#*#" as a separator in the sent parameter string to parse the content if i need to send many parameters to the external program.
the whole duration of ShellExecutor.shellExec is around 500ms and running since 1 year without any problem.
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Arrays;
import java.util.concurrent.FutureTask;
import sun.misc.BASE64Encoder;
public class ShellExecutor {
static {
System.setProperty("file.encoding", "UTF-8");
}
private static final String INSERT_LOGS_SQL = "INSERT INTO JMS_LOG (TEXT_LOG) VALUES (?) ";
private static final String DEFAULT_CONNECTION = "jdbc:default:connection:";
public static String SQLshellExec(String command) throws Exception {
long start = System.currentTimeMillis();
StringBuffer result = new StringBuffer();
ShellExecutor worker = new ShellExecutor();
try {
worker.shellExec(command, result);
} finally {
result.append("exe duration : " + (System.currentTimeMillis() - start + "\n"));
Connection dbConnection = null;
PreparedStatement logsStatement = null;
try {
dbConnection = DriverManager.getConnection(DEFAULT_CONNECTION);
logsStatement = dbConnection.prepareStatement(INSERT_LOGS_SQL);
logsStatement.clearParameters();
Clob clob = dbConnection.createClob();
clob.setString(1, result.toString());
logsStatement.setClob(1, clob);
logsStatement.executeUpdate();
} finally {
if (logsStatement != null) {
try {
logsStatement.close();
} catch (Exception e) {
}
}
}
}
return result.substring(result.length() - 3090);
}
public void shellExec(String command, StringBuffer result) throws Exception {
Process process = null;
int exit = -10;
try {
InputStream stdout = null;
String[] params = command.split("#*#jms_separator#*#");
BASE64Encoder benc = new BASE64Encoder();
for (int i = 0; i < params.length; i++) {
if (params[i].contains("{") || params[i].contains("}") || params[i].contains("<")
|| params[i].contains("/>")) {
params[i] = benc.encodeBuffer(params[i].getBytes("UTF-8"));
}
}
result.append("Using separator : " + "#*#jms_separator#*#").append("\n")
.append("Calling : " + Arrays.toString(params)).append("\n");
ProcessBuilder pb = new ProcessBuilder(params);
pb.redirectErrorStream(true);
process = pb.start();
stdout = process.getInputStream();
LogStreamReader lsr = new LogStreamReader(stdout, result);
FutureTask<String> stdoutFuture = new FutureTask<String>(lsr, null);
Thread thread = new Thread(stdoutFuture, "LogStreamReader");
thread.start();
try {
exit = process.waitFor();
} catch (InterruptedException e) {
try {
exit = process.waitFor();
} catch (Exception e1) {
}
}
stdoutFuture.get();
result.append("\n").append("exit code :").append(exit).append("\n");
if (exit != 0) {
throw new RuntimeException(result.toString());
}
} catch (Exception e) {
result.append("\nException(").append(e.toString()).append("):").append(e.getCause()).append("\n\n");
e.printStackTrace(System.err);
throw e;
} finally {
if (process != null) {
process.destroy();
}
}
}
}
class LogStreamReader implements Runnable {
private BufferedReader reader;
private StringBuffer result;
public LogStreamReader(InputStream is, StringBuffer result) {
this.reader = new BufferedReader(new InputStreamReader(is));
this.result = result;
}
public void run() {
try {
String line = null;
while ((line = reader.readLine()) != null) {
result.append(line).append("\n");
}
} catch (Exception e) {
result.append("\nException(").append(e.toString()).append("):").append(e.getCause()).append("\n\n");
e.printStackTrace(System.err);
} finally {
try {
reader.close();
} catch (IOException e) {
}
}
}
}
The class of the external Java program packaged as an executable with all needed librairies, a simple JMS sender :
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import javax.jms.Destination;
import javax.jms.MessageProducer;
import javax.jms.Session;
import javax.jms.TextMessage;
import org.apache.commons.codec.binary.Base64;
import org.json.JSONObject;
import progress.message.jclient.ConnectionFactory;
import progress.message.jimpl.Connection;
public class JMSSender {
private static SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss.SSS");
public static void main(String[] args) throws Throwable {
doSend(args[0]);
}
public static void doSend(String text)
throws Throwable {
if (Base64.isBase64(text)) {
text = new String(Base64.decodeBase64(text));
}
String content = "\n\nsending message :" + text;
Connection con = null;
Session session = null;
try {
ConnectionFactory cf = new ConnectionFactory();
session = con.createSession(false, Session.AUTO_ACKNOWLEDGE);
Destination dest = session.createTopic(destination) ;
MessageProducer producer = session.createProducer(dest);
con.start();
JSONObject json = new JSONObject();
json.put("content", text);
json.put("date", sdf.format(new Date()));
TextMessage tm = session.createTextMessage(json.toString());
producer.send(tm);
content += " \n\n" + "sent message :" + json.toString();
} catch (Throwable e) {
content += " \n\n" + e.toString() + " \n\n" + Arrays.toString(e.getStackTrace());
if (e.getCause() != null) {
content += " \n\nCause : " + e.getCause().toString() + " \n\n"
+ Arrays.toString(e.getCause().getStackTrace());
}
e.printStackTrace(System.err);
throw e;
} finally {
write("steps on sending message : " + content);
if (session != null) {
try {
session.commit();
session.close();
} catch (Exception e) {
}
session = null;
}
if (con != null) {
try {
con.stop();
con.close();
} catch (Exception e) {
}
}
}
}
private static void write(String log) {
try {
if (System.out != null) {
System.out.println(log);
}
} catch (Exception e2) {
}
}
}
