Procedure fails with numeric or value error

Question

I'm working on a procedure to take rows from a table and change the names slightly to make 50 rows total... as you'll see in code.

However, I'm having an issue

a numeric or value error, as you'll see in code I try using

 dbms_output.put_line('some message'); 

but my output doesn't want to work so finding the issue is becoming troublesome.

an error says

"PL/SQL: numeric or value error"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
       occurred. For example, this error occurs if an attempt is made to
       assign the value NULL to a variable declared NOT NULL, or if an
       attempt is made to assign an integer larger than 99 to a variable
       declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
       that values do not violate constraints.

If anyone can point me in the right direction (or even how to get dbms to work) it would be greatly appreciated

(I have opened dbms and connected to server and set serverouput on;)

Code:

create or replace procedure bbt_phone_users (n in number) authid current_user
as

    cursor r10 is select firstname, lastname, password_ from bbt_users_temp;
    r10type r10%rowtype;

    fn bbt_users_temp.firstname%type;
    ln bbt_users_temp.lastname%type;
    pass bbt_users_temp.password_%type;

    tel varchar2(15);
    keymap_ln varchar(4);

    phone_end number(4);
    name_end number;

begin

    phone_end := 1000;

    dbms_output.put_line('hey');
    for i in 1 .. n
    loop
        open r10;
        fetch r10 into fn, ln, pass;
        close r10;
        for oneRow in r10
        loop
            name_end := name_end + 1;

            dbms_output.put_line('works pre-1');
            --1
            -- each row gets the phone_end, which increments on each iteration
            phone_end := phone_end + 1;
            tel := '(317) 456-' || to_char(phone_end);

            dbms_output.put_line('works after 1');
            --2
            -- takes the last name, and adds 000 and some number if its less than 10 OR
            -- adds (concatinates) 00 and the numbers if its > 10
            if name_end < 10 then
                ln := ln || '000' || to_char(phone_end, '9');
            else
                ln := ln || '00' || to_char(phone_end, '99');
            end if;

            dbms_output.put_line('works after 2');
            --3
            -- calls the KEYMAP function and passes it the lastname
            keymap_ln := KEYMAP(ln);

            dbms_output.put_line('works after 3');

            --4
            -- inserts all our values
            insert into phone_users values(tel, fn, ln, keymap_ln, pass);

            --5
            --rest are ignored since we don't do anything with them

        end loop;
    end loop;
end;
/

call bbt_phone_users(10);

select * from phone_users;

Answer 1

You've declare ln as the same data type and size as bbt_users_temp.lastname. Then you attempt to append five characters to that. So if whatever value your first fetch gets (which is indeterminate as the cursor query has no order-by clause) is within five characters of the maximum allowed length for that column you will get your error on the first time round the inner loop.

Say your column is varchar2(20). If the first fetched value is 16 characters or more - e.g. 'Vandroogenbroeck' - then ln will start with that value, with length 16. You then do:

ln := ln || '00' || to_char(phone_end, '99');

which appends five characters to the existing value, making the length 21. Which is too long to fit in the variable.

Even with a shorter value, say 'Chamberlain', the first time round the loop you append five characters which makes the total 16 and is OK, but then the second time round the loop you append another 5 characters to that - not to the original - which again makes that second value 21, and too long. Even with a shorter name and a longer column it isn't going to take may loops to exceed the limit.

This isn't really the point, but you may only be expecting it to append four characters. It's actually always appending '00###'. You haven't initialised name_end to that is always null and you go into the else, which appends '00' and then tries to format phone_end with mask 99. As phone_end is 1001 at this point it won't fit into two digits, so you'll get ## instead; but you also get a bonus one for the sign position.

Presumably you're trying to account for a different starting phone_end. You can replace your if/else/end block with a single large format model that left-pads with zeros, and suppresses the space for the sign value (You may want to do this for the tel value too):

ln := ln || to_char(phone_end, 'FM0000');

But that's only a small part of the problem; now you're just appending four characters every time round the loop instead of five, so you may just take slightly longer to hit the error.

You probably either want to be appending the four characters to the same initial string every time, or to the value from the current cursor row. And if the column lengths are the same in the source and destination tables then you may need to truncate the initial value before adding the number to make sure it will fit.

It isn't clear what you're trying to end up with, and the loop logic looks suspect (do you really want to insert 10 times the number of rows in bbt_users_temp? What do you think will happen to phone_end and the formatted values if that exceeds 9000 rows in total?). You may be able to rework the logic, and you may not need cursor loops - or even PL/SQL - at all. Without sample data and expected results, and a clearer description, it's impossible to say for sure.