Oracle difficulty creating a procedure that has subquery

Question

I am attempting to build a procedure that will INSERT rows into the table emp_attendance.

I call a procedure that generates a list of dates based on a range. I then join that table with each employee_id.

Being a novice SQL developer, I am having difficulty trying to understand why the procedure create_emp_attendance is not being created.

Below is my test CASE. Once I get the rows working for the SELECT I will add the INSERT code as I am trying to take the one little piece at a time.

Thanks in advance for your help and expertise.

     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 SEQUENCE batch_seq
      START WITH 1
      MAXVALUE 999999999999999999999999999
      MINVALUE 1
      NOCYCLE
     CACHE 20
     NOORDER;

      

    Create table employees(
      employee_id NUMBER(6), 
      first_name VARCHAR2(20),
      last_name VARCHAR2(20),
     card_num VARCHAR2(10),
      work_days VARCHAR2(7)
   );

    INSERT INTO employees (
     employee_id,
     first_name, 
     last_name,
     card_num,
     work_days
    )
    WITH names AS   ( 
      SELECT 1, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
      SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
    SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
     SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' FROM dual 
   ) SELECT * FROM names; 


    CREATE TABLE  emp_attendance(    
  seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
        employee_id NUMBER(6),
        start_date DATE,
        end_date DATE,
        week_number NUMBER(2),
        create_date DATE DEFAULT SYSDATE
        );

    CREATE OR REPLACE PROCEDURE create_emp_attendance      (
      p_start_date  IN DATE,
      p_end_date   IN DATE
    )
    IS  l_batch_seq number;
   BEGIN

        SELECT get_batch_seq INTO l_batch_seq FROM dual;

     SELECT 
      employee_id,
      start_date, 
      start_date+NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(3600,43200)), 'SECOND') AS end_date,
    to_char(start_date,'WW') AS week_number 

     FROM (
   
    -- Need subquery to generate end_date based on start_date. 

    SELECT 
    e.employee_id,         d.COLUMN_VALUE+       NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS start_date
      FROM   employees e
       INNER JOIN TABLE(           generate_dates_pipelined(p_start_date, p_end_date)
       ) d
   ) ed
  END;


    EXEC create_emp_attendanc(DATE '2021-08-07', DATE '2021-08-14');

Answer 1

You didn't refer to the sequence, so I removed that. Your SELECT needs to have a target via INTO clause or be used within the context of an INSERT (or other) statement. The JOIN was missing an ON clause. But you appeared to want a CROSS JOIN.

If you really want to INSERT in one statement, here's the form:

CREATE OR REPLACE PROCEDURE create_emp_attendance      (
      p_start_date  IN DATE,
      p_end_date    IN DATE
    )
    IS
   BEGIN
     INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)
     SELECT employee_id
          , start_date
          , start_date+NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(3600,43200)), 'SECOND') AS end_date
          , to_char(start_date,'WW') AS week_number 
     FROM (  -- Need subquery to generate end_date based on start_date. 
             SELECT e.employee_id, d.COLUMN_VALUE + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS start_date
               FROM   employees e
              CROSS JOIN TABLE( generate_dates_pipelined(p_start_date, p_end_date) ) d
          ) ed
          ;
END;
/

EXEC create_emp_attendance(DATE '2021-08-07', DATE '2021-08-14');
/

-- Procedure CREATE_EMP_ATTENDANCE compiled
-- PL/SQL procedure successfully completed.

Answer 2

Read comments within code.

SQL> CREATE OR REPLACE PROCEDURE create_emp_attendance
  2   (
  3    p_start_date  IN DATE,
  4    p_end_date   IN DATE
  5   )
  6  IS
  7    l_batch_seq number;
  8  BEGIN
  9    -- there's no GET_BATCH_SEQ function (at least, you didn't post it)
 10    -- SELECT get_batch_seq INTO l_batch_seq FROM dual;
 11    l_batch_seq := batch_seq.nextval;
 12
 13    -- In order to avoid TOO_MANY_ROWS, switching to a cursor FOR loop
 14    for cur_r in
 15     (SELECT
 16        employee_id,
 17        start_date,
 18        start_date+NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(3600,43200)), 'SECOND') AS end_date,
 19        to_char(start_date,'WW') AS week_number
 20      FROM (-- Need subquery to generate end_date based on start_date.
 21            SELECT
 22              e.employee_id,
 23              d.COLUMN_VALUE + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS start_date
 24            FROM   employees e
 25            -- not INNER, but CROSS join (or, if it were INNER, on which column(s)?)
 26            CROSS JOIN TABLE(generate_dates_pipelined(p_start_date, p_end_date)) d
 27           ) ed
 28           ) loop
 29        -- you'll probably have INSERT statement here, according to what you said
 30        null;
 31      end loop;
 32  END;
 33  /

Procedure created.

Testing:

SQL> EXEC create_emp_attendance(DATE '2021-08-07', DATE '2021-08-14');

PL/SQL procedure successfully completed.

SQL>