DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Getting ORA-06502 error..Pls Help

  1. #1
    Join Date
    Jan 2002
    Posts
    65
    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;
    Sabitabrata

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Hi,

    I think you will have to TO_CHAR your number id

    eg.

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

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jan 2002
    Posts
    65
    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??
    Sabitabrata

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  5. #5
    Join Date
    Jan 2002
    Posts
    65
    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??
    Sabitabrata

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  7. #7
    Join Date
    Jan 2002
    Posts
    65
    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
    Sabitabrata

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  9. #9
    Join Date
    Dec 2001
    Location
    Bangalore, India
    Posts
    23
    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.





    Regards,
    Vijay R.

  10. #10
    Join Date
    Jan 2002
    Posts
    65
    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]
    Sabitabrata

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width