VijayaLakshmi Oracle Apps Zone

Wednesday, 4 February 2015

Difference between BULKCOLLECT and FORALL in Oracle

Bulk collect: is a CLAUSE. is used to fetch the records from the cursor.
Forall: is a STATEMENT. is used to do dml operation of fetched records.
The body of the FORALL statement is a single DML statement -- an INSERT, UPDATE, or DELETE.

BULK COLLECT is:

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT
INTO, FETCH INTO, and RETURNING INTO clauses.

Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."
and FORALL is defined as

FORALL is:

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an
iteration scheme, it is not a FOR loop.

Its syntax follows:

FORALL index IN lower_bound..upper_bound
   sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that
references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index
number in the range."

So there you go. Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance.
Hopefully, if you've not come across these areas before.
----------------------------------------------------------------------------

BULK COLLECT Syntax & Example:

FETCH BULK COLLECT BULK COLLECT INTO
LIMIT ;
set timing on
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
BEGIN
   FOR cur_rec IN a_cur LOOP
     NULL;
   END LOOP;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
   OPEN a_cur;
   LOOP
     FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
     EXIT WHEN a_cur%NOTFOUND;
   END LOOP;
   CLOSE a_cur;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
   OPEN a_cur;
   LOOP
     FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
     EXIT WHEN a_cur%NOTFOUND;
   END LOOP;
   CLOSE a_cur;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
   OPEN a_cur;
   LOOP
     FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
     EXIT WHEN a_cur%NOTFOUND;
   END LOOP;
   CLOSE a_cur;
END;
/
-- try with a LIMIT clause of 2500, 5000, and 10000. What do you see?
----------------------------------------------------------------------------

FORALL Syntax & Example:

FORALL IN ..

SAVE EXCEPTIONS;
FORALL IN INDICES OF
[BETWEEN AND ]

SAVE EXCEPTIONS;
FORALL IN INDICES OF
VALUES OF

SAVE EXCEPTIONS;

FOR INSERT
===========

CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;
DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;
TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
   OPEN s_cur;
   LOOP
     FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
     FORALL i IN 1..s_array.COUNT
     INSERT INTO servers2 VALUES s_array(i);
     EXIT WHEN s_cur%NOTFOUND;
   END LOOP;
   CLOSE s_cur;
   COMMIT;
END;
/

FOR UPDATE
===========

SELECTDISTINCT srvr_id
FROM servers2
ORDER BY 1;
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
   d_array(1) := 608;
   d_array(2) := 610;
   d_array(3) := 612;
   FORALL i IN d_array.FIRST .. d_array.LAST
   UPDATE servers2
   SET srvr_id = 0
   WHERE srvr_id = d_array(i);
   COMMIT;
END;
/
SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;

FOR DELETE
============

set serveroutput on
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
   d_array(1) := 614;
   d_array(2) := 615;
   d_array(3) := 616;
   FORALL i IN d_array.FIRST .. d_array.LAST
   DELETE servers2
   WHERE srvr_id = d_array(i);
   COMMIT;
   FOR i IN d_array.FIRST .. d_array.LAST LOOP
     dbms_output.put_line('Iteration #' || i || ' deleted ' ||
     SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
/
SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);

====================================================

In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.

BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

You may be wondering what very quickly might mean—how much impact do these features really have? Actual results will vary, depending on the version of Oracle Database you are running and the specifics of your application logic. You can download and run the script to compare the performance of row-by-row inserting with FORALL inserting. On my laptop running Oracle Database 11g Release 2, it took 4.94 seconds to insert 100,000 rows, one at a time. With FORALL, those 100,000 were inserted in 0.12 seconds. Wow!

=============================================================

BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP

After more and more reads about BULK COLLECT and FORALL and their performance improvements I decided to have a closer look on it by myself to see how powerful they really are. So I built a little test-case which inserts all entries from the all_object view into another table. The inserts happens on three different ways:
First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is.
The second way is a simple FOR – IN LOOP with the insert of the cursor variables.
And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see.

So the other table looks like this (three columns are enough for this tests)

