Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error string
ORA-06502: PL/SQL: numeric or value error string [message #246984] Fri, 22 June 2007 13:43 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
DECLARE
v_concat_desc_smry VARCHAR2(4000) DEFAULT ' ';
v_latest_desc_smry VARCHAR2(2000) DEFAULT ' ';
CURSOR c_concat_desc_smry IS
SELECT *
  FROM concat_desc_smry
ORDER BY INDIVIDUAL_ID ASC, first_row_by_id ASC;
BEGIN
FOR cv_concat_desc_smry IN c_concat_desc_smry
LOOP
       /* FIRST.INDIVIDUAL_id */
--   v_latest_desc_smry := 
   IF cv_concat_desc_smry.first_row_by_id = 'Y' 
   THEN
   	   v_concat_desc_smry := substr(v_concat_desc_smry ||
                                    cv_concat_desc_smry.desc_smry,1,4000);
       v_latest_desc_smry := substr(v_latest_desc_smry || 
                                    cv_concat_desc_smry.desc_smry,1,2000);
   END IF;
   IF cv_concat_desc_smry.first_row_by_id IS NULL
   THEN
       v_concat_desc_smry := '(1)' || v_concat_desc_smry || ', (2) ' || cv_concat_desc_smry.desc_smry;
   END IF;
   IF cv_concat_desc_smry.last_row_by_id = 'Y' 
   THEN
   	   UPDATE concat_desc_smry
		  SET concat_desc_smry.concat_desc_smry = substr(v_concat_desc_smry, 1, 4000),
		      concat_desc_smry.latest_desc_smry = v_latest_desc_smry
		WHERE Individual_ID = cv_concat_desc_smry.Individual_ID;
	   COMMIT;
--	   v_concat_desc_smry := '';
	   
   END IF;
END LOOP;
END;
/


ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 26

I am not sure why this is happening.

[Updated on: Sat, 23 June 2007 10:58]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error string [message #246988 is a reply to message #246984] Fri, 22 June 2007 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am not sure why this is happening.
I am not sure which is line 26.
Re: ORA-06502: PL/SQL: numeric or value error string [message #246993 is a reply to message #246984] Fri, 22 June 2007 14:48 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
in your concat_desc_smry table the column concat_desc_smry must be at least 4000 characters in size (varchar2, char, or clob) and the column latest_desc_smry must be at least 2000 characters in size. Do a describe on the table and find out.


Also, it is not a good idea to use a column name that is the exact same as the table name. Oracle doesn't disallow it, but it can cause confusion for someone looking at the code.

[Updated on: Fri, 22 June 2007 14:50]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error string [message #246994 is a reply to message #246984] Fri, 22 June 2007 14:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Where does one learn concepts such as committing in the middle of a loop, defaulting VARCHARs to a space and using ASC in an order by clause even though that is the default?
This is a serious question. I would like to know if this was from school, a book, a non-university class, a friend, etc.
Re: ORA-06502: PL/SQL: numeric or value error string [message #246999 is a reply to message #246994] Fri, 22 June 2007 15:11 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
I understand ASC and commit in the middle of loop are wrong concepts. THese are not my code nevertheless. I'm just trying to use it.

But the fact about defaulting to ' ' shouldn't be prohibited at any situations. In this case, what the programmer is trying to achieve is to accumulatively concatenate the rest of the strings to v_concat_desc_smry and v_latest_desc_smry. What would be your suggested way if not using ' '?



Re: ORA-06502: PL/SQL: numeric or value error string [message #247001 is a reply to message #246993] Fri, 22 June 2007 15:30 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Does that mean I have to re-create those two variables to a different type ?

As far as I know the limit for the length of VARCHAR2 would be 4000, what's your suggested solution to it?

Re: ORA-06502: PL/SQL: numeric or value error string [message #247002 is a reply to message #246984] Fri, 22 June 2007 15:33 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You can make it anything you want, but your index can NOT be over the maximum length. Determine different columns to index. Why are you using that particular index? What are you trying to do?
Re: ORA-06502: PL/SQL: numeric or value error string [message #247004 is a reply to message #247002] Fri, 22 June 2007 15:39 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
If it's first_row of the group, then concatenate concat_desc_smry, and latest_desc_smry with itself.

If it's not first_row of the group, then concatenate in the following way '(1)' || v_concat_desc_smry || ', (2) ' || cv_concat_desc_smry.desc_smry <== this is the line that's causing me errors. The programmer who coded this is on vacation. SO I have to debug his code and found no luck after 1 and half hours.

If it's last_row in that particular grouping, update concat_desc_smry to itself, and latest_desc_smry to itself for that individual_id (record on a individual basis.)

Really I'm not sure why no matter how I tweaked it, it still gives me an error on that line. I tried out with no concatenation, it worked. But that's not what I intended.



[Updated on: Fri, 22 June 2007 15:39]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error string [message #247057 is a reply to message #246984] Sat, 23 June 2007 06:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is this supposed to do?
Quote:
TO_CHAR('(1)')

and also the idea behind
Quote:
TO_CHAR(', (2) ')
is totally unclear to me

[Updated on: Sat, 23 June 2007 06:10]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error string [message #247076 is a reply to message #247057] Sat, 23 June 2007 10:59 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
Thanks. That was part of the debugging process. I realized that wasn't necessary.
Previous Topic: Finding missed records and Inserting them in the same table
Next Topic: Problem in designing a database for project management
Goto Forum:
  


Current Time: Fri Dec 09 23:30:40 CST 2016

Total time taken to generate the page: 0.10009 seconds