CLOBs & PL/SQL
Date: Mon, 14 Mar 2022 14:31:02 +0000
Message-ID: <d21c363669a44e8a8a8934d75886aaa4_at_ex04mail02d.ad.rit.edu>
I'm trying to update a CLOB field in a table with data from another table. It seems simple enough, but I'm getting an error that I can't figure out. The data in the original table is one field, varchar2(1000). In some cases, the field has a 6 digit number at the beginning and a comma. In some cases, there is no number. The idea is to concatenate the rows with no number at the beginning to the data in the CLOB until a row with a number at the beginning comes up again.
For example:
In these cases, the data in the first row needs to be updated in the other table starting with the 8th character. The second and third rows should be concatenated starting with the 8th character of row 2 and all of row 3.
This is Oracle 19.14 on Red Hat 7.
Here's the code:
create or replace procedure coopeval_owner.add_desc as
begin
last_id int := 0;
last_id := c1_rec.id;
Here's the error:
*
123456,Some text
123457,More text
and still more text
declare
description_line clob;
cursor c1 is
select decode(regexp_count(substr(desc_line,1,6),'\d'),length(substr(desc_line,1,6)),'Y','N') as is_numeric,
substr(desc_line,1,6) as id, desc_line
from coopeval_owner.cp_desc;
begin
dbms_lob.createtemporary(description_line,true);
for c1_rec in c1 loop
if last_id = 0 then
else
if c1_rec.is_numeric = 'Y' then
if to_number(c1_rec.id) <> last_id then
update coopeval_owner.coopplacements
set description = description_line
where id = to_number(last_id);
last_id := to_number(c1_rec.id);
dbms_lob.write(description_line,length(substr(c1_rec.desc_line,8)),1,to_clob(substr(c1_rec.desc_line,8)));
end if;
else
dbms_lob.append(description_line,to_clob(c1_rec.desc_line));
end if;
end if;
end loop;
commit;
end;
end;
/
SQL> exec coopeval_owner.add_desc;
BEGIN coopeval_owner.add_desc; END;
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 1149
ORA-06512: at "COOPEVAL_OWNER.ADD_DESC", line 22
ORA-06512: at "COOPEVAL_OWNER.ADD_DESC", line 22
ORA-06512: at line 1
Line 22 is: dbms_lob.write(description_line,length(substr(c1_rec.desc_line,8)),1,to_clob(substr(c1_rec.desc_line,8)));
I have verified that length(substr(c1_rec.desc_line,8)) are all above 0.
I'm sure it's something simple that I've missed.
Thank you,
Scott Canaan '88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 14 2022 - 15:31:02 CET