DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1'); v_text_buffer := DBMS_LOB.SUBSTR (v_text_loc, v_text_amt, 1);
DBMS_OUTPUT.PUT_LINE ('Chapter 1: ' || v_text_buffer);
END;
/The output of this script is:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.The next example reads sixty characters at a time from the CLOB chapter_text column of the my_book_text table using the 慍hapter 1?row. Note that the loop continues until SUBSTR returns NULL (i.e., SUBSTR does not raise NO_DATA_FOUND exception):DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_pos INTEGER := 1;
v_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1'); LOOP
v_buffer :=
DBMS_LOB.SUBSTR (v_text_loc, v_text_amt, v_text_pos);
EXIT WHEN v_buffer IS NULL; /* process the text and prepare to read again */
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_buffer);
v_text_pos := v_text_pos + v_text_amt;
END LOOP;
END;
/The output of this script is:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.
Chapter 1: An EXCEPTION had not been handled.
DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
DBMS_LOB.READ (v_text_loc, v_text_amt, 1, v_text_buffer);
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_text_buffer);
END;
/The output of this script is:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.The next example reads sixty characters at a time from the CLOB chapter_text column of the my_book_text table using the 慍hapter 1?row. Note that the loop continues until READ raises the NO_DATA_FOUND exception:DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_pos INTEGER := 1;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
LOOP
DBMS_LOB.READ
(v_text_loc, v_text_amt, v_text_pos, v_text_buffer);
/* process the text and prepare to read again */
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_text_buffer);
v_text_pos := v_text_pos + v_text_amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('End of Chapter Reached.');
END;
/This script produces:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.
Chapter 1: An EXCEPTION had not been handled.
End of Chapter Reached.Note that the maximum size of a VARCHAR2 or RAW variable is 32767 bytes. This is the size limit of the buffer to be used with READ.
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1'); v_text_buffer := DBMS_LOB.SUBSTR (v_text_loc, v_text_amt, 1);
DBMS_OUTPUT.PUT_LINE ('Chapter 1: ' || v_text_buffer);
END;
/The output of this script is:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.The next example reads sixty characters at a time from the CLOB chapter_text column of the my_book_text table using the 慍hapter 1?row. Note that the loop continues until SUBSTR returns NULL (i.e., SUBSTR does not raise NO_DATA_FOUND exception):DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_pos INTEGER := 1;
v_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1'); LOOP
v_buffer :=
DBMS_LOB.SUBSTR (v_text_loc, v_text_amt, v_text_pos);
EXIT WHEN v_buffer IS NULL; /* process the text and prepare to read again */
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_buffer);
v_text_pos := v_text_pos + v_text_amt;
END LOOP;
END;
/The output of this script is:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.
Chapter 1: An EXCEPTION had not been handled.
DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
DBMS_LOB.READ (v_text_loc, v_text_amt, 1, v_text_buffer);
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_text_buffer);
END;
/The output of this script is:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.The next example reads sixty characters at a time from the CLOB chapter_text column of the my_book_text table using the 慍hapter 1?row. Note that the loop continues until READ raises the NO_DATA_FOUND exception:DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_pos INTEGER := 1;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
LOOP
DBMS_LOB.READ
(v_text_loc, v_text_amt, v_text_pos, v_text_buffer);
/* process the text and prepare to read again */
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_text_buffer);
v_text_pos := v_text_pos + v_text_amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('End of Chapter Reached.');
END;
/This script produces:Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.
Chapter 1: An EXCEPTION had not been handled.
End of Chapter Reached.Note that the maximum size of a VARCHAR2 or RAW variable is 32767 bytes. This is the size limit of the buffer to be used with READ.
解决方案 »
- 如何获取最接近当前时间的记录?
- oracle数据丢失问题
- 关于精简版 Express Edition 和程序版 Application Express(oracle 10g)
- VS 2008的Server Explorer中添加一个数据库连接,但是总是提示ora-12154 TNS:无法解析指定的连接标示符
- SQL是否有用于字符串的聚合函数
- 网络问卷答题系统的数据库设计!我这里有一份需求,请各位帮助设计一下数据库,需要那几张表。。。
- 求 貌似简单,却难以实现的一SQL语句
- 如何得到schema下每张表的记录数以及表所占用的空间?
- 如何替换某个字段中的回车换行符?急!!!!
- 有人知道吗?
- 存储过程的输入参数的大小怎么定义
- oracle9.2.0.1中blob数据操作问题
参考:
http://expert.csdn.net/Expert/topic/2479/2479396.xml?temp=4.793948E-02
下载个
KXpertPLSQL:
http://www.quest.com/kxpert_plsql/payload/KXpertPLSQLSetup.exetoad:
http://www.quest.com/toad/payload/TOADXpertSetup.exe
toad7.x的注册码:
5-88860-01683-29060-00749可以找到楼上的解释!(KXpertPLSQL好象是绑在toad上的!)
http://211.99.196.144:8090/forum1/frontshow/dispbbs.jsp?boardid=106&id=5355
帮助文档:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lob.htm#ARPLS020