Bursting Engine Implementation

其實這部份在一個月前就已經完成測試了
不過現在才有空寫上來

基本上
Tim的Blog提供的例子與架構已經很清楚了
但是習慣上會照著做一個不同的報表來用看看...
在做的過程中
才會知道有那些是重要的...要克服的問題..
否則看別人的Code...一切都是那麼理所當然...自己做看看才知道..即使只是模仿

我的測試分兩部份
第一部份是不管Data怎麼出來的部份...假設已有xml了
然後搭配JSP與Control file
看看有沒有什麼問題

如果第一部份沒問題
那麼第二部份的測試...就是把它完整地implement出來
從rdf開始..含report trigger..並掛到Server上跑
讓它完全如Tim的例子一樣運作完美...

(註:由於我用的是XMLP 5.6.2
所以要自己寫JCP..聽說5.6.3就不用了...)

第一部份:
* Control file : burst.xml 放到/tmp/下
* Data file : emp.xml 放到/tmp/下
* Rtf Template : test.rtf 放到/tmp/下
* JCP: BurstingTest.java compile後掛上Server..
主要在JCP中就會依Control file與Data去抓Template並做Bursting
因此測試時跑JCP就好

這邊卡住的問題有
1. JCP Compile問題
一開始抓下來的Java檔...居然在Compile時就有問題...
經過確認..
發現程式中用OADocumentProcessor是有問題的
http://forums.oracle.com/forums/thread.jspa?messageID=1843531
要使用DocumentProcessor

所以import的部份多加了
import oracle.apps.xdo.batch.DocumentProcessor;

程式中的這一段
lfile.writeln("Starting burst ...",1);

OADocumentProcessor dp = new OADocumentProcessor("/tmp/burst.xml","/tmp/emp.xml","/tmp");

lfile.writeln("Bursting initiated ... ",1);

也改為
file.writeln("Starting burst ...",1);

DocumentProcessor dp = new DocumentProcessor("/tmp/burst.xml","/tmp/emp.xml","/tmp");

lfile.writeln("Bursting initiated ... ",1);

2. Data file的問題
我的emp.xml中
用到幾個會有問題的tag...我用了EMP、EMPLOYEE、NAEM這類的....
結果在5.6.2中...不管我資料有幾筆...都只是Burst出第一筆...
這裡卡了很久
後來才知道可能是Bug
改了Tag後...就沒有問題了
http://forums.oracle.com/forums/message.jspa?messageID=1904432#1904432

這邊兩個問題都要感謝Ashish的協助

第二部份:
* Control file : burst.xml 放到/tmp/下
* Report file : BURSTDEMO.rdf 放到Server上
* Rtf Template : test.rtf 放到/tmp/下
* JCP: XMLPReportBurst.java compile後掛上Server..
主要是跑掛上Server的Report: BURSTDEMO..它產生的output就是XML格式
而由於它的After Report Trigger會去Call JCP
因此...JCP會抓得到它XML的output..加上Control file與template
一氣呵成完成後半部的Bursting動作

這部份與第一部份的差異
就在於怎麼去找到rdf的xml output
主要問題在
1. JCP怎麼寫
這邊沒有太大的麻煩..
Tim的例子本身就有這個功能..
只要傳入request id當參數就好...
甚至還能回過頭去改Request畫面上的Request Name...

2. RDF的After Report怎麼把自己的Request id當參數傳給JCP
這裡卡了一陣子
因為~~自己都還沒跑出去..要怎麼知道自己的Request id??
可以看這邊
http://zhxiangxie.blogspot.com/2007/06/how-to-get-this-request-id.html

而完成第一部份測試
加上克服第二部份的問題後
RDF到Bursting的路就走出來了

應該可以用來取代之前用JavaMail與iText寫的那一段功能...

JCP in Oracle EBS

這裡記錄一個Java Concurrent Program
應用在EBS上的範例...

0. Java file path

基本上..在$JAVA_TOP可以看到
oracle/apps/xxx/..的java class
然而..
如果這次要做的是放到oracle/apps/fnd/cp/request下的class
則Java程式讓它放在oracle.apps.fnd.cp.request的package下
而原始檔放到 $FND_TOP/java/cp/request下做compile..

1. Compile Java file

範例中HelloJCP定義在

oracle.apps.fnd.cp.request的package下

因此將HelloJCP移到$FND_TOP/java/cp/request

