Print Image using Reports 6i using the file path

To add further to my earlier post Display image using Reports 6i, here we will discuss how to display image when file path is stored in the table. In our example the file directory is a constant directory and only the file name is stored in the table.
The person requested this post had following problem
1) Need to store image in one of the directory and display that using Reports 6i
2) If file does not exists in the directory, the report should complete normal and do not display any image
3) Psuedo code
If file exists then
display image
else
display blank (no image)
end if;

Most of the steps discussed in earlier post are same with few changes as follows
Create a new formula column say CF_URL and write following code in the format trigger

function CF_URLFormula return Char is
 v_handle  utl_file.file_type;
 v_file_dir VARCHAR2(60) := '/u002/app/applmgr/temp/';
begin
-- The UTL_FILE is used to check if the file exists in the directory.
-- If it is unable to open the file then an exception will thrown.
-- We will catch the exception and return null as the final URL.
-- Without this exception for any invalid path, the report will error out 
-- and not work as expected.
 v_handle := utl_file.fopen(v_file_dir, :photo_name, 'R');
 utl_file.fclose(v_handle);
 RETURN v_file_dir||:photo_name;
exception
 when others then
 srw.message(100,SQLERRM);
 return null;
end;

Another change is in the layout. Screenshot below


Related Post:
Display image using Reports 6i
Insert BLOB Image file into Oracle Database Table

Display images in Reports 6i

On request from one of our reader, Below is the post on how to display and dynamically change image in reports 6i.
Please refer to my post Insert BLOB image file into oracle database table where I have discussed the steps on how to insert a record into BLOB table.
We will use same SV_EMP_TABLE as a reference and using Reports 6i create a report that prints ID Card. For simplicity there are no parameters to the report and it will create ID cards for all the employees for which record exists in the table.
In the data model create following SQL Query

SELECT ID, PHOTO_NAME, PHOTO_RAW ,emp_name
FROM SV_EMP_PHOTO

The next step is to create a layout for the report. Screenshot below


Complete necessary concurrent program setup to run this report from Oracle Apps. Below is the screenshot of the report output.


Related Post:
Insert BLOB Image file into Oracle Database Table

P.S. Click on the image to zoom it.

Oracle Report 6i closes with Error when trying to compile some reports

Sometimes when we try to compile a standard (complicated) oracle reports using Reports 6i, the report builder closes with following error message
Reports Builder has encountered a problem and needs to close. We are sorry for the inconvenience.


One of the fix is to check the version of oracle report and apply a patch and get Reports/Forms to the latest version.

Download patch from metalink

Using 3 of 9 Barcodes in XML Publisher

I am using Code 39(3 of 9) Barcode font for my report. Every thing seems to be fine and barcode seems to be displayed correctly, but not recognized by the barcode reader/gun?
          For code 39 barcode fonts asterisk(*) is used as a start and end delimeter. So any text which is displayed as barcode should have * at the start and end.

E.g. 12345 should be displayed as *12345*

Printing Barcode on a zebra Printer using ZPL

Here we will discuss on how to automatically print Barcodes on a zebra printer from Oracle concurrent program.
Below are the 3 approaches that we tried and was successful with the third approach.
1) Print Labels on a Zebra printer using BI Publisher
The first approach we tried was to print barcodes using BI(formerly XML) Publisher. The report generates a PDF output and should print directly on a Zebra printer using PASTA driver. For some reason the report was not printing directly on a printer. The PDF opened in window and printed seperately/manually using Windows driver was working fine but not meeting our requirement of automatic printing. Hence we had to try second approach.
2) Print Labels using Zebra Enterprise Connector Solution
The Zebra Enterprise Connector Solution is designed to streamline the printing
process for companies using ERP systems such as Oracle BI Publisher. The Zebra
Enterprise Connector Solution helps to lower middleware costs, overhead, and
pre-printer licensing fees. It can be used with an unlimited number of
Zebra ZPL-II printers without additional per-printer licensing fees. We didnt do much research on this as were looking for an approach with minimal cost. More details on this is available at the website of Zebra
3) Print Lables on Zebra printers using Zebra Programming Language(ZPL)
The second and successful approach was to print labels using ZPL. A text output with all the zebra codes was created and which would ultimately print Barcode images on a printer. A normal regular lp command for printing text is used to print over Zebra Printers.
Below is an example of how to print Barcodes

^XA
^FO40,35^AR,10,10^FDItem :^FS
^FO40,115^AR,10,10^FDItem Description:^FS
^FO40,165^AR,10,10^FDQuantity :^FS
^FO40,245^AR,10,10^FDDiscrete Job# :^FS
^FO40,295^AR,10,10^FDSupply Sub :^FS
^FO40,375^AR,10,10^FDLocator :^FS
^FO40,455^AR,10,10^FDDestination Sub :^FS
^FO40,505^AR,10,10^FDLocator :^FS
^FO40,555^AR,10,10^FDOrder # :^FS
^FO40,635^AR,10,10^FDLine # :^FS
^FO40,715^AR,10,10^FDOrder Type :^FS
^FO40,765^AR,10,10^FDDepartment :^FS^FO305,765^AR,10,10^FDResource Group :^FS
^FO40,845^AR,10,10^FDWork Center :^FS^FO305,845^AR,10,10^FDItem Sequence :^FS
^FO40,925^AR,10,10^FDAsm Item # :^FS^FO305,925^AR,10,10^FDAsm Item Desc :^FS

^FO265,35^BY3^B3N,N,50,Y,Y^FD199395B^FS
^FO265,115^AR,10,10^FDFILTER, LUBE OIL.^FS
^FO265,165^BY3^B3N,N,55,Y,N^FD2^FS
^FO265,245^AR,10,10^FD613467^FS
^FO265,295^BY3^B3N,N,50,Y,N^FDASM^FS
^FO265,375^BY3^B3N,N,50,Y,N^FDASM.REW..^FS
^FO265,555^BY3^B3N,N,50,Y,N^FD1264076^FS
^FO265,635^BY3^B3N,N,55,Y,N^FD1^FS
^FO265,715^AR,10,10^FDMove Order^FS
^FO50,795^AR,10,10^FD6400^FS
^FO375,795^AR,10,10^FD0047^FS
^FO70,875^AR,10,10^FDASMVH12.4.5.A^FS
^FO335,875^AR,10,10^FD3^FS
^FO70,955^AR,10,10^FDL7044GSI-1021598^FS
^FO335,955^AR,10,10^FDP2212J13 L7044 GSI W/ESM & AFR^FS
^XZ

More details on how to write codes using ZPL can be found in the website of zebra
The output of the above code is shown below.

ORA-24323: Value not Allowed error in Oracle Reports

Sometimes while working in Oracle Reports whenever we are trying to modify any query in the data model
ORA-24323: Value not allowed error is raised.

This happens when we are connected to database and for some reason the connection is dropped/disconnected. When we click on file menu, we feel like the connection still exists, but in reality this is not true. To overcome this error, completely close report builder and reopen it.