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

19 Comments:

Ram said...

hi sutresh

i will try this Code.
Thanks
Ram

Trusha said...

Hi Suresh,

I have tried this code and is working fine. But one question related to this. What if the file is not there in the location? I mean query returns the file name but that file is not exists at the location provided. How to deal with this? Kindly update if you have any idea.

Regards
Trusha

Suresh Vaishya said...

Trusha,
If you see the code, I am using utl_file to check if file exists in the location, when it is unable to open the file then an exception is thrown and you can write your code in the exception to either give a message or display some default image in such situation.

Thanks,
Suresh

Trusha said...

Hi Suresh,

Thanks for reply. I have modified the code as per my requirement and it is working fine.

Thanks
Trusha

juampa said...

not working for me. I have this error when i'm trying to compile the report: "REP-0737: Should be a function with return type 'boolean'"

Suresh Vaishya said...

Can you please paste the code that you are using.

Regards,
Suresh

juampa said...

This is:

function CF_URLFormula return Char is
v_handle utl_file.file_type;
v_file_dir VARCHAR2(60) := '\\cbrnt\Usuarios\DAC\Seccion Personal\Funcionarios\FOTOS FUNCIO\';
begin
v_handle := utl_file.fopen(v_file_dir, :fun_nrofunc, 'R');
utl_file.fclose(v_handle);
RETURN v_file_dir||:fun_nrofunc;
exception
when others then
srw.message(100,SQLERRM);
return null;
end;

Suresh Vaishya said...

Also check the return type and size of the formula column. Should be character.

Rahul Pardeshi said...

function CF_1Formula return Char is
v_handle utl_file.file_type;
v_file_dir VARCHAR2(60) := '/usr/tmp';
begin

SRW.Do_SQL ( 'CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS ' || '''/usr/tmp''');

-- 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( 'SAMPLE_FILES_DIR', 'GHH_Rand_Logo.jpg', 'R');
utl_file.fclose(v_handle);
RETURN v_file_dir|| 'GHH_Rand_Logo.jpg';
exception when others then
srw.message(100,SQLERRM);
return null;
end;

------------------
function CF_1Formula return Char is
v_handle utl_file.file_type;
v_file_dir VARCHAR2(60) := '/gpfs_u02/escnvappl/irpo/reports/US';
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, 'GHH Rand Logo.jpg', 'R');
utl_file.fclose(v_handle);
RETURN v_file_dir||'GHH Rand Logo.jpg';
exception when others then
srw.message(100,SQLERRM);
return null;
end;
----------------------------
both of them are not working

Suresh Vaishya said...

Please check your log file for any error message. Possibly directory do not exists or you do not have read permission to the directory.
check v$parameter for name = utl_file_dir and get the directory name.
Let me know if any more questions.

Regards
Suresh

Anonymous said...

Hi,
I like this article. Is it possible to read the show images froma webserver using there URL. If so, how can we do that.

Thanks
SG

Balaji said...

I got an error INVALID DIRECTORY PATH in my log.please help me out.

Anonymous said...

heey

please if you can help me the report is not showing the image.this is the formula:

function CF_URLFormula return Char is
v_handle utl_file.file_type;
v_file_dir VARCHAR2(60) := '/u03/oracle/prod/prodappl';
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, 'pic.jpg', 'R');
utl_file.fclose(v_handle);
RETURN v_file_dir||'pic.jpg';
exception
when others then
srw.message(100,SQLERRM);
return null;
end;

Anonymous said...

