Getting ORA-06502 error..Pls Help

I have a procedure block as given below...
I am getting the above error for the line

Comp_list := Comp_list || CompId || ',' ;

Comp_list is of VARCHAR2(1000),CompId is of type NUMBER(10)...

Please help me out on this...I need to prepare a comma seperated list of numbers but am failing to do so..


DECLARE
CURSOR C2 IS SELECT NAME_ID FROM RPTDEFN WHERE RPTNAME=Report_Name;
BEGIN
OPEN C2;

n :=1;
Comp_list :='';

LOOP
FETCH C2 INTO CompId;

IF n=Comp_Counter THEN
Comp_list := Comp_list || CompId ;
ELSE
Comp_list := Comp_list || CompId || ',' ;
END IF;


n := n+1;

END LOOP;

CLOSE C2;
END;

Hi,

I think you will have to TO_CHAR your number id

eg.

comp_list := comp_list || TO_CHAR(compid) || ',' ;

Regards

Hi,

Tried using TO_CHAR but got same error..

I guess problem is somewhere else because
The line below
Comp_list := Comp_list || CompId ;

Doesn't fail

Only the else part is failing...don't know why??

Hi,
it is probably overflow of the Comp_list variable.
Try to declare it longer than 1000 chars.

BTW, for better performance you should avoid testing "IF n=Comp_Counter THEN ... ".
You can add the comma in each loop and after closing the cursor just trim the last one:
Comp_list := rtrim(Comp_list,',');

HTH,
Ales

Ales,

I have implemented ur second suggestion...but sorry to say that the problem still persists although I have increased the size of Comp_list to VARCHAR2(3000)

I don't think that the string that i need to generate like 123,234,235,235 will ever be of such a huge magnitude...

There can be at max 50 comma seperated components each of max 3 digit making it 50*3+50 chars long at most...

Can there be any other reason for the error??

There could be also Com_Id overflow or type mismatch.
Declare Com_Id as RPTDEFN.NAME_ID%TYPE, not NUMBER(10).

I'd suggest to write an exception block and write all the variables out.

Code:

---

...
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(length(Com_list));
    dbms_output.put_line(Com_list);
    dbms_output.put_line(CompId)
...

---

Feel free to post further questions.
Ales

Ales,

At last was able to get rid of the error....
After a good checking found that there was indeed a buffer overflow and some infinite loops in my procedure.

Thanks for the help.

One more problem that I am getting is ORA-01722
The situation is like this.

I have got Comp_list as 123,456,789,12
and Comp_list is VARCHAR2(1000).

Now I have a query like

SELECT DISTINCT CATEGORYNAME FROM BCOMP_MAP WHERE BCNAME_ID IN (Comp_List);

In Bcomp_map table we have Categoryname as VARCHAR2(60) and BCNAME_ID as NUMBER(10)

So in the select query we have a type mismatch.

I thought of overcoming this using TO_NUMBER but didnt work..

Do u have any idea on how to overcome such a situation??


Thanks in advance

Well, this is a job for dynamic sql.
In 8i I'd do that in this way:

Code:

---

--declare cursor variable:
type type_cc is ref cursor;
my_cursor type_cc;
...
begin
...
  open my_cursor for
    'SELECT DISTINCT CATEGORYNAME FROM BCOMP_MAP WHERE BCNAME_ID IN ('||Comp_list||')';

---

Then you can go through the cursor with FETCH statement ...

HTH,
Ales

Hi,

Since, it is an endless loop you are getting the error. To come out of the loop, include the below command in your code before the END LOOP;

EXIT WHEN C@%NOTFOUND;

Your code should be,

ELSE
Comp_list := Comp_list || CompId || ',' ;
END IF;

n := n+1;

EXIT WHEN C2%NOTFOUND; --- Added by me

END LOOP;


Hoep this will help you.

Ales,

Thank u very much...

Today with ur help I learnt the following things.

1. How to resolve ORA-06502
2. Should not use IF ELSE in loop...this causes performance loss
3. Dynamic sql solved my ORA-01722 ...this doesn't work in Oracle 7.3.4....luckily I had 8i server arround...so was able to figure it out.
4. Good amount of PL/SQL.


Vijayr,

Thanks for pointing out my mistake...I got rid of all infinite loops


Thanks again.

oops..
just to let u know....my procedure is working fine now...
This I had to device because sombody deleted all entries in a particular important table and there was no backup of the table...

So I had to recreate this table with all previous data...fetching from various different existing tables




[Edited by sabita_ban on 03-07-2002 at 09:16 AM]