Home » Developer & Programmer » Forms » Inserting & Retrieving BLOB using Form 6i  () 1 Vote
icon9.gif  Inserting & Retrieving BLOB using Form 6i [message #136410] Wed, 07 September 2005 22:59 Go to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Dear All,

I've problem when inserting and retrieving PDF's file into oracle database via Form 6i. Perhaps someone here has experience working on it. Please give me some advices and clues how to get rid off from this.


TIA,


-Ranubaya
Re: Inserting & Retrieving BLOB using Form 6i [message #136413 is a reply to message #136410] Wed, 07 September 2005 23:14 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Search this forum for 'image'. A few people have asked about how to handle images and have been successful. I don't remember whether PDFs can be handled as images or you need to use 'host' or 'client_host' commands to display them.

Have a search and get back to us.

David
Re: Inserting & Retrieving BLOB using Form 6i [message #136418 is a reply to message #136413] Wed, 07 September 2005 23:38 Go to previous messageGo to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Dear Djmartin,

I've try to insert and view image file (*.gif) using Form 61 and I was successful. Now I try to upload PDF's file into BLOB and get problem. Let's see the script below,

SQL> create table test
  2    (post   varchar2(10),
  3  	loc    varchar2(20),
  4  	resume blob)
  5  /

Table created.

SQL> create or replace directory my_files as 'c:\test'
  2  /

Directory created.

SQL> select owner, directory_name, substr (directory_path, 1, 30) as path
  2  from   all_directories
  3  where  directory_path like '%test%'
  4  /

OWNER               DIRECTORY_NAME                 PATH
------------------- ------------------------------ ------------------------------
SYS                 MY_FILES                       c:\test

SQL> create or replace procedure load_file is
  2    l_clob BLOB;
  3    l_bfile BFILE:=BFILENAME('MY_FILES','metalinksv.pdf');
  4  begin
  5    insert into res_det values('TEST','TEST1',EMPTY_BLOB())
  6    returning resume into l_clob;
  7    dbms_lob.fileopen(l_bfile);
  8    dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
  9    dbms_lob.fileclose(l_bfile);
 10  end;
 11  /

Procedure created.

SQL> show errors
No errors.
SQL> exec load_file
BEGIN load_file; END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 7

Please give me some clues and advices. Am I missing something?


TIA,

-Zettira
Re: Inserting & Retrieving BLOB using Form 6i [message #136420 is a reply to message #136418] Wed, 07 September 2005 23:46 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
This looks more like an SQL*Plus problem than a Forms problem. Do you intend performing this action in a Form or through a script?

David
icon9.gif  Re: Inserting & Retrieving BLOB using Form 6i [message #136423 is a reply to message #136420] Wed, 07 September 2005 23:59 Go to previous messageGo to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Yes, that's right. Is it possible SQL script can be run on Form 6i? Have an idea?


Regards,

Zettira
Re: Inserting & Retrieving BLOB using Form 6i [message #136424 is a reply to message #136423] Thu, 08 September 2005 00:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I'm going to move this thread to the PL/SQL thread. I think the people there may be able to help you more.

David
Re: Inserting & Retrieving BLOB using Form 6i [message #136434 is a reply to message #136423] Thu, 08 September 2005 00:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
I haven't used Forms very much. You might see if anything in the following thread helps you.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:239814548726
icon5.gif  Re: Inserting & Retrieving BLOB using Form 6i [message #136437 is a reply to message #136434] Thu, 08 September 2005 01:17 Go to previous messageGo to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Dear Barbara,

Thank you very much for your reply.
BTW does the directory create on server or client?

Create directory my_files as 'c:\test'

'c:\test' must on server or client?
I try to create directory on my computer (window 2000 server).
But after I run the script I get an error message. pls see script below.

SQL> create table demo (id number, theblob blob);

SQL> create directory my_files as 'c:\test';

SQL> ed
Wrote file afiedt.buf

  1  declare
  2        l_clob BLOB;
  3        l_bfile BFILE:=BFILENAME('MY_FILES','metalinksv.pdf');
  4  begin
  5    insert into demo values(1,EMPTY_BLOB())
  6        returning theblob into l_clob;
  7        dbms_lob.fileopen(l_bfile);
  8        dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
  9        dbms_lob.fileclose(l_bfile);
 10*    end;
SQL> /
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 7

What's wrong with this? Am I missing something? Embarassed

Best Regards,

Zettira
Re: Inserting & Retrieving BLOB using Form 6i [message #136441 is a reply to message #136437] Thu, 08 September 2005 01:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The directory must be on the server, not on the client. Also, 9i and 10g require directory objects as you have used, but 8i would just use the directory path.
Re: Inserting & Retrieving BLOB using Form 6i [message #136469 is a reply to message #136441] Thu, 08 September 2005 02:55 Go to previous messageGo to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Thanks Barbara for your kind answer.
I'm sorry for this. This is my first experience working on BLOB datatype.
It's rather confusing for me. I'm working on server right now and I already create directory on it. Is that right way? My computer isn't a client.
Would you give me spesific answer? May be with some examples.
I'm working on Oracle 8i.


Regards,

Zettira
Re: Inserting & Retrieving BLOB using Form 6i [message #136569 is a reply to message #136469] Thu, 08 September 2005 15:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The file metalinksv.pdf must already exist in the directory c:\test in the operating system of the server. In Oracle 8i, you do not create an Oracle directory object; you just use the actual path as a parameter. I am using 9i, not 8i, so I can't demonstrate, but you would just use the following in 8i, if executing from SQL*Plus. There may be a better way through Forms.

create table demo (id number, theblob blob)
/
declare
  l_blob  BLOB;
  l_bfile BFILE := BFILENAME ('c:\test', 'metalinksv.pdf');
begin
  insert into demo values (1, EMPTY_BLOB())
  returning theblob into l_blob;
  dbms_lob.fileopen (l_bfile);
  dbms_lob.loadfromfile (l_blob, l_bfile, dbms_lob.getlength (l_bfile));
  dbms_lob.fileclose (l_bfile);
end;
/

Re: Inserting & Retrieving BLOB using Form 6i [message #136788 is a reply to message #136569] Sat, 10 September 2005 05:41 Go to previous messageGo to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Thank you very much Barbara.
I already test your script and successful. It's fantastic! genius answer.
Ok now, I need one more help from you. Please see the following script. I make it to display my pdf file from database.


create or replace package image_get
as
    procedure pdf( p_id in demo.id%type );
end;
/
create or replace package body image_get
as

procedure pdf( p_id in demo.id%type )
is
    l_lob    blob;
    l_amt    number default 30;
    l_off   number default 1;
    l_raw   raw(4096);
begin
    select theBlob into l_lob
        from demo 
         where id = p_id;

    owa_util.mime_header( 'image/pdf' );

        begin
           loop
              dbms_lob.read( l_lob, l_amt, l_off, l_raw );

              htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
              l_off := l_off+l_amt;
              l_amt := 4096;
           end loop;
        exception
           when no_data_found then 
              NULL;
        end;
end;
end;

The procedure is running succesfully but without result.
What's wrong with this? And what is
'owa_util.mime_header( 'image/pdf' )'? How to set ('image/pdf')
into my database server?
Thank you very much for your time and I really need your help.

Best Regards,

Zettira
Re: Inserting & Retrieving BLOB using Form 6i [message #136806 is a reply to message #136788] Sat, 10 September 2005 15:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Where are you trying to execute it from? It won't produce any images from SQL*Plus. Owa_util is meant to be run from the web. To display images from Forms, I believe you should be using the Forms utilities, such as read_image_file(). You would probably find more information about that on the Forms forum.


Re: Inserting & Retrieving BLOB using Form 6i [message #136807 is a reply to message #136806] Sat, 10 September 2005 15:24 Go to previous messageGo to next message
ursvenkat
Messages: 1
Registered: September 2005
Location: hyderabad
Junior Member

hi dude, iam venkat, iam also working with forms, one thing u cant work with images in forms, as i know i tell u.
Re: Inserting & Retrieving BLOB using Form 6i [message #136932 is a reply to message #136807] Mon, 12 September 2005 05:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ursvenkat wrote on Sat, 10 September 2005 22:24

hi dude, iam venkat, iam also working with forms, one thing u cant work with images in forms, as i know i tell u.

ROFL

You can tell your friend 'U' what you want but you are terribly wrong.

Forms supports images in BLOB columns. I've used them more than once.

MHE

[Updated on: Tue, 13 September 2005 07:54]

Report message to a moderator

Re: Inserting & Retrieving BLOB using Form 6i [message #137036 is a reply to message #136932] Mon, 12 September 2005 20:26 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Okay ... it looks like we have got as much information as we can from this forum ... I'll move it back to the Forms Forum.

David
Re: Inserting & Retrieving BLOB using Form 6i [message #138417 is a reply to message #136788] Wed, 21 September 2005 17:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Did you find a solution for your problem yet? If so, please post it for the benefit of others. If not, please see if you are able to use something like the following. It may require 9i. In 8i, you can skip the directory object and use the full path, as you did before, but I think you may need 9i for the ctx_doc.ifilter.

scott@ORA92> create table demo
  2    (id	number,
  3  	theblob blob)
  4  /

Table created.

scott@ORA92> -- skip next line for 8i:
scott@ORA92> create or replace directory my_files as 'c:\test'
  2  /

Directory created.

scott@ORA92> declare
  2    l_blob	     blob;
  3    l_bfile	     bfile;
  4  begin
  5    l_bfile := bfilename ('MY_FILES', 'clip.gif');
  6    insert into demo (id, theblob)
  7    values (1, empty_blob())
  8    returning theblob
  9    into l_blob;
 10    dbms_lob.fileopen (l_bfile);
 11    dbms_lob.loadfromfile
 12  	  (l_blob,
 13  	   l_bfile,
 14  	   dbms_lob.getlength (l_bfile));
 15    dbms_lob.fileclose (l_bfile);
 16    commit;
 17    l_bfile := bfilename ('MY_FILES', 'metalinksv.pdf');
 18    insert into demo (id, theblob)
 19    values (2, empty_blob())
 20    returning theblob
 21    into l_blob;
 22    dbms_lob.fileopen (l_bfile);
 23    dbms_lob.loadfromfile
 24  	  (l_blob,
 25  	   l_bfile,
 26  	   dbms_lob.getlength (l_bfile));
 27    dbms_lob.fileclose (l_bfile);
 28    commit;
 29  end;
 30  /

PL/SQL procedure successfully completed.

scott@ORA92> create or replace procedure get_blob
  2    (p_id in demo.id%type)
  3  as
  4    l_clob clob;
  5  begin
  6    for r in
  7  	 (select theblob
  8  	  from	 demo
  9  	  where  id = p_id)
 10    loop
 11  	 dbms_lob.createtemporary (l_clob, true);
 12  	 ctx_doc.ifilter (r.theblob, l_clob);
 13  	 l_clob := p_id ||' ' || l_clob;
 14  	 for i in 0 .. ceil (length (l_clob) / 200) loop
 15  	   dbms_output.put_line (substr (l_clob, (i * 200) + 1, 200));
 16  	 end loop;
 17    end loop;
 18  end get_blob;
 19  /

Procedure created.

scott@ORA92> show errors
No errors.
scott@ORA92> set serveroutput on
scott@ORA92> execute get_blob (1)
1 <HTML><BODY>



    <H1>Image 1</H1>

    <BR>

  



</BODY></HTML>



PL/SQL procedure successfully completed.

scott@ORA92> execute get_blob (2)
2 <HTML><BODY>






Stuffed Peppers
Servings: 6
Preparation Time: 1 hours 10 minutes
... truncated to save space
. Bake in
preheated 350-degree oven for 1 hour. Serve as is or with heated tomato sauce, especially
sauce with herbs
added.
Per Serving (excluding unknown items): 592 Calories; 39g Fat (58.6% calories
from fat); 34g Protein; 27g Carbohydrate; 4g Dietary Fiber;
131mg Cholesterol; 342mg Sodium.
Exchanges: 1 Grain(Starch); 4 Lean Meat; 1 1/2 Vegetable; 0 Non-Fat Milk; 5 Fat.
</BODY></HTML>

PL/SQL procedure successfully completed.

Re: Inserting & Retrieving BLOB using Form 6i [message #138434 is a reply to message #138417] Thu, 22 September 2005 00:32 Go to previous messageGo to next message
zettira
Messages: 11
Registered: September 2005
Location: indonesia
Junior Member

Hi Barbara,

Thank you very much for your kind attention.
My oracle database is running on AIX. I can insert BLOB into my table after I change my directory. Actually directory in AIX and windoz are not the same, right?
I already processed your script but unfortunately my Oracle 8i can't fires ctx_doc.ifilter. Do you have any other way to substitute ctx_doc.ifilter?


Best Regards,

Zettira
Inserting & Retrieving BLOB using Form 6i [message #165673 is a reply to message #136410] Fri, 31 March 2006 06:25 Go to previous messageGo to next message
RAS_SANKAR
Messages: 42
Registered: March 2006
Location: India
Member
can any body send the code for inserting and retrieving blobs using forms 6i
Re: Inserting & Retrieving BLOB using Form 6i [message #664832 is a reply to message #165673] Tue, 08 August 2017 02:17 Go to previous messageGo to next message
rustamsatti
Messages: 1
Registered: August 2017
Junior Member
SQL> DESC DOCUMENTS;
 Name                            Null?    Type
 ------------------------------- -------- ----
 PERSONALNO                               CHAR(4)
 DOCUMENTID                      NOT NULL CHAR(10)
 REFERENCENO                              VARCHAR2(30)
 ISSUEDATE                                DATE
 DOCUMENTTYPEID                           CHAR(2)
 SUBJECT                                  VARCHAR2(50)
 DESIGNATIONID                            CHAR(3)
 FILENO                                   VARCHAR2(30)
 DEPARTMENTID                             NUMBER
 ATTACHEMENT                              BLOB

DECLARE
  filename VARCHAR2(4000) ;
 BEGIN
 	filename := GET_FILE_NAME('','','JPEG FILES/"(*JPG.)|*.JPG|',
 	'select a image', open_file,TRUE);
 read_image_file('X:\Rustam_Satti\Files\'||:documents.Documentid||'.jpg','any','DOCUMENTS.ATTACHEMENT');
 
 END; 

Re: Inserting & Retrieving BLOB using Form 6i [message #669128 is a reply to message #136434] Wed, 04 April 2018 03:19 Go to previous messageGo to next message
syedferhat
Messages: 14
Registered: February 2013
Location: Karachi, Pakistan
Junior Member
Dear following link is given in your post not available

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:239814548726
Re: Inserting & Retrieving BLOB using Form 6i [message #669129 is a reply to message #669128] Wed, 04 April 2018 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66943
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course, things change on the web in 12.5 years!
Go to the site, fill the search field with the title and you will find the article.

Re: Inserting & Retrieving BLOB using Form 6i [message #669130 is a reply to message #669129] Wed, 04 April 2018 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66943
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and envisage to feedback in the topics you posted questions if you want to get help.

Re: Inserting & Retrieving BLOB using Form 6i [message #669279 is a reply to message #669128] Wed, 11 April 2018 17:02 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
syedferhat wrote on Wed, 04 April 2018 01:19
Dear following link is given in your post not available

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:239814548726
Please try the links below.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:239814548726

https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-to-store-images-into-the-database-through-forms
Previous Topic: Record not saved despite Transaction Successful message
Next Topic: Facing compilation errors with Oracle forms 11g
Goto Forum:
  


Current Time: Fri Apr 10 04:47:45 CDT 2020