Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure to convert long raw to blob > 32k (oracle 91 rel 2)
Stored procedure to convert long raw to blob > 32k [message #284386] Thu, 29 November 2007 08:48 Go to next message
malakaus
Messages: 7
Registered: May 2005
Junior Member
Hi,

I have a long raw column in a table.(obtained from a third party for which we have no control. I am aware that oracle does not support long raw)

I need to write a stored procedure to convert this long raw(pictures > 32k) to a blob and update this in another table.

We are on Oracle 9i rel 2.

Long raw has pictures and the stored procedure works fine for picture sizes <32k and for picture>32k, it gives a numeric or value error.

Help Appreciated.

My code is pasted here:
PROCEDURE wi_update_Service_Blob
(
in_noti_id IN NUMBER,
out_success_flag OUT VARCHAR2 )
AS

v_blob_dest_bef notification.before_picture%type ;
v_blob_src_bef long raw;
verror varchar2(200) := null;
v_success varchar2(1) := '';
BEGIN
BEGIN
UPDATE notification
SET before_picture = empty_blob()
WHERE notification_id = in_noti_id ;
commit ;
SELECT before_picture
INTO v_blob_dest_bef
FROM NOTIFICATION
WHERE notification_id = in_noti_id
FOR UPDATE;

SELECT acimage
INTO v_blob_src_bef
FROM wamain
WHERE notification_id = in_noti_id
AND action = 'Pre-Repair Picture' ;

DBMS_LOB.APPEND(v_blob_dest_bef,v_blob_src_bef );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
out_success_flag := 'N';
verror := SUBSTR(SQLERRM(SQLCODE),1,450);
END;

END wi_Update_Service_Blob ;

Thanks
Mala.Kaushik
Re: Stored procedure to convert long raw to blob > 32k [message #284395 is a reply to message #284386] Thu, 29 November 2007 09:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow 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.
Always post your Oracle version (4 decimals).

TO_LOB

Regards
Michel
Previous Topic: TRIGGER
Next Topic: SQL join 3 tables
Goto Forum:
  


Current Time: Sat Dec 03 13:46:45 CST 2016

Total time taken to generate the page: 0.08065 seconds