SQL> create table temp (owner varchar2(30), name varchar2(30), type varchar2(19));

Table created.

And the three diffrent procedures looks like this

CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY

 IS

 l_sOwner VARCHAR2(30);

 l_sName VARCHAR2(30);

 l_sType VARCHAR2(19);

 CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;

 BEGIN

 dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);

 OPEN cur;

 dbms_output.put_line('Before LOOP: ' || systimestamp);

 LOOP

 FETCH cur INTO l_sOwner, l_sName, l_sType;

 IF cur%NOTFOUND THEN

 EXIT;

 END IF;

 INSERT INTO temp values (l_sOwner, l_sName, l_sType);

 END LOOP;

 CLOSE cur;

 dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);

 COMMIT;

 END;

 /

CREATE OR REPLACE PROCEDURE CURSOR_FOR_QUERY

 IS

 BEGIN

 dbms_output.put_line('Before CURSOR: ' || systimestamp);

 FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP

 INSERT INTO temp values (cur.owner, cur.name, cur.type);

 END LOOP;

 dbms_output.put_line('After CURSOR: ' || systimestamp);

 COMMIT;

 END;

 /

CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY

 IS

 TYPE sOwner IS TABLE OF VARCHAR2(30);

 TYPE sName IS TABLE OF VARCHAR2(30);

 TYPE sType IS TABLE OF VARCHAR2(19);

 l_sOwner sOwner;

 l_sName sName;

 l_sType sType;

 BEGIN

 dbms_output.put_line('Before Bulk Collect: ' || systimestamp);

 SELECT owner, object_name, object_type

 BULK COLLECT INTO l_sOwner, l_sName, l_sType

 FROM all_objects;

 dbms_output.put_line('After Bulk Collect: ' || systimestamp);

 --

 FORALL indx IN l_sName.FIRST..l_sName.LAST

 INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx));

 --

 dbms_output.put_line('After FORALL: ' || systimestamp);

 COMMIT;

 END;

 /

Ok, then I bounced the database to get no buffers, caching, etc. on it.

So the first execute

SQL> exec cursor_for_open_query
Before CURSOR OPEN: 27-SEP-07 10.56.30.699401000 AM +02:00
Before LOOP: 27-SEP-07 10.56.30.922366000 AM +02:00
After CURSOR CLOSE: 27-SEP-07 10.57.07.699791000 AM +02:00

Only look at the seconds it took 37 seconds and nearly nothing for opening the cursor! But how much rows were inserted?

SQL> select count(*) from temp;

COUNT(*)
———-
49424

Truncate the table (truncate to free the extends!) and bounce the database again and now the second run

SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 10.59.47.848249000 AM +02:00
After CURSOR: 27-SEP-07 11.00.09.072525000 AM +02:00

The whole loop took 22 seconds, well this looks already better. Well, also all rows inserted?

SQL> select count(*) from temp;

COUNT(*)
———-
49424

But now (after truncate and bouncing) the bulk collect run

SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.01.33.553224000 AM +02:00
After Bulk Collect: 27-SEP-07 11.01.41.874054000 AM +02:00
After FORALL: 27-SEP-07 11.01.42.065753000 AM +02:00

Look at this, for bulking all the lines into the collection took just 8 seconds (for 49 424 rows) and the inserts just 1 second! Unbelievable, together we did everything in 9 seconds where the other ways took over 20 seconds!

Well now lets try to first execute the bulk load then truncate the table again but not bouncing the database so that the buffers and caches a still filled

SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.02.31.257498000 AM +02:00
After Bulk Collect: 27-SEP-07 11.02.41.614205000 AM +02:00
After FORALL: 27-SEP-07 11.02.41.818092000 AM +02:00

PL/SQL procedure successfully completed.

SQL> select count(*) from temp;

COUNT(*)
———-
49423

SQL> truncate table temp;

Table truncated.

SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 11.04.04.960254000 AM +02:00
After CURSOR: 27-SEP-07 11.04.25.749038000 AM +02:00

