OraFAQ Forum: Reports & Discoverer » print image on criteria

Home » Developer & Programmer » Reports & Discoverer » print image on criteria (Reports 2.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

print image on criteria [message #591596]

Tue, 30 July 2013 01:59

m.abdulhaq
Messages: 254
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);

Report message to a moderator

Re: print image on criteria [message #591597 is a reply to message #591596]

Tue, 30 July 2013 02:01

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yes, you need a BLOB column to store images.

Report message to a moderator

Re: print image on criteria [message #591701 is a reply to message #591597]

Wed, 31 July 2013 01:39

m.abdulhaq
Messages: 254
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.

Report message to a moderator

Re: print image on criteria [message #591707 is a reply to message #591701]

Wed, 31 July 2013 02:03

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: print image on criteria [message #592081 is a reply to message #591707]

Sat, 03 August 2013 02:01

m.abdulhaq
Messages: 254
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

Report message to a moderator

Re: print image on criteria [message #592083 is a reply to message #592081]

Sat, 03 August 2013 02:07

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: print image on criteria [message #592090 is a reply to message #592083]

Sat, 03 August 2013 04:59

m.abdulhaq
Messages: 254
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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: print image on criteria [message #592135 is a reply to message #592107]

Sun, 04 August 2013 00:43

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Thanks littlefoot.

Report message to a moderator

Previous Topic:	Reports to csv
Next Topic:	displaying image from database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 17:07:04 CST 2025