How I can improve my plsql code for working correctly?

user_13468217 0
Viewed 23

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

plsql oracle
1 Answers

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;
user_30272660
Related