並在該目錄下執行
javac HelloJCP.java

2. Deploy Java Class

成功Compile後會有一個HelloJCP.class的檔案

移到$JAVA_HOME/oracle/apps/fnd/cp/request

3. Register as an Executable

然而在Application Developer可以建立一個Executable與Java Class相關連

Execution Method訂為Java Concurrent Program

Execution File Path指到oracle.apps.fnd.cp.request

4. Register as a Concurrent

定義Concurrent則和一般的做法相同

5. Execute

掛到適當的權限下進行測試

Output File ...
裡面的內容由OutFile.writeln(String)寫入

LOG File...

裡面的內容由LogFile.writeln(String)寫入

如此又多了一種不使用D2K Report的方法了

但相對要熟悉 java.sql.* 等的應用...

Oracle + Java - PDF Issue using iText

如上一篇
善用Oracle 9i上的Java技術
讓EBS擴充上更有彈性

雖然XML Publisher已經可以產生非常漂亮的PDF格式報表
但是在我還不知道XML Publisher時
測試也可以透過萬能的Java技術來完成PDF報表的製作

當時我所碰到的需求是
將text格式的報表結果
轉換為pdf格式...

透過itext
還可以加頁首頁尾/浮水印/密碼限制
都有詳細的範例碼
雖然沒有XML Publisher方便
但是功能上甚至比XML Publisher更多變...
因為它是Java base PDF creator..

1. 確認版本與loadjava
　
　itext並不像javax一樣...已經在資料庫中了
　但Oracle Client有提供loadjava的機制
因此沒有也沒關係啦...把它放上去就好了~~~

然而itext有1.4.5版..load上去會因為11.5.10.2的jdk版本問題而無法正確compile
　因此後來改用itext1.3的版本

loadjava –user apps/apps@SERVER–resolve –force itext-1.3.jar

2. Java Source and Compile

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "iTextJPDF"
AS import java.awt.Color;
import java.io.FileOutputStream;
import java.io.*;
import com.lowagie.text.Annotation;
import com.lowagie.text.Chunk;
import com.lowagie.text.Document;
import com.lowagie.text.Element;
import com.lowagie.text.ExceptionConverter;
import com.lowagie.text.Font;
import com.lowagie.text.Image;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.Rectangle;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.pdf.PdfContentByte;
import com.lowagie.text.pdf.PdfGState;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfPageEventHelper;
import com.lowagie.text.pdf.PdfTemplate;
import com.lowagie.text.pdf.PdfWriter;
/*
* Modify form iText tutorial
* by zhxiang @ 2006-11-14
*/
public class iTextJPDF extends PdfPageEventHelper {
/** An Image that goes in the header. */
public Image headerImage;
/** The Graphic state */
public PdfGState gstate;
/** A template that will hold the total number of pages. */
public PdfTemplate tpl;
/** The font that will be used. */
public BaseFont helv;

public static int JCvtPDF1 (String file_src,String file_des){
try {
// step 1: creating the document
Document doc = new Document(PageSize.A4, 50, 50, 20, 20);
// step 2: creating the writer
PdfWriter writer = PdfWriter.getInstance(doc, new FileOutputStream(file_des));
// step 3: initialisations + opening the document
writer.setPageEvent(new iTextJPDF());
//-------------
Font NewFont = new Font(Font.COURIER, 9, Font.BOLD);
FileReader fr1 = new FileReader(file_src);
BufferedReader buf_fr1 = new BufferedReader(fr1);
String strReadLine;
//-------Metadata------
doc.addTitle("Invoice and Packing List");
doc.addSubject("Twinhead Invoice and Packing List");
doc.addCreator("Twinhead MIS using iText java tech");
doc.addAuthor("Twinhead MIS");
//-------------
doc.open();
// step 4: adding content
Annotation ann = new Annotation(250f, 400f, 350f, 500f,
"http://www.twinhead.com/");
doc.add(ann);
Image jpg = Image.getInstance("/tmp/logo.jpg");
jpg.scalePercent(90);
jpg.setAnnotation(ann);
doc.add(jpg);
strReadLine = buf_fr1.readLine();
while (strReadLine != null)
{
if (strReadLine.length()==0){
strReadLine=" ";
}
doc.add(new Paragraph(strReadLine,NewFont));
strReadLine = buf_fr1.readLine();
}

buf_fr1.close();
// step 5: closing the document
doc.close();
}
catch ( Exception e ) {
e.printStackTrace();
}
return 1;
}

public void onOpenDocument(PdfWriter writer, Document document) {
try {
// initialization of the header table
headerImage = Image.getInstance("/tmp/logo.jpg");

// initialization of the Graphic State
gstate = new PdfGState();
gstate.setFillOpacity(0.3f);
gstate.setStrokeOpacity(0.3f);
// initialization of the template
tpl = writer.getDirectContent().createTemplate(100, 100);
tpl.setBoundingBox(new Rectangle(-10, -10, 100, 100));
// initialization of the font
helv = BaseFont.createFont("Helvetica", BaseFont.WINANSI, false);
}
catch(Exception e) {
throw new ExceptionConverter(e);
}
}

public void onEndPage(PdfWriter writer, Document document) {
PdfContentByte cb = writer.getDirectContent();
cb.saveState();

// compose the footer
String text = "Page " + writer.getPageNumber() + " of ";
float textSize = helv.getWidthPoint(text, 9);
float textBase = document.bottom() - 10;
cb.beginText();
cb.setFontAndSize(helv, 9);
// for odd pagenumbers, show the footer at the left
//if ((writer.getPageNumber() & 1) == 1) {
cb.setTextMatrix((document.right()-document.left())/2, textBase);
cb.showText(text);
cb.endText();
cb.addTemplate(tpl, (document.right()-document.left())/2 + textSize, textBase);
cb.saveState();
// draw a Rectangle around the page
cb.setColorStroke(Color.black);
cb.setLineWidth(1);
cb.rectangle(20, 20, document.getPageSize().width() - 40, document.getPageSize().height() - 40);
cb.stroke();
cb.restoreState();
// starting on page 3, a watermark with an Image that is made transparent
if (writer.getPageNumber() >= 0) {
cb.setGState(gstate);
cb.setColorFill(Color.pink);
cb.beginText();
cb.setFontAndSize(helv, 48);
cb.showTextAligned(Element.ALIGN_CENTER, "Twinhead International Corp.", document.getPageSize().width() / 2, document.getPageSize().height() / 2, 45);
cb.endText();

cb.restoreState();
}
}


public void onCloseDocument(PdfWriter writer, Document document) {
tpl.beginText();
tpl.setFontAndSize(helv, 9);
tpl.setTextMatrix(0, 0);
tpl.showText("" + (writer.getPageNumber() - 1));
tpl.endText();
}

}
/

