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:
123456,Some text
123457,More text
and still more text
This is Oracle 19.14 on Red Hat 7.
create or replace procedure coopeval_owner.add_desc as
begin
declare
last_id int := 0;
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
last_id := c1_rec.id;
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;
/
Here's the error:
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