How I can improve my plsql code for working correctly?

Question

I want to create a procedure that would generate records for a training schedule table with dates for 4 weeks(or a month) from the specified start date, 3 times per week (Monday, Wednesday, Friday or Tuesday, Thursday, Saturday). If a training already exists on a date for that user, there is no need to insert a record. The schedule table is:

1. TRAININGID NUMBER

2. USERNAME VARCHAR

3. TRAININGDATE DATE Here my code:

   CREATE OR REPLACE PROCEDURE schedule_training
        (p_training_id IN OUT trainings.trainingid%TYPE,
         p_username trainings.username%TYPE,
         p_training_date IN OUT trainings.trainingdate%TYPE) IS
        v_date DATE := TO_DATE(TO_CHAR(ADD_MONTHS(p_training_date,1),'DD-MON-YYYY'));
        v_date1 trainings.trainingdate%TYPE;
        v_username trainings.username%TYPE;
    BEGIN
        WHILE(p_training_date < v_date)
        LOOP
        SELECT trainingdate, username INTO v_date1, v_username
        FROM trainings;
            IF(TO_CHAR(p_training_date,'d') = 1) THEN
                IF p_username != v_username AND p_training_date != v_date1
                THEN INSERT INTO trainings VALUES
                        (p_training_id, p_username, p_training_date);
                        p_training_id := p_training_id + 1;
                END IF;
            ELSIF(TO_CHAR(p_training_date,'d') = 3) THEN
                IF p_username != v_username AND p_training_date != v_date1
                THEN INSERT INTO trainings VALUES
                        (p_training_id, p_username, p_training_date);
                        p_training_id := p_training_id + 1;
                END IF;   
            ELSIF(TO_CHAR(p_training_date,'d') = 5) THEN
                IF p_username != v_username AND p_training_date != v_date1
                THEN INSERT INTO trainings VALUES
                        (p_training_id, p_username,p_training_date);
                        p_training_id := p_training_id + 1;
                END IF;
            END IF;
   
   
            p_training_date := TO_DATE(TO_DATE(v_date,'dd.mm.yyyy')+ 1, 'dd.mm.yyyy');
   
        END LOOP;
    END schedule_training;
   

   and I am calling this procedure like this:

    schedule_training(1,'user1',TO_DATE('21-03-2021','DD-MM-YYYY'));
   

When I am calling it there is occurring unknown error: Error starting at line : 41 in command - schedule_training(1,'user1',TO_DATE('21-03-2021','DD-MM-YYYY')) Error report - Unknown Command

Answer 1

Running TO_DATE() on a values which is already a DATE does not make any sense.

So use simply

v_date DATE := ADD_MONTHS(p_training_date,1);

p_training_date := v_date + 1;

The result of TO_CHAR(..., 'd') depents on current user session NLS_TERRITORY settings, it may change at any time. Better use TO_CHAR(p_training_date, 'DY', 'nls_date_language = american') IN ('MON', 'WED', 'FRI')

The query SELECT trainingdate, username INTO v_date1, v_username FROM trainings; is a problem SELECT ... INTO ... requires the query to return exactly one row.

And finally you don't need any loop. You can use row generator, should be similar to this:

v_date DATE := ADD_MONTHS(p_training_date,1);
INSERT INTO trainings (better, list, column, names, here) 
SELECT p_training_id + ROWNUM, p_username, training_date
FROM (
     SELECT p_training_date + LEVEL as training_date 
     FROM dual 
     CONNECT BY p_training_date + LEVEL <= v_date)
WHERE to_char(training_date , 'DY', 'nls_date_language = american') in ('MON', 'WED', 'FRI')
   AND NOT EXISTS (
      SELECT 1 
      FROM trainings 
      WHERE username = v_username AND trainingdate = training_date)

p_training_id  := p_training_id + SQL%ROWCOUNT;