Ok so now we need 10 seconds for the run with the bulk but we sill need 21 seconds for the cursor! So not really a improvement with the cache and so on. Ok final test on a big system with over 268 thousand rows

Before Bulk Collect: 27-SEP-07 11.24.17.034732000 AM +02:00
After Bulk Collect: 27-SEP-07 11.24.25.111020000 AM +02:00
After FORALL: 27-SEP-07 11.24.26.129826000 AM +02:00
PL/SQL procedure successfully completed.

COUNT(*)
———-
267985

Table truncated.

Before CURSOR: 27-SEP-07 11.24.29.629354000 AM +02:00
After CURSOR: 27-SEP-07 11.25.02.244549000 AM +02:00
PL/SQL procedure successfully completed.

COUNT(*)
———-
268056
And again, bulking took 8 seconds and the inserts just 1 second! But the run with the cursor took 33 seconds!

So this was just a short test but it definitely shows that BULK COLLECT and FORALL are much faster than cursors within the FOR loop! Only disadvantage of FORALL as you maybe already guess if you looked at the code: You can just perform one DML statement, there is no “FORALL END” clause! But anyway also bulking is a very high-performance functionality of Oracle! So if you have to run throw data collections then use BULK COLLECT!

How to handle BULKCOLLECT Exceptions in PL/SQL

Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing. Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute. This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – Holds the iteration (not the subscript) of the original FORALL statement that raised the exception. In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE – Holds the exceptions error code.

The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised. The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.

save_exceptions.sql

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

l_tab t_tab := t_tab();
l_error_count NUMBER;

ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
END LOOP;

-- Cause a failure.
l_tab(50).id := NULL;
l_tab(51).id := NULL;

EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

-- Perform a bulk operation.
BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF

The FORALL statement includes the SAVE EXCEPTIONS clause, and the exception handler displays the number of exceptions and their associated error messages. The output from the save_exceptions.sql script is listed below.

SQL> @save_exceptions.sql

Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
2 FROM exception_test;

COUNT(*)
----------
98

1 row selected.

SQL> SET ECHO OFF

As expected the test table contains 98 of the 100 records, and the associated error message has been displayed by looping through the SQL%BULK_EXCEPTION collection.

If the SAVE EXCEPTIONS clause is omitted from the FORALL statement, execution of the bulk operation stops at the first exception and the SQL%BULK_EXCEPTIONS collection contains a single record. The no_save_exceptions.sql script demonstrates this behavior.

no_save_exceptions.sql

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

l_tab t_tab := t_tab();
l_error_count NUMBER;

ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -01400);
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
END LOOP;

-- Cause a failure.
l_tab(50).id := NULL;
l_tab(51).id := NULL;

EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

-- Perform a bulk operation.
BEGIN
    FORALL i IN l_tab.first .. l_tab.last
      INSERT INTO exception_test
      VALUES l_tab(i);
EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
     END LOOP;
END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM exception_test;
SET ECHO OFF

Notice that in addition to the SAVE EXCEPTIONS clause being removed, the no_save_exceptions.sql script now traps a different error number. The output from this script is listed below.

SQL> @no_save_exceptions.sql

Number of failures: 1
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into
("TIM_HALL"."EXCEPTION_TEST"."ID")

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
2 FROM exception_test;

COUNT(*)
----------
49

1 row selected.

SQL> SET ECHO OFF

As expected there is only a single error in the SQL%BULK_EXCEPTIONS collection, and there are only 49 records in the test table as the operation has rolled back to the preceding implicit savepoint.

As shown from previous examples, a move from conventional operations to bulk operations will require a revision of your current exception handling or the desired results may not appear.

The use of bulk operations with dynamic SQL is explained in the next section.

======================================================

SQL%BULK_ROWCOUNT

The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in theSQL%BULK_ROWCOUNT cursor attribute

How to create a Multilingual reports in Oracle XML Publisher

There are two options for adding translated templates to your report definition:

-->Create a separate RTF template that is translated (a localized template)

This option is useful if the translated template requires a different layout for each language.

