Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47

    Unhappy Unanswered: error ora-06502 help

    My procedure is haveing problem getting the :amount from the text_item on my form. Here is my code

    PROCEDURE Generate_fees IS

    mem_count number(4);
    mem_amount number(4);
    fee_amount number(10,2);
    CURSOR members_cursor IS
    SELECT M_ID, T_TYPE_TT_ID,MEM_TYPE_MT_MEM
    FROM CLUB_MEMS
    WHERE T_TYPE_TT_ID = :TEAM_TYPE
    AND CLUB_MEMS.MEM_TYPE_MT_MEM = 300;
    club_member members_cursor%ROWTYPE;

    BEGIN
    :system.message_level := 25;

    --loop that get the number of cursor records
    fee_amount := :AMOUNT; --amount is a text_item on my form i want the value inside it.
    FOR club_member IN members_cursor LOOP
    mem_count := members_cursor%ROWCOUNT;
    END LOOP;

    --loop cursor that will insert or update the mem_fees table
    FOR club_member IN members_cursor LOOP
    mem_amount := fee_amount/mem_count;
    --call another procedure and pass it the values(m_id,fee_id and mem_amount)
    insert_data(:FEE_ID,club_member.M_ID,mem_amount);
    --DBMS_OUTPUT.PUT_LINE('UK violation, About to UPDATE. '|| club_member.M_ID);
    END LOOP;
    END;

    I think these are the fields that are giving me problems. If i put a number(ie 500) in place of the fee_amount it goes fine.

    fee_amount := :AMOUNT;
    --amount is a text_item on my form i want the value inside it.
    fee_amount number(10,2);
    mem_amount := fee_amount/mem_count;

    I hope anyone can help
    Nerdy Girl

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    I think you need to put the name of the data_block that contains the AMOUNT-item in front of the item name. Like so:
    fee_amount := :BLOCK_NAME.AMOUNT;

  3. #3
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47
    thank you for your reply, I have tried what you had, but still no lick. I think it may have something to do with the datatypes of all the variable.
    The amount text item has a datatype number. Do i need to do any conversions on the datatypes when i am getting them from the forms.

    thank you for your help. This is my first time on a forum and i am surprised with the quick response.

    thank you.
    Nerdy Girl

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Welcome to the forums then. I think you'll find a lot of helpful people here.

    My second guess is that the problem might be mem_amount. If the fee_amount is high and the mem_count is low, mem_amount may not be large enough to contain fee_amount/mem_count.

    Try declaring mem_amount number(10,2);
    That way, if mem_count = 1, mem_amount is large enough to contain the same value as fee_amount.

    (I hope this is it; I can't think of anything else right now...)

  5. #5
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47

    Talking

    thank you glad to be here. I have a new access project so i will be spending alot of time at this forum.

    the problem was with the insert_data procedure that is called from the generate_fees procedure.

    inside the insert_data procedure i was trying to insert data into the database, i had conflicting data type. i was trying to put mem_amount(number ) into amount in the table which had datatype Varchar2. silly i know.

    thanks for your sujjestions. it greate getting feed back.

    tanks again.
    Nerdy Girl

Posting Permissions

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