Search This Blog

Thursday, August 13, 2009

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
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/';
-- 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');
RETURN v_file_dir||:photo_name;
when others then
return null;

Another change is in the layout. Screenshot below

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

Wednesday, August 12, 2009

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


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.

Insert BLOB image file in oracle database table

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table


Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.
Create directory SV_PHOTO_DIR as '/u002/app/applmgr/empphoto'

Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.

, p_emp_name IN VARCHAR2
, p_photo_name IN VARCHAR2
l_source BFILE;
l_dest BLOB;
l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

INSERT INTO sv_emp_photo
, photo_name
, emp_name
, photo_raw
VALUES (p_id
, p_photo_name
, p_emp_name
RETURNING photo_raw
INTO l_dest;

-- lock record
SELECT photo_raw
INTO l_dest
FROM sv_emp_photo
WHERE ID = p_id AND photo_name = p_photo_name

-- open the file
DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
-- get length
l_length := DBMS_LOB.getlength (l_source);
-- read the file and store in the destination
DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

-- update the blob field with destination
UPDATE sv_emp_photo
SET photo_raw = l_dest
WHERE ID = p_id AND photo_name = p_photo_name;

-- close file
DBMS_LOB.fileclose (l_source);
END --sv_load_image;

I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database

exec sv_load_image(1,'Pavki','one.jpg')
exec sv_load_image(2,'Suresh','two.jpg')
exec sv_load_image(3,'Rachna','three.jpg')

Following is how data is stored in the database

Tuesday, August 11, 2009

NVL2 Function in Oracle

In Oracle, the NVL2 function extends the functionality found in the NVL function. It can be used to substitute a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function is:

    NVL2( string1, value_if_NOT_null, value_if_null )
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.

Monday, August 3, 2009

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

Sunday, August 2, 2009

Set use of VI Commands at the UNIX prompt

VI commands can be used at the UNIX prompt to retrieve history commands or modify already typed command using VI editor keys. This can be done by executing following command

set -o vi

One of the option is to enter and save this command in .profile file, so that everytime we login in UNIX this command is executed.

Copyright (c) All rights reserved. Presented by Suresh Vaishya