OraFAQ Forum: SQL & PL/SQL » Need help in updating blob column

Home » SQL & PL/SQL » SQL & PL/SQL » Need help in updating blob column

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

Need help in updating blob column [message #191922]

Fri, 08 September 2006 12:26

grk28
Messages: 38
Registered: June 2006

Member

Hi friends,

I have gone through some forums and wrote a procedure to update a blod column and the procedure is as follows

SQL> CREATE OR REPLACE DIRECTORY MY_FILES AS 'C:\MY_DATA';

Directory created.

I have placed the pdf in C:\MY_DATA.

SQL> CREATE OR REPLACE PROCEDURE read_file IS
2 src_file BFILE := bfilename('MY_FILES', 'scheduler.pdf');
3 dst_file BLOB;
4 lgh_file BINARY_INTEGER;
5 BEGIN
6
7
8
9 -- lock record
10 SELECT resume
11 INTO dst_file
12 FROM sam_emp
13 FOR update;
14
15 -- open the file
16 dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
17
18 -- determine length
19 lgh_file := dbms_lob.getlength(src_file);
20
21 -- read the file
22 dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
23
24 -- update the blob field
25 UPDATE sam_emp
26 SET resume = dst_file ;
27 COMMIT;
28
29 -- close file
30 dbms_lob.fileclose(src_file);
31
32
33 END read_file;
34 /

Procedure created.

Elapsed: 00:00:00.68
SQL> exec read_file

PL/SQL procedure successfully completed.

I am able to view the file if it is img/word document ,but if it is .pdf it is saying that the document is corrupted.What could be the reason ??

Also when i m running the same procedure in linux machine i am getting the following error.

SQL> CREATE OR REPLACE DIRECTORY MY_FILES AS /u01/app/oracle';

Directory created.

I have placed these files in above location

SQL> CREATE OR REPLACE PROCEDURE read_file IS
2 src_file BFILE := bfilename('MY_FILES', 'scheduler.pdf');
3 dst_file BLOB;
4 lgh_file BINARY_INTEGER;
5 BEGIN
6
7
8
9 -- lock record
10 SELECT resume
11 INTO dst_file
12 FROM sam_emp
13 FOR update;
14
15 -- open the file
16 dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
17
18 -- determine length
19 lgh_file := dbms_lob.getlength(src_file);
20
21 -- read the file
22 dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
23
24 -- update the blob field
25 UPDATE sam_emp
26 SET resume = dst_file ;
27 COMMIT;
28
29 -- close file
30 dbms_lob.fileclose(src_file);
31
32
33 END read_file;
34 /

Procedure created.

When i am executing my procedure in linux machine it is giving me following error.

SQL> exec read_file;
BEGIN read_file; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 637
ORA-06512: at "SCOTT.READ_FILE", line 22
ORA-06512: at line 1

Can any one of you please help me out.

Regards,
R

Report message to a moderator

Re: Need help in updating blob column [message #191923 is a reply to message #191922]

Fri, 08 September 2006 12:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

22275, 00000, "invalid LOB locator specified"
// *Cause: There are several causes: (1) the LOB locator was never
// initialized; (2) the locator is for a BFILE and the routine
// expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
// BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
// (4) trying to update the LOB in a trigger body -- LOBs in
// trigger bodies are read only; (5) the locator is for a
// BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
// (6) the locator is for a CLOB/NCLOB and the routine expects
// a BFILE/BLOB locator;
// *Action: For (1), initialize the LOB locator by selecting into the locator
// variable or by setting the LOB locator to empty. For (2),(3),
// (5) and (6)pass the correct type of locator into the routine.
// For (4), remove the trigger body code that updates the LOB value.

Report message to a moderator

Re: Need help in updating blob column [message #192004 is a reply to message #191922]

Sat, 09 September 2006 14:14

oigor
Messages: 8
Registered: September 2006
Location: Athens, GREECE

Junior Member

Hi,

Just remove these lines from your program:

25 UPDATE sam_emp
26 SET resume = dst_file ;

Leave COMMIT (this is to save the changes and to release the lock on "FOR UPDATE")
That should be all, and your program should run properly.

DBMS_LOB.LOADFROMFILE procedure already copies the data drom file to BLOB, there's no need for UPDATE statement.

Don't forget to grant read permissions on the folder to all the users that will use it.

I don't see thet your SELECT statement has WHERE part! Be careful, and specify WHERE clause to select only ONE record at the time, otherwise you will get an exception.

----

Igor Obradovic

Report message to a moderator

Re: Need help in updating blob column [message #192011 is a reply to message #191922]

Sat, 09 September 2006 14:27

oigor
Messages: 8
Registered: September 2006
Location: Athens, GREECE

Junior Member

I've just noticed...
I also can't see what you do to initialize BLOB column.

Before you do (e.g.):

SELECT resume INTO dst_file
FROM sam_emp
WHERE ID = 1
FOR update;

You MUST have that "resume" column with ID=1 set to either EMPTY_BLOB() or some other valid BLOB locator.

If the record does not exist, I would do the folloving:

-----------------------

INSERT INTO sam_emp (id, resume)
VALUES (sam_emp_seq.nextval, empty_blob())
RETURNING id INTO my_id;
COMMIT;

SELECT resume INTO dst_file
FROM sam_emp
WHERE ID = my_id
FOR update;

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
COMMIT;
dbms_lob.fileclose(src_file);

-----------------------

So, I introduce a few more elements here Smile

- sam_emp_seq - sequencer to generate ID values for sam_emp.id
- id - column in sam_emp table that will be used as the primary key
- empty_blob() - built-in function to set valid empty BLOB locator
- my_id - a variable to hold the value of ID generated by the sequencer in INSERT statement.

---------------------

Igor Obradovic

Report message to a moderator

Re: Need help in updating blob column [message #192019 is a reply to message #191922]

Sat, 09 September 2006 15:22

grk28
Messages: 38
Registered: June 2006

Member

Hi Igor,

Thanks for your response.It seems to be working .

But i just wonder how it is gonna update that column without update clause ?? Can you please explain that to me as well ?

One more thing is ,i am able to upload .jpg , .xls ,.doc files into that column but not .pdf ,i able to execute the proc successfully but whn i try to view the .pdf file it is saying file is damaged .What could be the reason ? Is this the problem with the adobe version that i am using or something else ?

Please help me out in this as well.

Regards,
K

Report message to a moderator

Re: Need help in updating blob column [message #192021 is a reply to message #191922]

Sat, 09 September 2006 16:13

oigor
Messages: 8
Registered: September 2006
Location: Athens, GREECE

Junior Member

DBMS_LOB.LOADFROMFILE procedure is doing the update for you.
Why is so!? Well, it has to do with BLOB type itself.
Many things are different with BLOBs comparing to scalar types, and that's because it's an object.
So, this is how it works... You do not use UPDATE, you must use locators, empty_blob() function, DBMS_LOB package, etc etc... If you code an application, you will have to use streams to deal with BLOBs, while with VARCHAR2 you won't.

Concerning the PDF, I'm not sure what's wrong there.. But if your program works fine with JPG, GIF, Word, MP3, AVI, etc, then it cannot be your program that is corrupted. Neither can be that your program corrupts the PDF file.

Is everything OK with your extracting procedure from the DB?
How do you extract back from the column to the file?

Try to save the content of the column to the file using TOAD or similar tool. Then try to open the PDF with various PDF viewers.
Also, try with different PDF files, e.g. with some files that can open on your computer for sure...

Ciao,

Igor

Report message to a moderator

Re: Need help in updating blob column [message #192061 is a reply to message #191922]

Sun, 10 September 2006 14:40

grk28
Messages: 38
Registered: June 2006

Member

Hi Igor,

Thanks for explaining the concepts behind BLOB .

Well, i am executing procedure through sql prompt and viewing the file using TOAD.It opens JPG,GIF ,word documents perfectly,but when it comes to PDF it says that the file is damaged.

Do you think is there any problem with my TOAD or with my Adobe acrobat reader ??.

Anyways let me try using another version of Adobe.

Please let me know if you get some thought on this.

Thanks in Advance.
Raghukalyan.G

Report message to a moderator

Previous Topic:	ORA-06502: PL/SQL: numeric or value error
Next Topic:	Stored Procedure using SQL insert for clob data > 4k

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 18 20:26:15 CDT 2025