不知道你text的具体格式是什么。先给你一些修改lob字段的dbms_lob包中的函数和用途。
PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values
Table 3-3 PL/SQL: DBMS_LOB Procedures To Modify BLOB, CLOB, and NCLOB Values
Function/Procedure Description
APPEND()
Appends the LOB value to another LOB
COPY()
Copies all or part of a LOB to another LOB
ERASE()
Erases part of a LOB, starting at a specified offset
LOADFROMFILE()
Load BFILE data into an internal LOB
TRIM()
Trims the LOB value to the specified shorter length
WRITE()
Writes data to the LOB at a specified offset
WRITEAPPEND()
Writes data to the end of the LOB
PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values
Table 3-3 PL/SQL: DBMS_LOB Procedures To Modify BLOB, CLOB, and NCLOB Values
Function/Procedure Description
APPEND()
Appends the LOB value to another LOB
COPY()
Copies all or part of a LOB to another LOB
ERASE()
Erases part of a LOB, starting at a specified offset
LOADFROMFILE()
Load BFILE data into an internal LOB
TRIM()
Trims the LOB value to the specified shorter length
WRITE()
Writes data to the LOB at a specified offset
WRITEAPPEND()
Writes data to the end of the LOB
解决方案 »
- 请问如何用PL/SQL 输出杨辉三角。 谢谢
- ORA-01555,duration的单位问题
- 如何在存储过程中返回游标类型?
- 小问题,在线等,
- 如何利用XML将数据传到数据库服务器进行解析
- 关于Oracle SQL语句优化的一个难题
- 请教oracle中编写包体脚本中出现的问题
- 做一个通过输入表名,输出该表的记录数的存储过程
- 安装oracle 11g R2 rac遇到节点错误,麻烦高手专家帮忙解答下,感谢
- redhat下安装oracle11g图形界面乱码
- 求oracle基础知识教程,提供网址也可以.从基本的教起.如数据库的创建,表的创建,数据库的删除表的删除,数据更新等.要sql之类语句的教程,不
- (原创)Oracle8.1.7如何在系统启时自动启动---个人总节
text
-------------
abc2001-10-10
abc2001-10-10fasf
2001-10-10fasre
afa2001-10-10dfa2001-10-10还有除了2001-10-10是不是还有2005-01-10等等其它日期也要改?不同的情况程序都不一样的。总之,基本上就是用到上面列出的几种函数。楼主可以根据自己的情况考虑如何使用。实在困难再问吧
declare
source_lob CLOB;
v_pos BINARY_INTEGER;
buffer CHAR(10);
BEGIN
select text into source_lob FROM employee WHERE ID = ....;
v_pos := DBMS_LOB.INSTR(source_lob, '2001-10-10', 1,1);
DBMS_LOB.WRITE(source_lob, 10, v_pos, buffer);
END;
SQL> create table liq (c clob);Table created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> /1 row created.SQL> commit;Commit complete.SQL> select replace(c,'2001-10-10','2001.10.10') from liq;REPLACE(C,'2001-10-10','2001.10.10')
-------------------------------------------------------------------------2001.10.10
2001.10.10
2001.10.10
2001.10.10
2001.10.10SQL> update liq set c=replace(c,'2001-10-10','2001.10.10') ;5 rows updated.SQL> commit;Commit complete.SQL> select c from liq;C
-------------------------------------------------------------------------2001.10.10
2001.10.10
2001.10.10
2001.10.10
2001.10.10SQL>
source_lob CLOB;
v_pos BINARY_INTEGER;
buffer CHAR(10);
BEGIN
buffer := '2001.10.10';
select CLOB_LOCATOR into source_lob FROM MYLOBS WHERE LOB_INDEX = 1 for update;
v_pos := DBMS_LOB.INSTR(source_lob, '2001-10-10', 1,1);
DBMS_LOB.WRITE(source_lob, 10, v_pos, buffer);
END;
------------------------------------------------------------------------------2001.10.10 ddddddddd
2001.10.10 ddddddddd
2001.10.10 ddddddddd
2001.10.10 dddddddddSQL>
source_lob CLOB;
v_pos BINARY_INTEGER;
buffers CHAR(4);
BEGIN
select text into source_lob from employee where id='20050119074938_106';
v_pos := DBMS_LOB.INSTR(source_lob,'.173',1,1);
DBMS_LOB.WRITE(source_lob,4,v_pos,buffers);
END;怎么会出现以下错误?declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在"SYS.DBMS_LOB", line 708
ORA-06512: 在line 8
declare
source_lob CLOB;
v_pos BINARY_INTEGER;
buffer CHAR(6);
CURSOR mylob IS select text FROM employee;
BEGIN
buffer := '2001.10.10';
FOR mycur IN mylob LOOP
select text into source_lob FROM employee WHERE id = mycur.id for update;
v_pos := DBMS_LOB.INSTR(source_lob, '2001-10-10', 1,1);
IF v_pos > 0 Then
DBMS_LOB.WRITE(source_lob, 10, v_pos, buffer);
END IF;
END LOOP;END;
/
COMMIT;
改成
v_pos integer;
v_pos 的值为NULL或为0,buffers没有指定值,应指定成替换后的值。
怎样在sql中象函数一样去调用这个过程呀? 你可以将13楼的语句写成个存储过程。
IS
source_lob CLOB;
v_pos BINARY_INTEGER;
CURSOR mylob IS select text FROM employee;
BEGIN
FOR mycur IN mylob LOOP
select text into source_lob FROM employee WHERE id = mycur.id for update;
v_pos := DBMS_LOB.INSTR(source_lob, source_txt, 1,1);
IF v_pos > 0 Then
DBMS_LOB.WRITE(source_lob, 10, v_pos, desc_txt);
END IF;
END LOOP;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE P_REPL_CLOB(source_txt char, desc_txt char)
IS
source_lob CLOB;
v_pos BINARY_INTEGER;
str_len integer;
CURSOR mylob IS select text FROM employee;
BEGIN
str_len := LENGTH(desc_txt);
FOR mycur IN mylob LOOP
select text into source_lob FROM employee WHERE id = mycur.id for update;
v_pos := DBMS_LOB.INSTR(source_lob, source_txt, 1,1);
IF v_pos > 0 Then
DBMS_LOB.WRITE(source_lob, str_len, v_pos, desc_txt);
END IF;
END LOOP;
COMMIT;
END;
...
END LOOP;
没错,Oracle有这种语法,以上过程在我这里试了,没问题,把你的报错信息贴出来。
CURSOR mylob IS select text FROM employee;
应改为
CURSOR mylob IS select ID FROM employee;