Forum Stats

  • 3,769,299 Users
  • 2,252,945 Discussions
  • 7,874,982 Comments

Discussions

Categories

Porting SQL to PLSQL expecting INTO clause

BeefStu
BeefStu Member Posts: 284 Blue Ribbon
edited Jul 31, 2021 1:06PM in SQL & PL/SQL 
I have some SQL code, with the help of Frank Kulash, runs perfectly. When I try to wrap it within a procedure, so I can remove the hard coded dates from the SQL and pass them as parameters, it fails to create and I get the following error. 

Errors: PROCEDURE CREATE_ACCESS_HISTORY
Line/Col: 4/1 PLS-00428: an INTO clause is expected in this SELECT statement

I suspect that I have to modify my procedure to include the 'INTO' keyword into some sort of PLSQL access_history table declaration but being a novice with PLSQL I can't seem to get it working. 

I was trying to include a statement like this and SELECT into but that lead me to different syntax errors.

TYPE AhistTabTyp IS TABLE OF access_history %ROWTYPE
      INDEX BY BINARY_INTEGER;


I  was hoping someone  could explain why an INSERT needs a 'SELECT INTO' and successfully create my procedure so I can run it with the sample EXEC command below.

Below is my test CASE. Thanks in advance to all 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 table employees(
          employee_id NUMBER(6), 
          first_name VARCHAR2(20),
          last_name VARCHAR2(20),
          card_num VARCHAR2(10),
          work_days VARCHAR2(7)
        );

        ALTER TABLE employees
        ADD (
          CONSTRAINT employees_pk PRIMARY KEY (employee_id)
              );

        INSERT INTO employees (
          EMPLOYEE_ID,
          first_name, 
          last_name,
          card_num,
          work_days
        )
        WITH names AS ( 
          SELECT 1, 'Jane',     'Doe',      'F123456', 'NYYYYYN'   FROM dual UNION ALL 
          SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN' FROM dual UNION ALL 
          SELECT 3, 'Justin',   'Case',     'C765341','NYYYYYN' FROM dual UNION ALL 
          SELECT 4, 'Mike',     'Jones',      'D564311','NYYYYYN' FROM dual  )
        SELECT * FROM names;  
  
         CREATE TABLE locations AS
             SELECT level AS location_id,
       'Door ' || level AS location_name,

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

            FROM   dual
           CONNECT BY level <= 50;


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

       create table access_history(     seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
             employee_id NUMBER(6), 
             card_num varchar2(10),
             location_id number(4),
             access_date date,
             processed NUMBER(1) default 0
             );


-- Works perfectly 
INSERT into  access_history
         (
          employee_id,
          card_num,
           location_id, 
           access_date
         )
WITH  cntr  AS
(
    SELECT LEVEL - 1 AS n
    FROM     dual
    CONNECT BY LEVEL <= 25 -- Max number of rows per employee per date
)
,    got_location_num  AS
(
    SELECT  location_id
    ,    ROW_NUMBER () OVER (ORDER BY location_id) AS location_num
    ,    COUNT (*)   OVER ()            AS max_location_num
    FROM     locations
)
,    employee_days  AS
(
    SELECT   e.employee_id, e.card_num
    ,     d.column_value         AS access_date
    ,     dbms_random.value (0, 25) AS rn    -- 0 to max number of rows per employee per date
    FROM     employees e
    CROSS JOIN TABLE (generate_dates_pipelined (SYSDATE, SYSDATE + 2)) d
)
,    employee_n_days  AS
(
    SELECT     ed.employee_id, ed.card_num, ed.access_date
    ,     dbms_random.value (0, 1) AS lrn
    FROM     employee_days        ed
    JOIN    cntr            c  ON  c.n <= ed.rn
)
SELECT   n.employee_id, n.card_num, l.location_id, n.access_date + 
NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS ACCESS_DATE 
FROM     employee_n_days  n
JOIN     got_location_num  l  ON l.location_num = CEIL (n.lrn * l.max_location_num);
      
-- Having problems creating procedure 

        create or replace procedure create_access_history(p_start_date date, p_end_date date)
        IS
         BEGIN
                 WITH  cntr  AS
        (
    SELECT LEVEL - 1 AS n
    FROM     dual
    CONNECT BY LEVEL <= 25 -- Max number of rows per employee per date
        )
        ,            got_location_num  AS
        (
    SELECT  location_id
    ,    ROW_NUMBER () OVER (ORDER BY location_id) AS location_num
    ,    COUNT (*)   OVER ()            AS max_location_num
    FROM     locations
        )
,    employee_days  AS
        (
    SELECT     e.employee_id, e.card_num
    ,      d.column_value         AS access_date
    ,          dbms_random.value (0, 25) AS rn    -- 0 to max number of rows per employee per date
    FROM     employees e
    CROSS JOIN TABLE (generate_dates_pipelined (p_start_date, p_end_date)) d
)
       ,        employee_n_days  AS
(
    SELECT     ed.employee_id, ed.card_num, ed.access_date
    ,     dbms_random.value (0, 1) AS lrn
    FROM     employee_days        ed
    JOIN    cntr            c  ON  c.n <= ed.rn
)
        SELECT     n.employee_id,       n.card_num, l.location_id,  n.access_date + 
NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS ACCESS_DATE 
FROM     employee_n_days  n
        JOIN         got_location_num  l  ON l.location_num = CEIL (n.lrn * l.max_location_num); 
       END;

      EXEC  create_access_history (DATE '2021-08-01',  DATE '2021-08-10');



 



Tagged:


  · Share on TwitterShare on Facebook
 








Answers