RE: CLOBs & PL/SQL

From: Scott Canaan <srcdco_at_rit.edu>
Date: Mon, 14 Mar 2022 15:47:00 +0000
Message-ID: <0db4254c5af74b6aa6c6fb6e7232bade_at_ex04mail02d.ad.rit.edu>

You are correct. There were lines with nothing after the number and comma.

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.

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Michael D O'Shea/Woodward Informatics Ltd Sent: Monday, March 14, 2022 11:22 AM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: oracle-l_at_freelists.org
Subject: 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

Am 14/03/2022 um 15:31 schrieb Scott Canaan <srcdco_at_rit.edu<mailto: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

Received on Mon Mar 14 2022 - 16:47:00 CET