一张表中BLOB类型字段,存储文本信息,要求转化成CLOB类型,存储到另外一张表中的CLOB类型字段,求教各位高手,如何实现,希望能写出完整程序。多谢,分不够继续加!!!
解决方案 »
- Oracle 连接池
- 【表结构】在不删除不损坏原表结构的情况下,如何通过PL/SQL修改某一个表的结构(主键或非主键字段的长、类型、是否为空)等?
- 求大大们指点!!!
- 请问一个SQL的性能问题?
- 如何不导出数据,而导出oracle库下所有用户的表结构?(急用,在线等待,谢谢)
- CLOB字段,能不能直接insert?为什么我在insert时遇到几千的字符就报超长了?
- oracle9镜像的问题
- 请高手写一个SQL,有一个表A,根据某种条件选出若干条记录,其中每一条记录都与另外一个表B中若干条记录相关联,我现在想在A的没一条记录
- 哪里有关于oracle方面的书籍下载阿?
- 在线求一条SQL语句 case when 的
- oracle 函数
- oracle如何批量编译包体
clob 存储的是字符串
这个不能转换吧
我也不是很清楚,关注中......
主要使用dbms_lob.convertToClob
----------------------------------------------------------
CREATE TABLE book_samples (
book_sample_id NUMBER (10) PRIMARY KEY,
isbn CHAR(10 CHAR),
description CLOB,
nls_description NCLOB,
misc BLOB,
chapter_title VARCHAR2(30 CHAR),
chapter BFILE
)
LOB (misc)
STORE AS blob_seg ( TABLESPACE blob_ts
CHUNK 8192
PCTVERSION 0
NOCACHE
NOLOGGING
DISABLE STORAGE IN ROW)
LOB (description, nls_description)
STORE AS ( TABLESPACE clob_ts
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
ENABLE STORAGE IN ROW);PROMPT
PROMPT ** Insert a record into book_samples table
PROMPTINSERT INTO book_samples (
book_sample_id,
isbn,
description,
nls_description,
misc,
chapter)
VALUES (
1,
'72230665',
'The essential reference for PL/SQL has been revised and expanded, featuring all new examples throughout based on the new Oracle Database 10g, plus all the book痴 code and expanded topics are included on the website for download.',
EMPTY_CLOB(),
EMPTY_BLOB(),
BFILENAME('BOOK_SAMPLES_LOC', '72230665.jpg'));PROMPT
PROMPT ** Create a dummy record
PROMPTINSERT INTO book_samples (
book_sample_id,
isbn,
description,
nls_description,
misc,
chapter)
VALUES (
2,
'72230665',
EMPTY_CLOB(),
EMPTY_CLOB(),
EMPTY_BLOB(),
BFILENAME('BOOK_SAMPLES_LOC', '72230665.jpg'));PROMPT
PROMPT ** Create procedure CONVERT_ME
PROMPTCREATE OR REPLACE PROCEDURE CONVERT_ME (
v_blob_or_clob IN NUMBER,
v_blob IN OUT BLOB,
v_clob IN OUT CLOB,
v_amount IN OUT NUMBER,
v_blob_offset IN OUT NUMBER,
v_clob_offset IN OUT NUMBER,
v_lang_context IN OUT NUMBER,
v_warning OUT NUMBER)
AS
BEGIN DBMS_LOB.OPEN(v_blob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE); IF v_blob_or_clob = 0
THEN
DBMS_LOB.CONVERTTOBLOB(v_blob,
v_clob,
v_amount,
v_blob_offset,
v_clob_offset,
1,
v_lang_context,
v_warning);
ELSE
DBMS_LOB.CONVERTTOCLOB(v_clob,
v_blob,
v_amount,
v_clob_offset,
v_blob_offset,
1,
v_lang_context,
v_warning);
END IF; DBMS_LOB.CLOSE(v_blob);
DBMS_LOB.CLOSE(v_clob);EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('The conver_me procedure is broken ...');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/PROMPT
PROMPT ** Converting a CLOB to a BLOB and writing it to the table
PROMPTDECLARE
v_clob_or_blob NUMBER;
v_blob_locator BLOB;
v_clob_locator CLOB;
v_blob_offset NUMBER;
v_clob_offset NUMBER;
v_lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning NUMBER;
v_string_length NUMBER(10);
v_source_locator BLOB;
v_destination_locator BLOB;
v_amount PLS_INTEGER;
v_string CLOB;
BEGIN -- CONVERT CLOB TO BLOB
SELECT description
INTO v_clob_locator
FROM book_samples
WHERE book_sample_id = 1
FOR UPDATE; SELECT misc
INTO v_blob_locator
FROM book_samples
WHERE book_sample_id = 1
FOR UPDATE; v_string_length := DBMS_LOB.GETLENGTH(v_blob_locator);
v_amount := DBMS_LOB.GETLENGTH(v_clob_locator); DBMS_OUTPUT.PUT_LINE('The initial length of the BLOB is: '||v_string_length); v_clob_or_blob := 0; -- Convert clob to blob
v_clob_offset := 1;
v_blob_offset := 1; CONVERT_ME(v_clob_or_blob,
v_blob_locator,
v_clob_locator,
v_amount,
v_blob_offset,
v_clob_offset,
v_lang_context,
v_warning); v_string_length := DBMS_LOB.GETLENGTH(v_blob_locator); DBMS_OUTPUT.PUT_LINE('The length of the BLOB post-conversion is: '||v_string_length); -- COPY BLOB FOR ONE ROW TO BLOB IN ANOTHER
v_source_locator := v_blob_locator; SELECT misc
INTO v_destination_locator
FROM book_samples
WHERE book_sample_id = 2
FOR UPDATE; DBMS_LOB.COPY(v_destination_locator, v_source_locator, 32768, 1, 1); v_string_length := DBMS_LOB.GETLENGTH(v_destination_locator); DBMS_OUTPUT.PUT_LINE('The length of the BLOB post-copy is: '||v_string_length); -- COPY BLOB FOR RECORD 2 BACK TO A CLOB
SELECT description
INTO v_clob_locator
FROM book_samples
WHERE book_sample_id = 2
FOR UPDATE; SELECT misc
INTO v_blob_locator
FROM book_samples
WHERE book_sample_id = 2
FOR UPDATE; v_string_length := DBMS_LOB.GETLENGTH(v_clob_locator);
v_amount := DBMS_LOB.GETLENGTH(v_blob_locator); DBMS_OUTPUT.PUT_LINE('The initial length of the CLOB (record 2) is: '||v_string_length); v_clob_or_blob := 1; -- Convert blob to clob
v_clob_offset := 1;
v_blob_offset := 1; CONVERT_ME(v_clob_or_blob,
v_blob_locator,
v_clob_locator,
v_amount,
v_clob_offset,
v_blob_offset,
v_lang_context,
v_warning); v_string_length := DBMS_LOB.GETLENGTH(v_clob_locator); SELECT description
INTO v_string
FROM book_samples
WHERE book_sample_id = 2; DBMS_OUTPUT.PUT_LINE('The length of the CLOB post-conversion is: '||v_string_length); DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('The converted CLOB');
DBMS_OUTPUT.PUT_LINE('==================');
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_string,1,150));
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_string,151,300));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('I''M BROKEN ... FIX ME!');
DBMS_OUTPUT.PUT_LINE(SQLERRM);END;
/
应对9i以前的方法:
---------------------------------------------------------------
CREATE OR REPLACE FUNCTION BlobToClob(blob_in IN BLOB) RETURN CLOB AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in,
v_buffer,
v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
DBMS_OUTPUT.PUT_LINE(v_varchar);
v_start := v_start + v_buffer;
END LOOP; RETURN v_clob;END BlobToClob;
功能是用PL/SQL还原Blob中的文件。
也许能够给你参考。
-----------------------------------------------------
create or replace procedure Convert_F001_Blob_to_File(
wn_gr_no F001.Gr_No%type,
wn_co_no F001.Co_No%type,
wn_f001_seq_no F001.F001_Seq_No%type
) is
NOT_DELETED constant number := 0;
BFILE_ISOPEN constant number := 1;
BUFFER_SIZE constant integer := 10000;
--variables used by source blob
lv_source F001.Space_Area%type;
ln_source_length integer;
lv_amount integer;
lv_offset integer;
lv_buffer raw(10000);
--variables used by target file
lv_target_file utl_file.file_type;
lv_directory_name constant varchar2(1000) := 'SGP_BFILE';
lv_file_name varchar2(1000);
--other variables
ln_raw_count integer;
i integer;
-- close the files
procedure close_files is
begin
if (dbms_lob.isopen(lv_source) = BFILE_ISOPEN) then
dbms_lob.close(lv_source);
end if;
if (utl_file.is_open(lv_target_file)) then
utl_file.fclose(lv_target_file);
end if;
end close_files;
begin
--get the file name and the blob file
select A.FILE_NAME,
A.SPACE_AREA
into lv_file_name,
lv_source
from F001 A
where A.GR_NO = wn_gr_no
and A.CO_NO = wn_co_no
and A.DEL_KBN = NOT_DELETED
and A.F001_SEQ_NO = wn_f001_seq_no;
--open the target file
lv_target_file := utl_file.fopen(lv_directory_name,lv_file_name,'wb');
--get the length of the source file
ln_source_length := dbms_lob.getlength(lv_source);
ln_raw_count := ceil(ln_source_length / 10000);
lv_amount := BUFFER_SIZE;
lv_offset := 1;
for i in 1..ln_raw_count loop
--read from the source file
dbms_lob.read(lv_source,lv_amount,lv_offset,lv_buffer);
--write to the target file
utl_file.put_raw(lv_target_file,lv_buffer);
lv_offset := lv_offset + BUFFER_SIZE;
end loop;
--close the target file
utl_file.fclose(lv_target_file);
exception
when no_data_found then
--close all the file
close_files;
dbms_output.put_line('no such data!');
when utl_file.invalid_path then
--close all the file
close_files;
dbms_output.put_line('no such file!');
when utl_file.invalid_filehandle then
--close all the file
close_files;
dbms_output.put_line('invalid file!');
when utl_file.write_error then
--close all the file
close_files;
dbms_output.put_line('Can not write to the target file!');
when others then
--close all the file
close_files;
dbms_output.put_line(sqlcode || ':' || sqlerrm);
end Convert_F001_Blob_to_File;
/