itext的使用很簡單(如果看懂的話:p)
主要是五個步驟
step 1: creating the document
step 2: creating the writer
step 3: initialisations + opening the document
step 4: adding content
step 5: closing the document

這段程式中
我是把一整個純文字文件轉為PDF格式
(利用java.io.FileOutputStream的buffer讀寫檔案進去)
並且加頁碼/浮水印與Metadata...
而首頁再加公司logo並超連結到公司首頁

對於CJK的問題
則需要改
helv = BaseFont.createFont("Helvetica", BaseFont.WINANSI, false); 這一句
改為用server上某一個字型檔

3. PL/SQL call Java

CREATE OR REPLACE
FUNCTION tw_cvt_pdf01
( Xfilefrom IN varchar2,
Xfileto IN varchar2)
RETURN number IS
LANGUAGE JAVA
NAME 'iTextJPDF.JCvtPDF1(java.lang.String,java.lang.String) return int';
/

4. Grant Java Permission

主要是我讀純文字報表檔與放pdf結果的位置access權限

exec dbms_java.grant_permission('APPS',
'java.io.FilePermission',
'/report_out_dir/*',
'read');

exec dbms_java.grant_permission('APPS',
'java.io.FilePermission',
'/tmp/*',
'read, write');

5. Application..

如此可以用tw_cvt_pdf01
輕而易舉地轉出PDF檔案...

Oracle + Java - Mail Issue using JavaMail

雖然Oracle的Alert有很強大的Mail功能
但是當需求上要求有多個附檔或多個收件人時
應用上並不是那麼容易

既然Oracle 9i上已經與Java有滿不錯地結合
因此可以善用Java網路上廣大的Free Resources
在mail方面..
Java Mail就是一個容易上手的工具...


1. 確認必要Class

