PL/SQL: numeric or value error

From: bryan munday <bardy_at_MailAndNews.com>
Date: Wed, 23 May 2001 12:42:25 -0400
Message-ID: <3B12A4BD@MailAndNews.com>

Dear all,

I'm having a problem with a procedure I've written. The confusing thing is it
sems to work on small tables but falls over on larger ones.

I essence the code searches through a CLOB field to find the location of some
text and then return it along with it's pair to another table. The code whith
the problem is:

      SELECT obj_text
        INTO v_clob_pos
        FROM object_text
       WHERE pane=v_pane;

/* v_count is number of items on page section. column(name= prefixes item name in object text. tabsequence= prefixes tab sequence order in object text. */

      FOR x IN 1..v_count LOOP
         v_pos1 := DBMS_LOB.INSTR(v_clob_pos,'column(name=',1,X)+12;
         v_pos2 := DBMS_LOB.INSTR(v_clob_pos,' ',v_pos1,1);
         v_var := DBMS_LOB.SUBSTR(v_clob_pos,(v_pos2-v_pos1),v_pos1);
         v_pos3 := DBMS_LOB.INSTR(v_clob_pos,'tabsequence=',1,X)+12;
         v_pos4 := DBMS_LOB.INSTR(v_clob_pos,' ',v_pos3,1);
         v_tab := DBMS_LOB.SUBSTR(v_clob_pos,(v_pos4-v_pos3),v_pos3);
         INSERT INTO pane_items (pane,item,tab)
            VALUES(v_pane,v_var,v_tab);
      END LOOP;

this code is nested withint another loop.

As I sadi above it seems to work for small tables, but when the tables are larger (still not that big) I get the following error:

ERROR at line 1:

ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 484
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "OPS$MUNDAYB.CT4QC", line 232
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

This can appear at, apparently, random places.

Any help on this would be greatly appreciated (the only thing we can vome up wioth here is that there is a bug wiotht eh DBMS_LOB procedures).

Thanks

Bryan

p.s. system is 8.0.5

Get your FREE web-based e-mail and newsgroup access at:

                http://MailAndNews.com

Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser.

Received on Wed May 23 2001 - 11:42:25 CDT