AW: CLOBs & PL/SQL

OK, so numeric conversion error on desc_line.

Just asking

> 123456
> 123457

Any text in source table coopeval_owner.cp_desc that looks like that? My bet it’s a null

Mike
http://www.strychnine.co.uk <http://www.strychnine.co.uk/>

> Am 14/03/2022 um 15:31 schrieb Scott Canaan <srcdco_at_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
>
> 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
> 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 <http://c1_rec.id/>;
> else
> if c1_rec.is_numeric = 'Y' then
> if to_number(c1_rec.id <http://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 <http://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-l