在9i資料庫中查Java Class
可以發現Javax.mail與Javax.activation的package已經在資料庫中了
因此要建置Java Mail並不需要再用loadjava置入額外的class
建置Java Mail主要參考Metalink的Note:120994.1

2. 建立Java Source並Compile

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "SendMail"
AS import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail {
// Zhxiang -test 20060918
// Sender, Recipient, CCRecipient, and BccRecipient are comma-
// separated lists of addresses;
// Body can span multiple CR/LF-separated lines;
// Attachments is a ///-separated list of file names;
public static int Send(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[],
String Attachments) {
// Error status;
int ErrorStatus = 0;
// create some properties and get the default Session;
System.setProperty("mail.mime.charset", "utf-8" ); //zhxiang 20060918
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);
try {
// create a message;
MimeMessage msg = new MimeMessage(session);
// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}
// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,
TheAddresses);
}
// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,
TheAddresses);
}
// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,
TheAddresses);
}
// subject field;
msg.setSubject(Subject);
//msg.setHeader("Subject", MimeUtility.encodeText(Subject, "big5", null)); //zhxiang --20060918 --test
//msg.setSubject(MimeUtility.encodeText(Subject, "big5", null)); //zhxiang --20060918 --test
// create the Multipart to be added the parts to;
Multipart mp = new MimeMultipart();
// create and fill the first message part;
{
MimeBodyPart mbp = new MimeBodyPart();
//mbp.setText(Body);
mbp.setContent(Body,"text/html ;charset=utf-8");//--zhxiang 20060918

// attach the part to the multipart;
mp.addBodyPart(mbp);
}
// attach the files to the message;
if (null != Attachments) {
int StartIndex = 0, PosIndex = 0;
while (-1 != (PosIndex = Attachments.indexOf("///",
StartIndex))) {
// create and fill other message parts;
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex,
PosIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
PosIndex += 3;
StartIndex = PosIndex;
}
// last, or only, attachment file;
if (StartIndex < Attachments.length()) {
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
}
}
// add the Multipart to the message;
msg.setContent(mp);
// set the Date: header;
msg.setSentDate(new Date());
// send the message;
Transport.send(msg);
} catch (MessagingException MsgException) {
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) !=
null)
ErrorMessage[0] = ErrorMessage[0] + "\n" +
TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
}
/

其中中文CJK的問題
以utf-8編碼解決
在這一行　mbp.setContent(Body,"text/html ;charset=utf-8");

3. PL/SQL call Java

CREATE OR REPLACE
PACKAGE BODY sendmailjpkg AS

PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
AttachmentList OUT VARCHAR2) IS
AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
BEGIN
-- boolean short-circuit is used here;
IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
AttachmentList := Attachments(Attachments.FIRST);

-- scan the collection if there is more than one element. If there
-- is not, skip the next part for parsing elements 2 and above. If there
-- is, skip the first element since it has been already processed

IF Attachments.COUNT > 1 THEN

FOR I IN Attachments.NEXT(Attachments.FIRST) .. Attachments.LAST LOOP
AttachmentList := AttachmentList AttachmentSeparator
Attachments(I);
END LOOP;
ELSE
-- whe have to terminate the list with the one element with /// for the java function

AttachmentList := AttachmentList AttachmentSeparator;
END IF;
ELSE
AttachmentList := '';
END IF;
END ParseAttachment;

-- forward declaration;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER;

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail(SMTPServerName,
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;


FUNCTION SendMail(Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail('123.456.789.111', --mail server
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;

-- JSendMail's body is the java function SendMail.Send();
-- thus, no PL/SQL implementation is needed;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING
) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'SendMail.Send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String[],
java.lang.String) return int';
END SendMailJPkg;
/

在這邊我同名了 JSendMail的function
使開發者在不知道mail server ip之下一樣可以send mail
而且當mail server ip改變時
只要回來改這個程式...
不必動到所有的application...


4. Grant Java Permission

-- Allows access to system properties
exec dbms_java.grant_permission('APPS',
'java.util.PropertyPermission',
'*',
'read,write');

-- Allows access to sockets
exec dbms_java.grant_permission('APPS',
'java.net.SocketPermission',
'*',
'connect, resolve');

-- Allows access to local files
exec dbms_java.grant_permission('APPS',
'java.io.FilePermission',
'/dir/dir/attachments/*',
'read, write');

5. Application

如此可以使用 sendmailjpkg.JSendMail(...)來發mail了