Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 30, 2009

Printing Barcodes in Oracle Reports

By Steve Callan

No, really, you may have to do this one day: enable the use of barcodes in Oracle Reports. Is this a database administration or administrator responsibility? Yes and no. It is obviously not related to anything having to do with data, but it can certainly falls under the “who is our Oracle and all things Oracle-related product expert?” scenario. Of course, that is you, the resident DBA.

There are two ways to categorize the implementation of barcode printing, and they are easy versus hard. More technically, they are Windows versus UNIX. This is a case where installation on a Windows server is much simpler than on a UNIX variant. Let’s assume you want to use the Barcode 3 of 9 font. This font is widely used and can be read by many barcode scanners. Some of the confusion behind which barcode font to use is due to its many names (e.g., code 39, type 3 of 9, and alpha39, to name a few). The leading and trailing characters are asterisks, and in between is the string (alphanumeric and a few special symbols) to be encoded. You can find many places where the font is freely available, but getting it to work for printing is another matter.

For fonts in general, as in installing a new font for Oracle Reports, the documentation for Application Server has for some time now included a chapter or two on managing fonts. In the 10g (to include 9.0.4 version) realm, look in Oracle Application Server Reports Services Publishing Reports to the Web, and in 11g, look in Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services.

What happens when you print reports on UNIX? By and large, the process is the same regardless of the OS version, where Reports goes through a search of various files. The files include uiprint.txt, uifont.ali, and font files for PPD, AFM, HPD and TFM. PPD is for PostScript, AFM is for Adobe, and the other two are for HP glue files. Oracle first looks at the TK60 (or 90) type, then in ORACLE_type, and finally, in the global directory. A search for PPD files would then be TK90_PPD, then ORACLE_PPD, and finally, ORACLE_HOME/guicommon9/tk90/admin/PPD.

Not to confuse matters more, but Oracle Reports in E-Business Suite has some additional complexity involved because you may be using IX (bit-mapped reports) and PASTA (a mechanism for font embedding in PostScript output for character based reports). Several support notes can assist you with these steps (the top two are a good place to start).

551630.1 How to setup, map, and print custom reports with a barcode / MICR font

240864.1 Activating and Configuring IX Library

356501.1 How to Setup Pasta Quickly and Effectively

267026.1 How To Set The PASTA Debug Option

You can also find information in Volume 1 of Oracle Applications System Administration Guide.

For printing Oracle Reports in UNIX and Windows, the process is diagrammed in the publishing references cited earlier. You can see by illustration how much simpler Windows processing is because of what the APIs do.

Oracle Reports printing on UNIX
Oracle Reports printing on UNIX

Oracle Reports printing on Microsoft Windows
Oracle Reports printing on Microsoft Windows

For a quick example of the differences between Windows and UNIX, let’s install the Barcode 3 of 9 font on a Windows machine. Your own PC works as the “machine” in this example. Step one is to first find the font file and download it to the server/machine of interest. Go into Control Panel, select Fonts, and click File at the top menu. Select Install New Font… and follow the rest of the steps in the Add Fonts dialog window.

install the Barcode 3 of 9 font on a Windows machine

When Windows reads the file, it will detect the name of the font. In this example, the “3 of 9 Barcode (True Type)” name appeared.

the “3 of 9 Barcode (True Type)” name appeared

Select the font in the list, click OK, and the font will appear in your Fonts window.

Select the font in the list

Once the font is installed, you now have a poor man’s way of printing barcodes outside of Reports. In any Word document (or application that reads fonts from your font picker list of values), simply select the 3 of 9 Barcode font. The fonts may also appear as a barcode value in the list of fonts.

In Report Builder, format your field(s) of interest and the barcode representation of the field appears. As a simple example, create a report based on select ename, empno from emp in the Scott schema, and in the results, format the empno field using the barcode font. In the picture below, selecting the Ename field shows the font (look in the top left corner) to be Courier New (Western) and when the Empno field is selected, you can see the font displayed as the TrueType 3 of 9 Barcode.

when the Empno field is selected, you can see the font displayed as the TrueType 3 of 9 Barcode

So, for “basic” Oracle Reports where the report server is hosted on a Windows server, you have it made fairly easy when it comes to installing a barcode font (or any font for that matter). Now comes the UNIX side.

Chapter 8 of Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services, 11g Release 1 (11.1.1) provides a good overview of managing (to include adding) fonts for Oracle Reports. A TrueType font installation is very straightforward (copy the font into ORACLE_HOME/reports/fonts, and take note of any prior aliasing). In Oracle Applications, which overall is not documented that well when it comes to installing barcode, where barcodes are obviously used in many manufacturing/shipping environments, and the support notes from Oracle which basically throws its figurative hands up in the air and tells you to contact Oracle Consulting or a third party vender, the process is a little more complex.

A company named IDAutomation sells/licenses a complete package for printing barcodes in many applications, Oracle Applications being one of them. Some of the additional steps here include using an SRW function. This is an opportunity to explore one of those taken for granted areas of Reports Builder. In the explorer pane, go down to Built-in Packages and expand the SRW item. Way down in the list will be a function named SET_FONT_FACE.

a function named SET_FONT_FACE

The support note on activating and configuring the IX library (240864.1) has a section on barcode font support. The configuration here also involves editing another configuration file in the $FND_TOP/resource directory of your “applmgr” account.

In Closing

With respect to barcode usage in Oracle Reports, installation in Windows is easy, “regular” reports on UNIX is a bit more complex, and when the same Oracle Report is part of Oracle Applications, your work is cut out for you and Oracle Corporation leaves you hanging in the breeze a bit. It may be well worth the money to license a packaged implementation from a third party vendor. And for a bit of clarity, reports in Oracle can also be those generated by XML or Business Intelligence Publisher (BIP). If you can do barcodes in Oracle Reports, there is nothing stopping you from printing barcodes in BI Publisher. Open Word, format the field with the barcode font, and off you go.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM