If Picutre not found then return default_picture

[kamran.it]

I used this query for print ID Card It is working fine but I want if there is no picture in the D:\pictures folder of same Employee_code then return default picture.

Code: Select all

SELECT '*'||a.Employee_code||'*' AS "BARCODE", a.Employee_code,
a.employee_code||'.bmp' as pict,a.job_status,
 a.Employee_name, a.Designation_desc,a.new_nic_no,
 a.COMPANY_CODE, b.company_name,b.ADDRESS,
 a.location_code, c.location_name,
 a.department_code, d.department_name,
 e.division_code, division_name
FROM   Employees a,
       company b,
	   locations c,
	   departments d,
                    divisions e
where a.company_code = b.company_code
and   a.location_code = c.location_code
and   a.department_code = d.department_code
and   a.employee_code between  :eno and :sno
and   e.division_name = :div
and   a.division_code = e.division_code
and   a.department_head = 'N'

after this query I used Formula column

Code: Select all

function CF_1Formula return Char is
 
    l_filename text_io.file_type;
BEGIN
  l_filename := text_io.fopen ('.\D:\Pictures\' || :pict, 'r');
  text_io.fclose (l_filename);
  RETURN :pict;
EXCEPTION
  -- the report photo doesn't exist 
  WHEN OTHERS THEN
  BEGIN
    l_filename := text_io.fopen ('.\D:\Pictures\default_image.jpg', 'r');
    text_io.fclose (l_filename);
    RETURN 'default_image.jpg';
  EXCEPTION
    -- the report photo 'default_image.jpg' doesn't exist 
    WHEN OTHERS THEN RETURN NULL;
  END;
END;

report running but pictures is not printing although I have some pictures in said folder.

[Kashif]

Use this Functions it will Solve ur Problem

Fisrt of All Create Directory in ur Schema

Code: Select all

Create or replace directory EMP_PIC as 'D:\Pictures\';

Then Create this function in ur Schema

Code: Select all

Create or Replace
FUNCTION PIC_PATH(emp_id varchar2) Return CHAR is
  pic_file bfile := bfilename( 'EMP_PIC', emp_id||'.bmp' );
BEGIN
  IF dbms_lob.fileexists(pic_file) = 1 THEN
    return 'D:\Pictures\' || emp_id || '.bmp';
  ELSE
    return 'D:\Pictures\default.bmp';
  END IF;
END;

Then Use this Function in ur QUERY for Employee Picture.

Code: Select all

SELECT '*'||a.Employee_code||'*' AS "BARCODE", a.Employee_code,
PIC_PATH(a.employee_code) as pict,a.job_status,
 a.Employee_name, a.Designation_desc,a.new_nic_no,
 a.COMPANY_CODE, b.company_name,b.ADDRESS,
 a.location_code, c.location_name,
 a.department_code, d.department_name,
 e.division_code, division_name
FROM   Employees a,
       company b,
	   locations c,
	   departments d,
                    divisions e
where a.company_code = b.company_code
and   a.location_code = c.location_code
and   a.department_code = d.department_code
and   a.employee_code between  :eno and :sno
and   e.division_name = :div
and   a.division_code = e.division_code
and   a.department_head = 'N'