-->Generate an XLIFF file from the original template (at runtime the original template is applied for the layout and the XLIFF file is applied for the translation)

If the layout of the report is same for all languages and if we only require translation of the text strings of the template layout, use the XLIFF option.

1. Localized Template Approach

-->Enable the languages that we need the translation for(System Administrator> Install >

Languages)

-->Create different templates for languages with different layouts. E.g. one for English and one for

Arabic.

-->Register the template in Oracle with the same name and short name as the concurrent

program. ( XML Publisher Administrator>Templates)

-->Click on Add File and then browse to file location.

-->Select the file and corresponding language.

-->We need to add as many files as there are to be translations.

2. XLIFF File Approach

-->XLIFF (XML Localization Interchange File Format) : format for exchanging localization data.

-->XMLbased format that enables translators to concentrate on the text to be translated.

-->We use this option when we want to use the same layout and apply specific translation.

Creating an XLIFF file from an RTF template

-->Open the template in Microsoft Word with the Template Builder for Word

-->Select Tools > Translations > Extract Text.

-->BI Publisher extracts the translatable strings from the template and exports them to

an XLIFF (.xlf) file.

-->This XLIFF file can then be sent to a translation provider, or using a text editor, we

can enter the translation for each string.

Sample translations list for XLIFF file

-->A separate XLIFF file is created for every language translation we need.

-->If we are going to translate the base source tags into 2 languages, Say French and

Spanish. So that when we run the report for French and Spanish the report will be

translated as per the language.

-->The assumption is that these language packs are already installed in Oracle.

-->Following is a sample list of all the translations of the tags is shown in the below table.

Uploading a translation in Oracle Apps E – Business Suite

-->Go to the responsibility XML Publisher Administrator > Home > Templates

-->Create Template and Select the Upload Translations button.

-->From the Upload Translations page, browse the translated file in your local file system

and save the changes.

-->Click the Enable button to enable a translation. Only enabled translations are available

to the Concurrent Manager. Both complete and incomplete translations can be enabled.

-->To download a translation file, select its Export translation icon to download the XLIFF file for editing.

XML Publisher tables in oracle apps

XMLP tables:

