Getting ORA-06502 error..Pls Help

[sabita_ban]

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;

[jovery]

Hi,

I think you will have to TO_CHAR your number id

eg.

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

Regards

[sabita_ban]

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

[ales]

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

[sabita_ban]

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

[ales]

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.

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

[sabita_ban]

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

[ales]

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

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

[vijayr]

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.

[sabita_ban]

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]