dear sir
my hr code dont get all employees can any one help me.
code:
SELECT PPF.EMPLOYEE_NUMBER,
PPF.FULL_NAME,
PAD.JOB,
PAD.ORGANIZATION,
P.PERSON_ID,
NVL (SUM (P.ABSENCE_DAYS), 0) ABSENCE_DAYS,
NVL (SUM (p.ABSENCE_hours), 0) ABSENCE_hours,
PER_UTILITY_FUNCTIONS.GET_NET_ACCRUAL (
PAD.ASSIGNMENT_id,
61,
Ppf.Business_Group_ID,
NULL,
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1,
61,
NULL,
NULL
)
NET_ACCRUAL_TOlastday,
ROUND (APPS.XX_GET_ACC_PLANN_MAHDI (PAD.ASSIGNMENT_ID)) year_rate,
PER_ACCRUAL_CALC_FUNCTIONS.GET_CARRY_OVER (PAD.ASSIGNMENT_id,
61,
SYSDATE,
TRUNC (SYSDATE, 'YEAR'))
carry_over
FROM per_all_assignments_d PAD,
per_all_PEOPLE_F PPF,
HR.PER_ABSENCE_ATTENDANCE_TYPES A,
HR.PER_ABSENCE_ATTENDANCES P
WHERE ppf.person_id = p.person_id
AND PAD.ASSIGNMENT_NUMBER = PPF.EMPLOYEE_NUMBER
AND PPF.EMPLOYEE_NUMBER IS NOT NULL
AND PPF.effective_start_date =
(SELECT MAX (d.effective_start_date)
FROM per_all_people_f d
WHERE PAD.ASSIGNMENT_NUMBER = D.EMPLOYEE_NUMBER)
AND Pad.effective_start_date =
(SELECT MAX (dd.effective_start_date)
FROM per_all_assignments_d dd
WHERE PAD.ASSIGNMENT_id = dd.ASSIGNMENT_id)
AND A.ABSENCE_ATTENDANCE_TYPE_ID = P.ABSENCE_ATTENDANCE_TYPE_ID
AND Pad.effective_END_date > SYSDATE
AND TRUNC (TO_DATE (P.DATE_START), 'yyyy') = TRUNC (SYSDATE, 'yyyy')
AND P.ABSENCE_ATTENDANCE_TYPE_ID IN (70, 65)
GROUP BY PPF.EMPLOYEE_NUMBER,
PPF.FULL_NAME,
P.PERSON_ID,
PAD.JOB,
PAD.ORGANIZATION,
PAD.ASSIGNMENT_id,
Ppf.Business_Group_ID,
PPF.effective_start_date;

Anonymous said...

Thank you!

Anonymous said...

how i can print attached pdf in report 6i ?

Anonymous said...

Easy solution, thanks, better than use a table with a blob column, thanks Suresh very much. Elier

PRASANNA said...

Hi,

Just try this. it'll work for u (oracle Reports 6i)

/********** First create Formula Function CF_1 ************/
open paint and draw one rectangle. inside the rectangle i mentioned no_image text. save it as "f:\all_images\no_image.jpg".

i have table that
create table emp_photo (empcd varchar2(7),name varchar2(20));

i scanned all employees image and store it under "f:\all_images\(filename is EMPCD.jpg)"

AT oracle Reports 6i
---------------------
1. DataModel -> create Formula -> exmaple CF_1
2. now "CF_1" properties set datatype="CHAR" Width=100(width depend your image path length).set value if null = F:\all_images\no_image.jpg
3. write on CF_1 plsql Editor

function CF_1Formula return Char is
OUT_FILE TEXT_IO.FILE_TYPE;
DIRX VARCHAR2(60) :='F:\ALL_IMAGES\';
PIC VARCHAR2(100);
begin
PIC:=DIRX||:empcd||'.JPG';
OUT_FILE:= TEXT_IO.FOPEN('F:\ALL_IMAGES\'||:empcd||'.JPG','R');
RETURN PIC;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null;
when others then
return null;
end;
//


now in paper layout i created a field set field properties source=CF_1,Read from file= Yes, file_format=Image now run report it shows the employee photo . if there is no photo it shows default image "no_image.jpg"

cmooi said...

Hi Suresh,

Thanks for your code.I get the directory from v$parameter for name = utl_file_dir but also return with error file not found. Is there any permission need to grand?

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