Oracle incorporate a pipeline functionality into working procedures

Question

I have a procedure that is working as designed. The issue is that it only creates a schedule for one day at a time.

My question is could the procedure be modified preferably(or wrapped) to call the function generate_dates_pipelined so that many schedules can be created at once?

To modify the procedure I suspect I would need something like this but I'm unsure how to make this work with the current code.

FROM TABLE(generate_dates_pipelined(p_start_date, p_end_date))

Thanks in advance for your patience, help and expertise and to alk who answer.

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

CREATE OR REPLACE FUNCTION CONVERT_TO_SECONDS( 
  i_date_string IN VARCHAR2 
)
RETURN INTEGER DETERMINISTIC
AS
BEGIN
  RETURN ( TO_DATE(i_date_string, 'HH24:MI:SS')
         - TO_DATE('00:00:00', 'HH24:MI:SS')
         ) * 86400;
END;
/  


CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'G' 
            WHEN 2 THEN 'G' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 5;

     ALTER TABLE locations 
         ADD ( CONSTRAINT locations_pk
       PRIMARY KEY (location_id));

create table schedule(
      seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       schedule_id NUMBER(4),
       location_id number(4),
       base_date DATE,
       start_date DATE,
       end_date DATE,
          constraint schedule_pk primary key (schedule_id, location_id, base_date),
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date)
      );


CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  i_base_date   IN DATE,
  i_offset      IN PLS_INTEGER DEFAULT 0, 
  i_incr        IN PLS_INTEGER DEFAULT 10,
  i_duration    IN PLS_INTEGER DEFAULT 5
)
AS 
  l_offset   interval day to second;
  l_incr     interval day to second;
  l_duration interval day to second;
BEGIN
  l_offset   := NUMTODSINTERVAL(i_offset, 'SECOND') ;
  l_incr     := NUMTODSINTERVAL(i_incr, 'MINUTE') ;
  l_duration := NUMTODSINTERVAL(i_duration, 'MINUTE') ;

  MERGE INTO schedule dst
  USING (
    SELECT   i_schedule_id AS schedule_id,
             l.location_id,
             i_base_date AS base_date,
             i_base_date + l_offset + (l_incr * (ROWNUM - 1))
               AS start_date,
             i_base_date + l_offset + (l_incr * (ROWNUM - 1)) + l_duration
               AS end_date
    FROM     locations l
    where  location_id in ( 
      select location_id
      from   locations
      where  location_type = 'G'
    ) 

  ) src
  ON (   src.schedule_id = dst.schedule_id
     AND src.location_id = dst.location_id
     AND src.base_date   = dst.base_date
  )
  WHEN NOT MATCHED THEN
    INSERT (
      schedule_id,
      location_id,
      base_date,
      start_date,
      end_date
    ) VALUES (
      src.schedule_id,
      src.location_id,
      src.base_date,
      src.start_date,
      src.end_date
    );
END;
/


EXEC CREATE_SCHEDULE(1,TRUNC(SYSDATE),CONVERT_TO_SECONDS('16:00:00'));
/

Answer 1

If I'm interpreting your requirement correctly, then a simple loop should suffice. (Not the most efficient mechanism but probably adequate)

CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  -- i_base_date   IN DATE,
  p_start_date in date,
  p_end_date in date,
  i_offset      IN PLS_INTEGER DEFAULT 0, 
  i_incr        IN PLS_INTEGER DEFAULT 10,
  i_duration    IN PLS_INTEGER DEFAULT 5
)
AS 
  l_offset   interval day to second;
  l_incr     interval day to second;
  l_duration interval day to second;
  
  i_base_date date;
BEGIN
  l_offset   := NUMTODSINTERVAL(i_offset, 'SECOND') ;
  l_incr     := NUMTODSINTERVAL(i_incr, 'MINUTE') ;
  l_duration := NUMTODSINTERVAL(i_duration, 'MINUTE') ;

  for i in ( select column_value each_date from TABLE(generate_dates_pipelined(p_start_date, p_end_date)) )
  loop

    i_base_date := i.each_date;

    MERGE INTO schedule dst
    USING (
      SELECT   i_schedule_id AS schedule_id,
               l.location_id,
               i_base_date AS base_date,
               i_base_date + l_offset + (l_incr * (ROWNUM - 1))
                 AS start_date,
               i_base_date + l_offset + (l_incr * (ROWNUM - 1)) + l_duration
                 AS end_date
      FROM     locations l
      where  location_id in ( 
        select location_id
        from   locations
        where  location_type = 'G'
      ) 

    ) src
    ON (   src.schedule_id = dst.schedule_id
       AND src.location_id = dst.location_id
       AND src.base_date   = dst.base_date
    )
    WHEN NOT MATCHED THEN
      INSERT (
        schedule_id,
        location_id,
        base_date,
        start_date,
        end_date
      ) VALUES (
        src.schedule_id,
        src.location_id,
        src.base_date,
        src.start_date,
        src.end_date
      );
  end loop;      
END;
/

I was a little lazy and simply assigned "i_base_date" as a variable so the MERGE stayed untouched.