Using XML Publisher in eBusiness Suite (Part 1)
As I’ve said before I’m going to concentrate my Blogs on using XML Publisher within Oracle eBusiness Suite. So here goes….
The first thing that we need to do is create ourselves an XML datafile. In eBusiness Suite, XML Publisher is closely linked into the Concurrent Manager, and will accept for processing XML data files written to the Concurrent Manager Output file. Therefore, the simplest way to create an XML file is to use a PL/SQL procedure called from Concurrent Manager. For example:
PROCEDURE writeXML
( p_errbuf OUT VARCHAR2
, p_retcode OUT NUMBER )
IS
BEGIN
fnd_file.put_line(fnd_file.output, ‘<?xml version=”1.0″?>’);
fnd_file.put_line(fnd_file.output, ‘<EMPLOYEES>’);
fnd_file.put_line(fnd_file.output, ‘ <EMPLOYEE>’);
fnd_file.put_line(fnd_file.output, ‘ <FIRST_NAME>Barry</FIRST_NAME>’);
fnd_file.put_line(fnd_file.output, ‘ <LAST_NAME>Goodsell</LAST_NAME>’);
fnd_file.put_line(fnd_file.output, ‘ <DATE_OF_BIRTH>1968-12-13</DATE_OF_BIRTH>’);
fnd_file.put_line(fnd_file.output, ‘ </EMPLOYEE>’);
fnd_file.put_line(fnd_file.output, ‘</EMPLOYEES>’);
END writeXML;
This technique is fine for this small sample, but soon gets very tedious for larger data files. Fortunately, Oracle provides an extension to the main Oracle database called the XML DB. This extension has been available since release 9.2 of the database and is included in every install. Chapter 16 of the XML DB User Guide describes quite a number of differerent ways of generating XML. The full list of options is:
- Using XML SQL functions
- Using DBMS_XMLGEN
- Using SQL functions SYS_XMLGEN and SYS_XMLAGG
- Using XSQL Pages Publishing Framework
- Using XML SQL Utility
I don’t intend to cover this complete list - that’s what the manual is for. The reason I have put the whole list here is to demonstrate that there are many ways to create exactly the same XML data; which method you use is down to which you feel most comfortable with. The key thing common across these methods is a new datatype: XMLType. This is effectively a super-type of CLOB, with a whole load of XML-related functionality added on.
The simplest of the methods mentioned above is to use the DBMS_XMLGEN package:
PROCEDURE writeXML
( p_errbuf OUT VARCHAR2
, p_retcode OUT NUMBER )
IS
l_context dbms_xmlgen.ctxHandle;
l_result XMLType;BEGIN
l_context := dbms_xmlgen.newContext(’SELECT first_name, last_name, date_of_birth FROM employees WHERE person_id = 13′);dbms_xmlgen.setRowSetTag(l_context, ‘EMPLOYEES’);
dbms_xmlgen.setRowTag(l_context, ‘EMPLOYEE’);l_result := dbms_xmlgen.getXMLType(l_context);
fnd_file.put_line(fnd_file.output, l_result.getStringVal());
end writeXML;
Here we don’t have to worry too much about creating the structure of the XML file, DBMS_XMLGEN will handle this for us. If you don’t call the setRowSetTag and setRowTag procedures you will get ROWSET and ROW elements instead.
The eagle-eyed reader may have spotted the problems with this example: fnd_file.put_line has a 32k limit, but more importantly, a VARCHAR2 (as returned by the getStringVal) is limited to 4000 bytes. This is fine for very small XML files, but not very useful in the real world. We need something that can split an XML data stream into smaller chunks and write it to fnd_file.output. The following procedure does just that:
PROCEDURE xml_to_file
( p_xml IN XMLType )
IS
l_clob CLOB;
l_size NUMBER;
l_offset NUMBER;
l_char VARCHAR2(2);BEGIN
SELECT XMLRoot(p_xml.extract(’/*’), VERSION ‘1.0′).getClobVal()
INTO l_clob
FROM sys.dual;l_size := dbms_lob.getlength(l_clob);
IF (NVL(l_size,0) = 0)
THEN
raise_application_error(-20001,’CLOB is NULL’);
END IF;l_offset := 1;
WHILE (l_offset <= l_clob_size)
LOOP
l_char := dbms_lob.substr(l_clob, 1, l_offset);IF (l_char = CHR(10))
THEN
fnd_file.new_line(fnd_file.output, 1);
ELSE
fnd_file.put(fnd_file.output, l_char);
END IF;l_offset := l_offset + 1;
END LOOP;fnd_file.new_line(fnd_file.output, 1);
END xml_to_file;
This procedure converts the XMLType parameter into a CLOB, then streams the characters one at a time to the output, sending a New Line when a CHR(10) is found. The key thing in this procedure is the use of the function XMLRoot. I will cover this function and the other XML SQL functions, such as XMLElement and XMLAttributes, in the next part of this series.