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.

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 TABLE SV_EMP_PHOTO
(
  ID          NUMBER(3) NOT NULL,
  PHOTO_NAME  VARCHAR2(40),
  PHOTO_RAW   BLOB,
  EMP_NAME    VARCHAR2(80)
)

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.

CREATE OR REPLACE PROCEDURE sv_load_image (
   p_id                NUMBER
 , p_emp_name     IN   VARCHAR2
 , p_photo_name   IN   VARCHAR2
)
IS
   l_source   BFILE;
   l_dest     BLOB;
   l_length   BINARY_INTEGER;
BEGIN
   l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

   INSERT INTO sv_emp_photo
               (ID
              , photo_name
              , emp_name
              , photo_raw
               )
   VALUES      (p_id
              , p_photo_name
              , p_emp_name
              , EMPTY_BLOB ()
               )
   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
   FOR UPDATE;

   -- 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

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.

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

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.