Oracle XML Publisher

How to print data across the columns?

2007-10-26T11:16:00.000-07:00

Let's say we have the following data,

<rowset>
<row>
<data>A</data>
</row>
<row>
<data>B</data>
</row>
...
...
<row>
<data>E</data>
</row>
</rowset>

And we want to print them in the following way:

A B
C D
E

- With using of MS Word basic method (Format->columns), it will print the data first down and then across. Like,
A D
B E
C

- I found something workaround to resolve this issue.
1. Create one table with two columns.
2. Create another table with one row and column in column1 and second table in column2

See below:

Output:

Download new version of BIP Enterprise 10.1.3.3

2007-08-16T12:59:00.000-07:00

Oracle has released new version of BIP Enterprise, standalone, 10.1.3.3.

Get it here,
Windows
Linux

Easiest way to insert image into oracle database

2007-08-10T10:45:00.000-07:00

I have had requirement to store some signature files of USERs in oracle database. So we can display that signature on Letter when we generate reports from BI Publisher. I tried two methods and both worked fine as expected.

Method 1 (Standard way):

First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'

Now copy all the files to that directory.

Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');

Create Procedure to update table with signature file.

CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN

DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;

Now run it with sql plus
SQL> exec user_signature('01')

or in TOAD (sql editor)

begin
user_signature('01');
end;

Method 2 (Easiest way): Need TOAD !!!

Select table from schema browser and click on data tab.

Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.

Once file is loaded you can see data length is displayed as 4664

(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)

Ohhh, It's done !!! now what ?
If you want to copy file on your local hard disk which is stored in oracle database then just do double-click on BLOB data and click on "Save to File" icon at top-left side of window (second icon).

Passing Report name as Report Title

2007-08-03T12:29:00.000-07:00

Found something interesting question here posted by Haresh.

Question :Using Desktop Publisher for Word and a standalone BIP Enterprise version, is it possible to pass the report name to the rtf template so that it could be used as the report title?

This feature would be useful when there are multiple reports (different names and slightly different queries/criteria) that use the same templates. We could then dynamically change the report title based on the report name.

Solution

Do not sure if there is any simple way to print report name in rtf template.
There is a work around. Follow below steps...

1>create parameter called Report_Name in every report
2>put default value for that parameter as report name (Title)
3>select parameter type as Hidden

Now in rtf template.

<?param@begin:Report_Name?>
<?$Report_Name?>

Done......

ERD

2007-07-24T07:48:00.000-07:00

Useful Entity Relationship Diagram which indicates relationship among OM, AR and Shipping modules.

Design reports to print on pre-printed stationary

2007-07-16T12:47:00.001-07:00

Guys,
Here you can find good article about how to design report which will print on pre-printed stationary in EBS.
http://oracle.anilpassi.com/xml-publisher-developing-reports-printed-on-pre-printed-stationary-2.html

Formatting char/number to US style phone number

2007-06-29T10:15:00.000-07:00

Someone asked this question on Oracle BI publisher forum and i just wondered whether it is possible with any formatting XSL function or not. I tried with format-number function but finally i got with extended XSL functions for use in RTF template

Case:
Suppose we have xml data like
1234567890
Now we want to display it as (123)456-7890 (US style Ph #)

Solution:
<?xdofx:’(‘||substr(Phone_No,1,3)||’) ’||substr(Phone_No,4,3)||’-‘||substr(Phone_No,7,4)?>

Download BIP 10.1.3.2 Standalone

2007-06-07T13:05:00.000-07:00

Get full informations about new version of BIP at Tim Dexter's blog.

http://blogs.oracle.com/xmlpublisher/2007/04/16#a243

Process XML data and generate a PDF and Store PDF file in same input XML data

2007-06-05T12:53:00.000-07:00

Use Case:
Objective is to process XML data and generate a PDF for the same using XML Publisher API. After the PDF is generated the PDF documented is to be converted into a Base64 encoded string and appended back to the input XML.

Solution:
Following are the steps involved to achieve the above use case
1) Create a RTF template file. This file is used for generating the PDF using Oracle BI Publisher APIs.
2) Generate Java classes for the input XML schema using Apache XML Beans. This is done in order to facilitate accessing the XML file using Java. Any other technology can be used for the same
3) Retrieve the List of files to be processed from a folder. Alternatively the input XML can be picked up from a database, JMS / MQ queue or any alternative data source.
4) Parse the input XML file using the generated XML Bean classes.
5) Extract the application id from the input xml. This is used as the filename for the generated XML file.
6) Create an RTFProcessor to generate a XSL file, which will be used by the FOProcessor to convert the input XML data into a PDF document. Both of these classes are part of Oracle BI Publisher API’s. The FOProcessor class is the front¬end class to use the FO Processing engine. The RTFProcessor class is the front¬end class to use RTF Processing engine. Both of these classes are used to generate the PDF using a RTF template file.
7) Retrieve the bytes for the generated PDF file using a FileInputStream object, this is used to convert the PDF file into a Base64 encoded string.
8) Set the Base64 encoded string, along with other information to the in memory XML bean.
9) Finally convert the XML Bean into an xml string and sore it onto a file system object. Alternatively the XML can be stored in a database or sent to a JMS / MQ queue or any alternative data source.

