Home » Developer & Programmer » Reports & Discoverer » print image on criteria (Reports 2.5)
print image on criteria [message #591596] Tue, 30 July 2013 01:59 Go to next message
m.abdulhaq
Messages: 105
Registered: April 2013
Location: Ajman
Senior Member
I have a report based on one table with three columns , one column will be updated upon user intervention that if approves the status flag of that column will be 3 and if he amends it will be 1 , by default it will be null.I want to display digital signature or image if the status is approved.I want to store the image/digital signature in Database.Do i need to create a table with BLOB column and store.


create table ot_rq_head ( r_date date, r_no number , r_status number );

insert into ot_rq_head values(sysdate,1,null);



Re: print image on criteria [message #591597 is a reply to message #591596] Tue, 30 July 2013 02:01 Go to previous messageGo to next message
Littlefoot
Messages: 19349
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, you need a BLOB column to store images.
Re: print image on criteria [message #591701 is a reply to message #591597] Wed, 31 July 2013 01:39 Go to previous messageGo to next message
m.abdulhaq
Messages: 105
Registered: April 2013
Location: Ajman
Senior Member
thanks littlefoot, but one more query , is there a method to insert the images into database.can you suggest me usefull link.
Re: print image on criteria [message #591707 is a reply to message #591701] Wed, 31 July 2013 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 19349
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Google know that better than me; here's one example: I have an image on my database server, in a directory for which I have created (as a privileged user) an Oracle directory object called EXT_DIR:
SQL> create directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL>


Now, as user SCOTT, I'm doing the following:
SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
EXT_DIR

SQL> create table test
  2    (col1    blob,
  3     col2    varchar2(20)
  4    );

Table created.

SQL> declare
  2    l_bfile  bfile;
  3    l_blob   blob;
  4  begin
  5    insert into test (col1, col2)
  6      values (empty_blob(), 'test')
  7      return col1 into l_blob;
  8
  9    l_bfile := bfilename('EXT_DIR', 'picture.jpg');
 10
 11    dbms_lob.fileopen     (l_bfile, dbms_lob.file_readonly);
 12    dbms_lob.loadfromfile (l_blob , l_bfile, dbms_lob.getlength(l_bfile));
 13    dbms_lob.fileclose    (l_bfile);
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> select * from test;

COL1
----------------------------------------------------------------------------------------------------
COL2
--------------------
FFD8FFE000104A46494600010101006000600000FFDB004300080606070605080707070909080A0C140D0C0B0B0C1912130F
141D1A1F1E1D1A1C1C20242E2720222C231C1C2837292C30313434341F27
test


SQL>


Re: print image on criteria [message #592081 is a reply to message #591707] Sat, 03 August 2013 02:01 Go to previous messageGo to next message
m.abdulhaq
Messages: 105
Registered: April 2013
Location: Ajman
Senior Member
thanks littlefoot,i tried using the same steps by first creating a local directory on my computer with name aa and i stored one image a.jpeg, but i am getting the error message as below.


create directory picu as 'd:\aa';

grant read on directory picu to public;

SQL> create table or_image
  2     (col1    blob,
  3      col2    varchar2(20)
  4      );


SQL> ed
Wrote file afiedt.buf

  1  declare
  2        l_bfile  bfile;
  3        l_blob   blob;
  4      begin
  5        insert into or_image (col1, col2)
  6          values (empty_blob(), 'test')
  7          return col1 into l_blob;
  8        l_bfile := bfilename('picu', 'a.jpeg');
  9       dbms_lob.fileopen     (l_bfile, dbms_lob.file_readonly);
 10       dbms_lob.loadfromfile (l_blob , l_bfile, dbms_lob.getlength(l_bfile));
 11       dbms_lob.fileclose    (l_bfile);
 12*    end;
SQL> /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 9

Re: print image on criteria [message #592083 is a reply to message #592081] Sat, 03 August 2013 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 19349
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
l_bfile := bfilename('picu', 'a.jpeg');
'picu' must be in upper case, i.e.
l_bfile := bfilename('PICU', 'a.jpeg');


Also, I hope you created that directory on a database server, NOT on your local PC (unless database is installed on your PC as well, so it is a database server for you).
Re: print image on criteria [message #592090 is a reply to message #592083] Sat, 03 August 2013 04:59 Go to previous messageGo to next message
m.abdulhaq
Messages: 105
Registered: April 2013
Location: Ajman
Senior Member
Yes littlefoot , it worked after changing the filename , thanks a lot.But when i query its giving me some error.


SQL> ED
Wrote file afiedt.buf

  1  declare
  2        l_bfile  bfile;
  3        l_blob   blob;
  4      begin
  5        insert into or_image (col1, col2)
  6          values (empty_blob(), 'test')
  7          return col1 into l_blob;
  8        l_bfile := bfilename('PICU', 'a.jpg');
  9       dbms_lob.fileopen     (l_bfile, dbms_lob.file_readonly);
 10       dbms_lob.loadfromfile (l_blob , l_bfile, dbms_lob.getlength(l_bfile));
 11       dbms_lob.fileclose    (l_bfile);
 12*    end;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM OR_IMAGE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM OR_IMAGE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> desc or_image;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               BLOB
 COL2                                               VARCHAR2(20)

SQL> select col2 from or_image;

COL2
--------------------
test

SQL> select col1 from or_image;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> 

[Updated on: Sat, 03 August 2013 05:07]

Report message to a moderator

Re: print image on criteria [message #592107 is a reply to message #592090] Sat, 03 August 2013 07:11 Go to previous messageGo to next message
Littlefoot
Messages: 19349
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's not really a problem; you can't view BLOB (image, in your case) in SQL*Plus anyway, it is a character-mode tool.

Now that you have a file in a BLOB column, you could try to display it in your report.
Re: print image on criteria [message #592135 is a reply to message #592107] Sun, 04 August 2013 00:43 Go to previous message
m.abdulhaq
Messages: 105
Registered: April 2013
Location: Ajman
Senior Member
Thanks littlefoot.
Previous Topic: Reports to csv
Next Topic: displaying image from database
Goto Forum:
  


Current Time: Fri Aug 01 12:24:36 CDT 2014

Total time taken to generate the page: 0.06842 seconds