Re: PL/SQL: numeric or value error

A copy of this was sent to bryan munday <bardy_at_MailAndNews.com> (if that email address didn't require changing) On Wed, 23 May 2001 12:42:25 -0400, you wrote:

>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;
>

which line of code is line 232 above? the error below says line 232 (your code) is calling to DBMS_SQL -- but I do not see DBMS_SQL in the above snippet at all.

I think you are looking at the wrong place.

See what:

select text from user_souce where name = 'CT4QC' and line between 230 and 240;

returns -- thats where the error is.

>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.
>------------------------------------------------------------

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp