Home » SQL & PL/SQL » SQL & PL/SQL » How to load LOB in the database (merged)
How to load LOB in the database (merged) [message #290925] Wed, 02 January 2008 03:34 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Guys,

Please assist me in this.

I am making a software that needs to upload resumes in the database. It may also need to load pictures. How to load it in the database

I know till the part where we have to use bfile to point towards the object. Then we are supposed to use the dbms_lob package to load it...can you please walk me through the process..

- Das
Re: How to load LOB in the database [message #290929 is a reply to message #290925] Wed, 02 January 2008 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You will find many examples to load images in your database with a simple search on Google, AskTom or any Oracle forums including here.

Regards
Michel
Re: How to load LOB in the database [message #290978 is a reply to message #290925] Wed, 02 January 2008 08:01 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member

http://atgc.itpub.net/post/22412/409915
E:photo>dir
Volume in drive E is New Volume
Volume Serial Number is F816-C39F

Directory of E:photo

2007-10-31 11:00 <DIR> .
2007-10-31 11:00 <DIR> ..
2007-08-13 16:19 1,212,681 flower.jpg
1 File(s) 1,212,681 bytes
2 Dir(s) 40,406,614,016 bytes free

----------------------------------------------------
SQL> conn a/a
Connected.

grant create any directory to a;
grant create any library to a;
create or replace directory photo_dir as 'e:photo';
create table photos (blob_column blob);

declare
a_blob blob;
a_bfile bfile := bfilename('PHOTO_DIR','flower.jpg');
begin
insert into photos values (empty_blob()) returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob,a_bfile,dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(blob_column) from photos;

DBMS_LOB.GETLENGTH(BLOB_COLUMN)
-------------------------------
1212681


Not able to open file [message #290986 is a reply to message #290925] Wed, 02 January 2008 08:42 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Guys,

While using dbms_lob package I tried to open a file to get its length ...but it says that file or directory not available

Any idea why?

- Das
Re: Not able to open file [message #290987 is a reply to message #290986] Wed, 02 January 2008 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your previous question was not an expert one.
This one which is a follow-up is no more one.
Don't start a new topic stay in the previous one.

Without any code, how can we know where the error comes from?
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

[Updated on: Wed, 02 January 2008 08:50]

Report message to a moderator

Re: How to load LOB in the database (merged) [message #291004 is a reply to message #290925] Wed, 02 January 2008 11:21 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
ok, MICHEL.

I will make more sense from the next message onwards. Sorry

- das
Re: How to load LOB in the database [message #291175 is a reply to message #290978] Thu, 03 January 2008 04:52 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks a lot man..

I tried the code but have come across this error..can you please help me in this

error 22285 - non existent directory or file for FILEOPEN operation

I have granted the rights to the user as you had written in the code (create any directory and library)..

Any idea why this error is occurring

- Das
Re: How to load LOB in the database [message #291181 is a reply to message #291175] Thu, 03 January 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You obviously did something worng but as you didn't post what you did this is the only answer we can give.
Review what you did.

Regards
Michel
Re: How to load LOB in the database [message #291186 is a reply to message #291181] Thu, 03 January 2008 05:26 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
ok..here is code that I wrote

This is from the sysdba login
........................................
grant create any directory to practice1;
grant create any library to practice1;


This is from the user's (practice1) login
.....................................
create directory d1 as 'd:\doc';
create table photos (blob_column blob);

declare
a_blob blob;
a_bfile bfile := bfilename('d1','taskbar.jpg');
begin
insert into photos values (empty_blob()) returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob,a_bfile,dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/

at the end I got the error ....now can you tell what wrong have I done..

Thanks for the lightening fast reply

- Das
Re: How to load LOB in the database [message #291188 is a reply to message #291186] Thu, 03 January 2008 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 02 January 2008 15:48

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

What in this didn't you understand?

'd1' must be in upper case.

Regards
Michel

Re: How to load LOB in the database [message #291195 is a reply to message #291188] Thu, 03 January 2008 06:12 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hey Michel..thanks a lot..

Let me assure you that I will go through the OraFAQ Forum Guide and will adhere with rules of the forum,

I wanted to know what exactly happens in this insert statement

insert into photos values (empty_blob()) returning blob_column into a_blob;

What does 'returning blob_column into a_blob' do?

Also can you tell me how will you display the picture just stored i n the table?

Thanks a lot for your assistance.

- Das
Re: How to load LOB in the database [message #291209 is a reply to message #291195] Thu, 03 January 2008 06:55 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INSERT returning clause

It returns a lob locator into your variable.

Regards
Michel
Previous Topic: Recompiling base object type and ORA-02303
Next Topic: Difference between the following combination : CREATE-REPLACE & DROP-CREATE
Goto Forum:
  


Current Time: Fri Dec 02 23:23:08 CST 2016

Total time taken to generate the page: 0.07861 seconds