Example: Work Order is defined for publish and yet nothing seems to be happening.
First find a general database trigger, in this case Passport.Wowotsku because it's defined to trigger for any update on table TIDWOTSK.
Trigger Wowotsku fires database procedure Wowotskp.
CREATE OR REPLACE PROCEDURE PASSPORT.wowotskp
(p_inputnew IN VARCHAR2,
p_inputold IN VARCHAR2,
p_inputdelim IN VARCHAR2,
p_inputkey IN VARCHAR2,
p_inputstatusnew IN VARCHAR2,
P_inputstatusold IN VARCHAR2,
P_aXrefCode IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'com.indus.apibo.afjwordr.WOwotsk.processChanges
(java.lang.String, java.lang.String,
java.lang.String, java.lang.String,
java.lang.String, java.lang.String,
java.lang.String)';
Procedure Wowotskp invokes Java program WOwotsk method processChanges with 7 String arguments.
Now it becomes difficult to see what's going on in the Java programs within the database.
An idea is to create a utility to write to a table with useful data.
Use table Tidaudit and before using it define column Audit_Data as data type BLOB from LONG RAW to make life a little easier. If you're OK with wiping out the data in Tidaudit, the following can be used:
DROP TABLE TIDAUDIT;
COMMIT;
/
CREATE TABLE PASSPORT.TIDAUDIT
(
CURSOR_MANAGER CHAR(8) NOT NULL,
FUNCTION_CODE CHAR(8) NOT NULL,
PASSPORT CHAR(8) NOT NULL,
PANEL_ID_7 CHAR(7) NOT NULL,
TIME_STAMP CHAR(26) NOT NULL,
AUDIT_DATA BLOB NOT NULL
)
TABLESPACE tablespace
LOGGING
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE(FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
/
COMMENT ON TABLE PASSPORT.TIDAUDIT IS
'AUDIT AFTER IMAGE DATA'
/
GRANT DELETE ON PASSPORT.TIDAUDIT TO "PUBLIC"
/
GRANT INSERT ON PASSPORT.TIDAUDIT TO "PUBLIC"
/
GRANT SELECT ON PASSPORT.TIDAUDIT TO "PUBLIC"
/
GRANT UPDATE ON PASSPORT.TIDAUDIT TO "PUBLIC"
/
Create a utility program such as XXlogme.java:
/**
* Name: XXlogme.java
* Date: 200910
* Owner: Jeff Chochon
*
* Insert String data into TIDAUDIT for debugging.
*/
/**
* MAINTENANCE HISTORY
*
* Date Id Description
* --------- -- ----------------------------------------------------------
* 200910 Original Version
*
*/
package com.indus.apibo.common;
import java.sql.*;
import com.indus.apibo.*;
public class XXlogme {
// variables that are constant across all instances of this class.
public String m_AuditData;
private String m_CursorManager,
m_FunctionCode,
m_Passport,
m_PanelId7,
m_TimeStamp;
public void XXlogme(String in) {
/* Convert input String to a byte array. */
byte[] inDatabyte = in.getBytes();
/* Convert from a byte array to a char array of hex values double in length. */
char[] inDatachar = encodeHex(inDatabyte);
m_AuditData = String.copyValueOf(inDatachar);
}
/**
* =================insertTidblob Class Method=========================
* Insert row into TIDAUDIT
*/
public void insertTidaudit() throws SQLException {
// Build the timestamp.
m_TimeStamp = XXxutil.buildTimeStamp();
m_CursorManager = "JEFF1";
m_FunctionCode = "JEFF2";
m_PanelId7 = "JEFF3";
m_Passport = "JEFF4";
#sql { INSERT INTO tidaudit
( cursor_manager
,function_code
,passport
,panel_id_7
,time_stamp
,audit_data)
VALUES ( :m_CursorManager
,:m_FunctionCode
,:m_Passport
,:m_PanelId7
,:m_TimeStamp
,:m_AuditData)};
}
/* Apache Jakarta commons codec.
* Used to build output as Hex.
*/
private static final char[] DIGITS = {
'0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', 'a', 'b', 'c', 'd', 'e', 'f'
};
/* Apache Jakarta commons codec.
* Converts an array of bytes into an array of characters representing the
* hexidecimal values of each byte in order.
* The returned array will be double the length of the passed array, as it takes
* two characters to represent any given byte.
*
* @param data a byte[] to convert to Hex characters
* @return a char[] containing hexidecimal characters
*/
public static char[] encodeHex(byte[] data) {
int datalen = data.length;
char[] out = new char[datalen << 1];
// two characters form the hex value.
for (int i=0, j=0; i < datalen; i++) {
out[j++] = DIGITS[ (0xF0 & data[i]) >>> 4 ];
out[j++] = DIGITS[ 0x0F & data[i] ];
}
return out;
}
}
Load the Java program into Oracle and compile it:
loadjava -u UserID/Password@database -v -resolve XXlogme.java
Now it's possible to get useful debugging information by, for example, editing Java program WOwotsk method processChanges with:
...
String workOrderNbr;
String workOrderTask;
String woExtSystemID;
String woExternalKey;
StringTokenizer inputKeyST = new StringTokenizer( inputKey, inputDelim);
// XXlog.insertLogInfo(01,"1", "1");
//Custom begin
XXlogme TMPlogme = new XXlogme();
TMPlogme.XXlogme(inputNew);
TMPlogme.insertTidaudit();
//Custom end
//****************************************************************
// Do not publish if business object publish is not active in passport
// level.
//****************************************************************
if (!XXxpref.publishBO("WO-API-PUBLISH"))
return;
// XXlog.insertLogInfo(02,"2", "2");
...
Recreate Java program WOwotsk.java in the database.
loadjava -u UserID/Password@database -v -resolve WOwotsk.java
Here's SQL to get the info out of Tidaudit:
select time_stamp
,UTL_RAW.CAST_TO_VARCHAR2(audit_data)
from passport.tidaudit
order by time_stamp desc