Sample Code:
public class XMLProcess {
// Variable declarations
static String xmlInputFolder = "D://XMLProcess//xmlInput//";
static String xmlOutputFolder = "D://XMLProcess//xmlOutput//";
static String pdfOutputFolder = "D://XMLProcess//pdfOutput//";
static String tmpFolder = "D://XMLProcess//tmp//";

static String templateFileName = "report.rtf";
static String templateFileLocation = tmpFolder + templateFileName;

static String xslFileName = tmpFolder + "tmp.xsl";
static String pdfFileName = "";
static String base64PDF = "";

public XMLProcess() {
}
public static void main(String[] args) {
try {

System.out.println("[XML Process] : Processing started"); System.out.println("[XML Process] : Generating temporary XSL");
// *******************************
// GENERATE XSL FOR PDF PROCESSING
// *******************************
RTFProcessor rtfProcessor = new RTFProcessor(templateFileLocation); rtfProcessor.setOutput(xslFileName); // output file rtfProcessor.process();
System.out.println("[XML Process] : Retrieving files to be processed");
// **********************************
// RETREIVE FILENAMES TO BE PROCESSED
// **********************************

File files = new File(xmlInputFolder);
String fileNames[] = files.list();

// ***************************
// PROCESS EACH INPUT XML FILE
// ***************************

for (int i = 0; i <>
System.out.println("[XML Process] : Processing " + fileNames[i] + " ¬Start");

// *******************************
// PARSE INPUT XML USING XML BEANS
// *******************************

AAUTransDocument doc = AAUTransDocument.Factory.parse(new File(xmlInputFolder + fileNames[i]));
pdfFileName = doc.getAAUTrans().getAAUEventData().getADIObject().getApplId().toString() + ".pdf";
// ************
// GENERATE PDF
// ************

System.out.println("[XML Process] : Generating PDF file " +
pdfFileName);

FOProcessor processor = new FOProcessor();
processor.setData(xmlInputFolder +

fileNames[i]); // set XML input file
processor.setTemplate(xslFileName); // set XSL input file
processor.setOutput(pdfOutputFolder +

pdfFileName); //set output file
processor.setOutputFormat(FOProcessor.FORMAT_PDF);

processor.generate();
// ************************
// GENERATE BASE64 ENCODING
// ************************

FileInputStream fileInputStream =
new FileInputStream(new File(pdfOutputFolder +

pdfFileName));
byte bytes[] = new byte[fileInputStream.available()];
fileInputStream.read(bytes);

// ****************************
// SET THE DOCUMENT PROPERTIES
// ****************************

doc.getAAUTrans().getAAUEventData().addNewDOCObject().setFileName(pdfFileName); doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentType("PDF");
doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentType("CORRESPONDENCE")
; doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentAuthor("DW"); doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentInfo("");

doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentStatus("PROCESSED"); doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentYear("2007"); doc.getAAUTrans().getAAUEventData().getDOCObject().setDocumentData(bytes);
// **************************
// WRITE MODFIIED XML TO FILE
// **************************

System.out.println("[XML Process] : Writing output xml file " + fileNames[i]);
FileWriter fileWriter =
new FileWriter(new File(xmlOutputFolder + fileNames[i])); fileWriter.write(doc.toString()); fileWriter.close();
System.out.println("[XML Process] : Processing " +
fileNames[i] + " ¬Finished");
}

System.out.println("[XML Process] : Processing finished");
} catch (Exception e) { e.printStackTrace(); } } }

Multiple selection for Parameter values in XML Publisher

2007-05-11T14:39:00.000-07:00

With working BIP Enterprise 10.1.3.2, we can't use all three options (Single, Multiple and All) with using of Oracle provided options in BIP. You can use only two options of them at runtime.

Symptoms:

****CASE 1 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (:myParameter)

All does not work
Single Selection is working
Multiple Selection is working

***********CASE 2 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (nvl(:myParameter, DEPARTMENT_NAME))

All is working now
Singe selection is working
Multiple selection is not working any more (ORA-00909: Ungültige Anzahl von Argumenten / illegal amount of arguments)..

***********CASE 3 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS where DEPARTMENT_NAME IN (:myParameter) or :myParameter IS NULL

All is working
Singe selection is working
Multiple selection is not working (ORA-00920: Ungültiger relationaler Operator / illegal relational operator)

Solution:

select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS
where ((coalesce(null, :Parameter) is null) or (DEPARTMENT_NAME in (:Parameter)))