Table Name	Description
XDO_CONFIG_PROPERTIES_B	Stores the XML Publisher Administration configuration properties that are accessible from the OA Framework interface.
XDO_CONFIG_PROPERTIES_TL	Translation table for XDO_CONFIG_PROPERTIES_B.
XDO_CONFIG_VALUES	Stores the values assigned to the property in Administration Configuration Data
XDO_CURRENCY_FORMATS	Stores the format masks for various currencies. A collection of these formats forms a currency format set.
XDO_CURRENCY_FORMAT_SETS_B	Stores the Currency Format Sets
XDO_CURRENCY_FORMAT_SETS_TL	Stores the Currency Format Sets
XDO_DS_DEFINITIONS_B	Stores data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and these information are stored in XDO_DS_ELEMENTS_B
XDO_DS_DEFINITIONS_TL	Translation table for XDO_DS_DEFINITIONS_B
XDO_FONT_MAPPINGS	Stores the mappings from a base font to a target Truetype or Type 1 font. A collection of these mappings forms a font mapping set
XDO_FONT_MAPPING_SETS_B	Stores the header information for a font mapping set, which is a collection of font mappings
XDO_FONT_MAPPING_SETS_TL	Translation table for XDO_FONT_MAPPING_SETS_B
XDO_LOBS	Stores Template(RTF File), XML File, XML Schema File, locale(langauge and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
XDO_TEMPLATES_B	Stores template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information are stored in the XDO_TEMPLATE_FILES.
XDO_TEMPLATES_TL	Translatable table for XDO_TEMPLATES_B
XDO_TEMPLATE_FIELDS	Stores information of the fields of template file. Each field belongs to one of physical template files
XDO_TRANS_UNITS	Stores the header information regarding each segment of translatable text in layout templates
XDO_TRANS_UNIT_PROPS	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translations
XDO_TRANS_UNIT_VALUES	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translationsd

Sub Templates in Oracle XML BI Publisher

There May be scenarios that where and when we have some part of XML data needs to be represent in fixed format and remaining XML data needs to be represent in multiple formats based on some conditions (let us say for each country specific remaining data XML data needs to be processed) in that case once after registering the concurrent program (either rdf or Data template type) we can create one Default data template for the concurrent program and other multiple sub templates for each country specific .

Create one Default or main Data template for concurrent program along with the multiple sub templates for the same data definition in oracle apps.

We can create sub templates in the oracle apps by selecting sub template option as "Yes" while creating template using Xml Publisher Administrator responsibility.

While creating the main or Default Data template RTF file first we have to import all the sub templates that are going to be called in this as below.

<?import:xdo://XXPIC.TEST_FRT.en.us?>

xxpic ->Application Shortname
TEST_FRT-> Subtemplate Code.

After Importing all required sub templates next we have to design the layout that is common in all cases.

Now based on conditions we have to call different sub templates.

<?IF:SUBTEMPLATE='TEST_FRT'?> <?call-template:TEST?> <?END IF?>
<?IF:SUBTEMPLATE='TEST_FRT2'?><?call-template:TEST1?><?END IF?>

Here TEST and TEST 1 are Sub template Names

This concludes the creation of the Main RTF template.

Now in each Sub template we have to design the layout between below tags.

<?TEMPLATE:TEST?>
----Layout design---
<?END TEMPLATE?>

TEST : Sub template Name

Using Barcodes in XML Publisher Repots

Code 128 barcodes are used extensively within various industries. Since they are so common, chances are you will be asked to develop reports containing this barcode at some stage (for example, within invoice statements).

This article explains in detail how to display Code 128 barcodes within XML Publisher reports. It starts with some background information on Code 128 barcodes, and then explains each step necessary to use Code 128 barcodes in XML Publisher:

Finding an appropriate Code 128 font
Installing the font on the local machine
Installing the font on the App Server/Process Scheduler
Using Code 128 font within XML Publisher RTF templates
Generating XML Publisher reports containing Code 128 barcodes (by using the barcode encoding function)
Testing barcodes

This article assumes you have a decent understanding and experience with XML Publisher.

Background of Code 128 barcodes

Code 128 barcodes are used for alphanumeric or numeric-only characters. The barcode itself consists of 6 sections:

Quiet Zone
Start Character
Encoded Data
Check Character
Stop Character
Quiet Zone

The Check Character is calculated from a weighted sum (modulo 103) of all the characters within the barcode (i.e. modulo 103 of the Start Character + all characters within Encoded Data).

To represent all 128 ASCII values, there are actually three Code 128 subtypes, which can be mixed within a single barcode (i.e. within the Encoded Data section):

128A - ASCII characters 00 to 95 (0-9, A-Z and control codes) and special characters
128B - ASCII characters 32 to 127 (0-9, A-Z, a-z) and special characters
128C - 00-99 (double density encoding of numeric only data) and FNC1

For representing digits, subtype C is preferred as it results in barcodes which are physically smaller and easier to read than the same data recorded entirely in subtype A or B. Subtype B is preferred for alphanumeric data.

Example barcode and check character calculation

Data to encode: 01234567890A

Barcode characters	StartC	01	23	45	67	89	CodeB	0	A	19	Stop
Value	105	1	23	45	67	89	100	16	33
Weights	1	1	2	3	4	5	6	7	8
cell-content	105	1	46	135	268	445	600	112	264

Sum of Products = 1976 modulus 103 = 19
Data returned by the barcode scanner: 01234567890A

For more information on Code 128 barcodes, please refer to the following sites:

STEP 1: Finding an appropriate Code 128 font

The first step is to find an appropriate Code 128 font for Microsoft Word. There are many Code 128 fonts out there (as Google searches testify), but few are free. The barcode font should be readable by your Code 128-compatible USB barcode scanner (you’ll need one for testing), and most importantly, it should be readable by the barcode scanners that will be used in the live production system. In other words, it is not enough to simply test the barcodes with your own USB barcode scanner; additional test will be needed to ensure it’ll work in the production system. For example, if you are generating invoices with barcodes on them, and these barcodes are scanned by a third-party, you’ll also need to test that their barcode scanners can actually read the barcode.

The example Code 128 font used in this article is free, and is available under the GNU General Public licence:
http://grandzebu.net/index.php?page=/informatique/codbar-en/code128.htm

STEP 2: Installing the font on the local machine

Before barcodes can be used in Microsoft Word (which is used for manipulating XML Publisher RTF templates), the Code128 barcode font needs to be installed on the developer’s local machine. This can be done by copying the downloaded font into the following directory:

C:\Windows\fonts\

STEP 3: Installing the font on the App Server/Process Scheduler

In order to generate XML Publisher reports containing barcodes, the barcode font needs to be installed on the Application and Process Scheduler servers.

Firstly, the barcode font (code128.ttf in our example) will also need to be installed in the following directory on the Application Server:

<server path>/fonts/truetype/

Afterwards, an XML Publisher configuration file needs to be created in the following directory, in order to make use of the new barcode font:

<server path>/jre/lib/xdo.cfg on the application server(s)
<server path>/jre/lib/xdo.cfg on the process scheduler server(s)

The XMLP config file should contain the following:

<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
   <!-- Font setting -->
   <fonts>
      <font family="Code 128" style="normal" weight="normal">
         <truetype path="<server path>/fonts/truetype/code128.ttf " />
      </font>
   </fonts>
</config>

The information in the configuration file only overrides settings that are specified in the file.

NOTE: It is necessary to apply this config to BOTH the Application Servers and the Process Scheduler servers. XML Publisher reports can be created “on the fly” (using the Application Server), or in batch (using the Process Scheduler server) – as such, the barcode font needs to be installed on all of them.

Another approach is to add the font code settings into the properties of the Word document:

In Word 2007:

Office Orb > Prepare > Properties
Custom tab
Name: xdo-font.Code 128.normal.normal
Type: Text
Value: truetype.PS_HOME/fonts/truetype/code128.ttf

STEP 4: Using Code 128 font within XML Publisher RTF templates

The data to encode for the barcode should exist in the Data Source for the XML Publisher report. In our example, we’ll assume that the data source is a Rowset, and that the field name for barcode data is BARCODE.

To use barcodes within your RTF template, first load the template’s Data Source which contains barcode data (XSD file).

Then insert the barcode field in your template (using the XML Publisher Word template builder add-in), like this:

Then, change the font for the newly-inserted barcode field (i.e. <BARCODE>). It will then look like this:

Save your RTF template, and upload it into the XML Publisher report definition.

Please note: When this RTF template is modified, it is necessary to first remove the barcode field and add it again in the same spot (in Code 128 font); i.e. repeat the steps outlined in this section. Otherwise, the barcode may not display correctly when the report is generated.

STEP 5: Generating XML Publisher reports containing Code 128 barcodes

Now that the RTF template contains the barcode field, use PeopleCode to generate the XML Publisher report. When generating the report, encode the barcode data (i.e. BARCODE field) before passing it to the template. This is done via the getCode128BarCode function. For example:

&DataSourceRowset(1).REC.BARCODE.value =  getCode128BarCode(RECB.BARCODE.Value);

This function puts start and stop characters, checksum character, and code shift characters if necessary (i.e. from subtype C to subtype B and vice versa). This means that you don’t have to worry about the structure of Code 128 barcodes – all of that is handled within this function.

The getCode128BarCode function code is provided here:

getCode128BarCode.pcode

The getCode128BarCode function is based upon the code provided on GrandZebu for the Code128 function (scroll about half way down):http://grandzebu.net/index.php?page=/informatique/codbar-en/code128.htm

STEP 6: Testing barcodes

As discussed in Step 1, it is necessary to ensure that generated barcode can be read by the relevant barcode scanners.

To test the barcode, print out the generated XML Publisher report and try scanning it with a Code-128 compatible USB barcode scanner. Ensure the expected data is read. Also, ensure barcodes can be scanned by the equipment used in the live production system.