Oracle issue joining pipelined data

Question

am trying to set up a test CASE, which calls a pipelined function that returns dates based on a DATE range. My goal is to have a row for each date.

I am getting the following error (see below) when I am trying to create the procedure. I know I have to JOIN the passed in p_id with the results returned back from the function but I can't seem to figure out how since there is no link to JOIN them with.

Can someone provide the correct code to fix this issue and explain what I did wrong. Thanks in advance to all who answer and your expertise.

Errors: PROCEDURE CREATE_DATA Line/Col: 8/10 PL/SQL: SQL Statement ignored Line/Col: 11/13 PL/SQL: ORA-00936: missing expression

      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 TABLE data(
      d_id NUMBER(6),
      d_date DATE
    );

  CREATE OR REPLACE PROCEDURE create_data (
    p_id IN NUMBER,
    p_start_date  IN DATE,
    p_end_date   IN DATE
 )
 IS
 BEGIN
     INSERT INTO data (d_id, d_date) 
     VALUES 
      (p_id, 
        TABLE(        generate_dates_pipelined(p_start_date, p_end_date)
       ) c
    );
   END;

  EXEC create_data (1, DATE '2021-08-21', DATE '2021-08-30');

Answer 1

Your table expects a single DATE per row; you are trying to provide a collection of dates per row.

You need to INSERT for each row of the collection and can do that with a SELECT statement:

CREATE OR REPLACE PROCEDURE create_data (
  p_id IN NUMBER,
  p_start_date  IN DATE,
  p_end_date   IN DATE
)
IS
BEGIN
  INSERT INTO data (d_id, d_date) 
  SELECT p_id,
         COLUMN_VALUE
  FROM   TABLE(generate_dates_pipelined(p_start_date, p_end_date));
END;
/