Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: value_error while inserting data using a cursor

    the code I am using to concatenate a multiple row/value result set to one name values pair inserted into a table.

    declare
    vdescription varchar2(200);
    prevcase_id number;

    cursor cur1 is
    select a.case_id,b.description from case_classifications a, lm_Case_classification b where a.classification_id = b.classification_id
    order by case_id,description;

    begin
    prevcase_id:=0;
    vdescription:='';
    delete rpt_classifications;

    for cur1_rec in cur1 loop
    if prevcase_id = 0 then
    vdescription:=cur1_rec.description;
    else if cur1_rec.case_id= prevcase_id then
    vdescription:=vdescription||'/'||cur1_rec.description;
    else
    insert into rpt_classifications values (prevcase_id,vdescription);
    end if;

    end if;
    prevcase_id:= cur1_rec.case_id;
    end loop;

    end;


    Iam getting a value error at this line
    vdescription:=vdescription||'/'||cur1_rec.description;

    Pls help..
    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do
    vdescription varchar2(32000);
    instead
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    7

    no didnt help

    its still giving me the same error message
    i changed the tables description data type as long

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Please epost your error message.
    It's impossible to determine the error.
    Thanks.

    I think this would be better since "else if" should probably be "elsif":

    PHP Code:
    for cur1_rec in cur1 loop 

      
    if prevcase_id 0 then 
        vdescription 
    := cur1_rec.description

      
    elsif cur1_rec.case_id prevcase_id then
          
    /* debug here by outputing data */
         
    dbms_output.put_line ('Total length of vdescription = '|| to_char(length(vdescription||'/'||cur1_rec.description)));
         
          
    vdescription := vdescription||'/'||cur1_rec.description
      
      else 
        
    insert into rpt_classifications values (prevcase_idvdescription); 
      
      
    end if; 

      
    prevcase_id  := cur1_rec.case_id

    end loop
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2004
    Posts
    7

    So dumb of me ...

    Iam not resetting the vdescription variable.. it kept on incrementing...
    thanks for the debug tip ..duck..

Posting Permissions

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