Method #1
create table support_portal.dms_det2
as select * from dms_det@LINK_218
UPDATE support_portal.dms_det a
SET CONTENTSS =
(SELECT CONTENTSS
FROM dms_det2
WHERE DOCID = a.DOCID
AND DOCREVID = a.DOCREVID
AND FILENO = a.FILENO
AND FILEID = a.FILEID)
Method#2
UPDATE support_portal.dms_det a
SET CONTENTSS =
(SELECT CONTENTSS
FROM dms_det@LINK_218
WHERE DOCID = a.DOCID
AND DOCREVID = a.DOCREVID
AND FILENO = a.FILENO
AND FILEID = a.FILEID)
--------------------------------------------------------------------------------------
Method#2
It's easy to write a BLOB (CLOB) to an OS file using PL/SQL.
Note that the PL/SQL below uses dbms_lob.read to read the BLOB or CLOB from Oracle, and utl_file.put_raw to write the blob to the file.
For more details on writing advanced PL/SQL, see Dr. Halls book "PL/SQL Tuning".
Here is non-working psuedocode that demonstrates the concept:
CREATE OR REPLACE PROCEDURE extract_file(product_id in number) IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
l_output := utl_file.fopen('DIR_TEMP', 'filename','wb', 32760);
vstart := 1;
bytelen := 32000;
-- get length of blob
SELECT dbms_lob.getlength(productblob)
INTO len
FROM products
WHERE id = product_id;
-- save blob length
x := len;
-- select blob into variable
SELECT product_blob
INTO vblob
FROM products
WHERE id = product_id;
-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
vstart := 1;
WHILE vstart < len and bytelen > 0
LOOP
dbms_lob.read(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
END IF;
utl_file.fclose(l_output);
end loop; |
|
Comments
Post a Comment