By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

ORA-06502: PL/SQL: numeric or value error

P: 1
I have a CLOB generated by a call to DBMS_XMLGEN.NEWCONTEXT('SELECT ...') which contains data that needs to be parsed and output to file one row at a time; the row data in the CLOB is delimited by </ROW></ROW> pairs.

Expand|Select|Wrap|Line Numbers
  1.  
  2.   v_xml_clob              CLOB;
  3.  
  4. --I use two offset pointers to indicate each row of data:
  5.  
  6.   v_start             NUMBER(10);
  7.   v_end               NUMBER(10);
  8.  
  9.   -- set the initial start-of-row offset position
  10.   v_start := 1;
  11.  
  12.   -- find first occurrence of the end-of-row tag '</ROW>'
  13.   -- add 5 to get to the end of </ROW> if found
  14.   v_end := INSTR( v_xml_clob, '</ROW>', v_start, 1 );
  15.   IF ( v_end > 0 ) THEN
  16.     v_end := v_end + 5;
  17.   END IF;
  18.  
  19. --I then go into a loop to walk through the CLOB, outputting a line at a time:
  20.  
  21.   WHILE v_end > 0 LOOP
  22.  
  23.     -- get and output the next line
  24.     v_line_out := TO_CHAR( SUBSTR( v_xml_clob, v_start, v_end ));
  25.     UTL_FILE.PUT_LINE( v_file, v_line_out );
  26.  
  27.     -- move along: set up the next line output start and end points
  28.     v_start := v_end + 1;
  29.     v_end := INSTR( v_xml_clob,'</ROW>', v_start, 1 );
  30.     IF ( v_end > 0 ) THEN
  31.       v_end := v_end + 5;
  32.     END IF;
  33.  
  34.   END LOOP;
  35.  
  36.  

All of this runs like an Olympian for a few iterations then drops dead like the original marathoner, at this line:

v_line_out := TO_CHAR( SUBSTR( v_xml_clob, v_start, v_end ));

with a plaintive and not very explanatory:

ORA-06502: PL/SQL: numeric or value error

The start and end offsets are well within the CLOB limits; the line out buffer is:

v_line_out VARCHAR2(32767);

The data lines are in the range of 2K chars. Everything appears OK:

- I have not read past the end of the CLOB
- I have not busted the line buffer
- the length of the CLOB remains constant, so does not appear to be corrupted
- I am doing an explicit conversion from CLOB to VARCHAR2 to match the line oput buffer type
- I am not using the dbms_lob package (this is legacy code) but Oracle asserts that SUBSTR works on any char type, including CLOB.

Any brilliant insight will be welcomed and appreciated.
Nov 23 '07 #1
Share this Question
Share on Google+
2 Replies


amitpatel66
Expert 100+
P: 2,367
Hi avimel,

Welcome to TDSN!!

Please make sure you follow POSTING GUIDELINES every time you post in this forum.

Thanks
MODERATOR
Nov 23 '07 #2

amitpatel66
Expert 100+
P: 2,367
I have a CLOB generated by a call to DBMS_XMLGEN.NEWCONTEXT('SELECT ...') which contains data that needs to be parsed and output to file one row at a time; the row data in the CLOB is delimited by </ROW></ROW> pairs.

Expand|Select|Wrap|Line Numbers
  1.  
  2.   v_xml_clob              CLOB;
  3.  
  4. --I use two offset pointers to indicate each row of data:
  5.  
  6.   v_start             NUMBER(10);
  7.   v_end               NUMBER(10);
  8.  
  9.   -- set the initial start-of-row offset position
  10.   v_start := 1;
  11.  
  12.   -- find first occurrence of the end-of-row tag '</ROW>'
  13.   -- add 5 to get to the end of </ROW> if found
  14.   v_end := INSTR( v_xml_clob, '</ROW>', v_start, 1 );
  15.   IF ( v_end > 0 ) THEN
  16.     v_end := v_end + 5;
  17.   END IF;
  18.  
  19. --I then go into a loop to walk through the CLOB, outputting a line at a time:
  20.  
  21.   WHILE v_end > 0 LOOP
  22.  
  23.     -- get and output the next line
  24.     v_line_out := TO_CHAR( SUBSTR( v_xml_clob, v_start, v_end ));
  25.     UTL_FILE.PUT_LINE( v_file, v_line_out );
  26.  
  27.     -- move along: set up the next line output start and end points
  28.     v_start := v_end + 1;
  29.     v_end := INSTR( v_xml_clob,'</ROW>', v_start, 1 );
  30.     IF ( v_end > 0 ) THEN
  31.       v_end := v_end + 5;
  32.     END IF;
  33.  
  34.   END LOOP;
  35.  
  36.  

All of this runs like an Olympian for a few iterations then drops dead like the original marathoner, at this line:

v_line_out := TO_CHAR( SUBSTR( v_xml_clob, v_start, v_end ));

with a plaintive and not very explanatory:

ORA-06502: PL/SQL: numeric or value error

The start and end offsets are well within the CLOB limits; the line out buffer is:

v_line_out VARCHAR2(32767);

The data lines are in the range of 2K chars. Everything appears OK:

- I have not read past the end of the CLOB
- I have not busted the line buffer
- the length of the CLOB remains constant, so does not appear to be corrupted
- I am doing an explicit conversion from CLOB to VARCHAR2 to match the line oput buffer type
- I am not using the dbms_lob package (this is legacy code) but Oracle asserts that SUBSTR works on any char type, including CLOB.

Any brilliant insight will be welcomed and appreciated.
Please POST the output for the following:

Add an EXCEPTION block to your plsql code:

Expand|Select|Wrap|Line Numbers
  1. WHEN OTHERS THEN
  2. DBMS_OUTPUT.PUT_LINE(SUBSTR( v_xml_clob, v_start, v_end ));
  3.  
Also please POST the declaration for v_line_out ie datatype for that variable
Nov 23 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.