numeric or value error: character to number conversion error pl/sql

so I'm really just trying to output the lines to the 12 days of Christmas, just to get a handle on loops in PL/SQL, and I can't even seem to get a simple array declared correctly. Here is the code:

CREATE OR REPLACE
    TYPE all_gifts is object (first_words varchar2(20), second_words varchar2(50));
/
DECLARE
type gifts is table of all_gifts;
type daysarray IS VARRAY(12) OF VARCHAR2(20); 


lv_gifts GIFTS := gifts(  all_gifts('and a','Partridge in a pear tree')
                        , all_gifts('Two','Turtle Doves')
                        , all_gifts('Three','French Hens')
                        , all_gifts('Four','Calling Birds')
                        , all_gifts('Five','Golden Rings')
                        , all_gifts('Six','Geese a laying')
                        , all_gifts('Seven','Swans a Swimming')
                        , all_gifts('Eight','Maids a milking')
                        , all_gifts('Nine','Ladies Dancing')
                        , all_gifts('Ten','Lords a leaping')
                        , all_gifts('Eleven','Pipers piping')
                        , all_gifts('Twelve','Drummers drumming')
                        );
second_array_elem varchar2(50);
lv_counter NUMBER := 1;
lv_days daysarray;
lv_first_word VARCHAR2(50);
BEGIN
lv_days := daysarray('First','Second','Third','Fourth','Fifth','Sixth','Seventh','Eigth','Ninth','Tenth','Eleventh','Twelefth');

FOR day in 1 .. lv_days.count loop

    IF day != 'first' THEN
        lv_first_word := second_array_elem;
    ELSE 
        lv_first_word := 'A';
        second_array_elem := lv_gifts.first;
    END IF;

    dbms_output.put_line('On the ['||lv_days(day)||'] day of Christmas');

    second_array_elem := lv_gifts.next(second_array_elem);

END LOOP;

END;
/

And the console is throwing an error on the line right after my BEGIN statement (where I'm trying to declare the lv_days array) saying:

numeric or value error: character to number conversion error

But above, in my declare statement, I have it as a varying array of 12 indexes with the type of varchar2(20).
What simple step did I miss here?

1 answer

  • answered 2018-10-12 09:37 chaitanya kvv

    Issue is with the below line of code..

    IF day != 'first' THEN
    

    As per the code, system is trying to compare the loop variable with string 'first'

    You may use the below code

    IF lv_days(day) != 'first' THEN